Stored procedure split by separator and Match Record to get
I use SQL Server 2014 platform. I have created a stored procedure which I pass 2 parameter to: Die and MetalCode.
If MetalCode is Null or blank, then if part will execute but when MetalCode has any type of value, then else part will execute in which I have to create loop to split value by |.
Here is my table structure
Table Structure image
Here is my stored procedure
ALTER PROCEDURE [dbo].[sp_TDCwax_Get_TDCNo]
@Die NVARCHAR(15),
@MetalCode INT,
AS
BEGIN
DECLARE @TDC NVARCHAR(15)
DECLARE @TDCMetal NVARCHAR(80)
DECLARE @TDCNo NVARCHAR(15), @yield DECIMAL(5,2), @Asswt DECIMAL(7,3)
SELECT
@TDC = TDCNO, @TDCMetal = ISNULL(TDCMCode, '')
FROM
TDCWax
WHERE
Mouldcode = @Die
AND Status = 1
ORDER BY
TdcMCode DESC
IF @TDCMetal = Null OR @TDCMetal = ''
BEGIN
SELECT
@TDCNo = TDCNo, @yield = Yield, @Asswt = AssemblyWT
FROM
TDCWax
WHERE
MouldCode = @Die AND Status = 1
PRINT @TDCNo
PRINT @yield
PRINT @Asswt
END
ELSE
BEGIN
DECLARE @TDCMcode TABLE (TdcMcode INT)
@TDCMcode = Split(@TDCMetal, '|')
-- What I have to do right here
-- @TDCMetal=25|35|65|22 ----- This String Split And Compare Parameter Pass @MetalCode Then Equal
END
END
sql-server stored-procedures edited Jan 2 at 6:25
marc_s
580k13011191266
asked Jan 2 at 5:21
manish patatmanish patat
25
add a comment |
I use SQL Server 2014 platform. I have created a stored procedure which I pass 2 parameter to: Die and MetalCode.
If MetalCode is Null or blank, then if part will execute but when MetalCode has any type of value, then else part will execute in which I have to create loop to split value by |.
Here is my table structure
Table Structure image
Here is my stored procedure
ALTER PROCEDURE [dbo].[sp_TDCwax_Get_TDCNo]
@Die NVARCHAR(15),
@MetalCode INT,
AS
BEGIN
DECLARE @TDC NVARCHAR(15)
DECLARE @TDCMetal NVARCHAR(80)
DECLARE @TDCNo NVARCHAR(15), @yield DECIMAL(5,2), @Asswt DECIMAL(7,3)
SELECT
@TDC = TDCNO, @TDCMetal = ISNULL(TDCMCode, '')
FROM
TDCWax
WHERE
Mouldcode = @Die
AND Status = 1
ORDER BY
TdcMCode DESC
IF @TDCMetal = Null OR @TDCMetal = ''
BEGIN
SELECT
@TDCNo = TDCNo, @yield = Yield, @Asswt = AssemblyWT
FROM
TDCWax
WHERE
MouldCode = @Die AND Status = 1
PRINT @TDCNo
PRINT @yield
PRINT @Asswt
END
ELSE
BEGIN
DECLARE @TDCMcode TABLE (TdcMcode INT)
@TDCMcode = Split(@TDCMetal, '|')
-- What I have to do right here
-- @TDCMetal=25|35|65|22 ----- This String Split And Compare Parameter Pass @MetalCode Then Equal
END
END
edited Jan 2 at 6:25
marc_s
580k13011191266
asked Jan 2 at 5:21
manish patatmanish patat
25
1
Side note: you should not use thesp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!
– marc_s
Jan 2 at 6:25
add a comment |
I use SQL Server 2014 platform. I have created a stored procedure which I pass 2 parameter to: Die and MetalCode.
If MetalCode is Null or blank, then if part will execute but when MetalCode has any type of value, then else part will execute in which I have to create loop to split value by |.
Here is my table structure
Table Structure image
Here is my stored procedure
ALTER PROCEDURE [dbo].[sp_TDCwax_Get_TDCNo]
@Die NVARCHAR(15),
@MetalCode INT,
AS
BEGIN
DECLARE @TDC NVARCHAR(15)
DECLARE @TDCMetal NVARCHAR(80)
DECLARE @TDCNo NVARCHAR(15), @yield DECIMAL(5,2), @Asswt DECIMAL(7,3)
SELECT
@TDC = TDCNO, @TDCMetal = ISNULL(TDCMCode, '')
FROM
TDCWax
WHERE
Mouldcode = @Die
AND Status = 1
ORDER BY
TdcMCode DESC
IF @TDCMetal = Null OR @TDCMetal = ''
BEGIN
SELECT
@TDCNo = TDCNo, @yield = Yield, @Asswt = AssemblyWT
FROM
TDCWax
WHERE
MouldCode = @Die AND Status = 1
PRINT @TDCNo
PRINT @yield
PRINT @Asswt
END
ELSE
BEGIN
DECLARE @TDCMcode TABLE (TdcMcode INT)
@TDCMcode = Split(@TDCMetal, '|')
-- What I have to do right here
-- @TDCMetal=25|35|65|22 ----- This String Split And Compare Parameter Pass @MetalCode Then Equal
END
END
edited Jan 2 at 6:25
marc_s
580k13011191266
asked Jan 2 at 5:21
manish patatmanish patat
25
I use SQL Server 2014 platform. I have created a stored procedure which I pass 2 parameter to: Die and MetalCode.
If MetalCode is Null or blank, then if part will execute but when MetalCode has any type of value, then else part will execute in which I have to create loop to split value by |.
Here is my table structure
Table Structure image
Here is my stored procedure
ALTER PROCEDURE [dbo].[sp_TDCwax_Get_TDCNo]
@Die NVARCHAR(15),
@MetalCode INT,
AS
BEGIN
DECLARE @TDC NVARCHAR(15)
DECLARE @TDCMetal NVARCHAR(80)
DECLARE @TDCNo NVARCHAR(15), @yield DECIMAL(5,2), @Asswt DECIMAL(7,3)
SELECT
@TDC = TDCNO, @TDCMetal = ISNULL(TDCMCode, '')
FROM
TDCWax
WHERE
Mouldcode = @Die
AND Status = 1
ORDER BY
TdcMCode DESC
IF @TDCMetal = Null OR @TDCMetal = ''
BEGIN
SELECT
@TDCNo = TDCNo, @yield = Yield, @Asswt = AssemblyWT
FROM
TDCWax
WHERE
MouldCode = @Die AND Status = 1
PRINT @TDCNo
PRINT @yield
PRINT @Asswt
END
ELSE
BEGIN
DECLARE @TDCMcode TABLE (TdcMcode INT)
@TDCMcode = Split(@TDCMetal, '|')
-- What I have to do right here
-- @TDCMetal=25|35|65|22 ----- This String Split And Compare Parameter Pass @MetalCode Then Equal
END
END
edited Jan 2 at 6:25
marc_s
580k13011191266
asked Jan 2 at 5:21
manish patatmanish patat
25
edited Jan 2 at 6:25
marc_s
580k13011191266
asked Jan 2 at 5:21
manish patatmanish patat
25
edited Jan 2 at 6:25
marc_s
580k13011191266
edited Jan 2 at 6:25
marc_s
580k13011191266
edited Jan 2 at 6:25
marc_s
580k13011191266
580k13011191266
asked Jan 2 at 5:21
manish patatmanish patat
25
asked Jan 2 at 5:21
manish patatmanish patat
25
asked Jan 2 at 5:21
manish patatmanish patat
25
25
1
Side note: you should not use thesp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!
– marc_s
Jan 2 at 6:25
add a comment |
1
Side note: you should not use thesp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!
– marc_s
Jan 2 at 6:25
1
1
Side note: you should not use the
sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!– marc_s
Jan 2 at 6:25
Side note: you should not use the
sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!– marc_s
Jan 2 at 6:25
add a comment |
1 Answer
1
active
oldest
votes
You can use one of the SQL split functions custom build by community developers if you are using a SQL Server version prior to SQL Server 2016
But if are running your application on SQL Server 2016 and later version of SQL Server, you can use string_split function in your stored procedure code
One last note, if you don't have a complex logic behind, just for splitting string parameters instead of using a stored procedure you can use a table valued UDF (user defined function)
answered Jan 2 at 5:49
EralperEralper
5,25011221
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001583%2fstored-procedure-split-by-separator-and-match-record-to-get%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use one of the SQL split functions custom build by community developers if you are using a SQL Server version prior to SQL Server 2016
But if are running your application on SQL Server 2016 and later version of SQL Server, you can use string_split function in your stored procedure code
One last note, if you don't have a complex logic behind, just for splitting string parameters instead of using a stored procedure you can use a table valued UDF (user defined function)
answered Jan 2 at 5:49
EralperEralper
5,25011221
add a comment |
You can use one of the SQL split functions custom build by community developers if you are using a SQL Server version prior to SQL Server 2016
But if are running your application on SQL Server 2016 and later version of SQL Server, you can use string_split function in your stored procedure code
One last note, if you don't have a complex logic behind, just for splitting string parameters instead of using a stored procedure you can use a table valued UDF (user defined function)
answered Jan 2 at 5:49
EralperEralper
5,25011221
add a comment |
You can use one of the SQL split functions custom build by community developers if you are using a SQL Server version prior to SQL Server 2016
But if are running your application on SQL Server 2016 and later version of SQL Server, you can use string_split function in your stored procedure code
One last note, if you don't have a complex logic behind, just for splitting string parameters instead of using a stored procedure you can use a table valued UDF (user defined function)
answered Jan 2 at 5:49
EralperEralper
5,25011221
You can use one of the SQL split functions custom build by community developers if you are using a SQL Server version prior to SQL Server 2016
But if are running your application on SQL Server 2016 and later version of SQL Server, you can use string_split function in your stored procedure code
One last note, if you don't have a complex logic behind, just for splitting string parameters instead of using a stored procedure you can use a table valued UDF (user defined function)
answered Jan 2 at 5:49
EralperEralper
5,25011221
answered Jan 2 at 5:49
EralperEralper
5,25011221
answered Jan 2 at 5:49
EralperEralper
5,25011221
answered Jan 2 at 5:49
EralperEralper
5,25011221
5,25011221
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001583%2fstored-procedure-split-by-separator-and-match-record-to-get%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Side note: you should not use the
sp_prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!– marc_s
Jan 2 at 6:25