October 31, 2013 at 4:39 am
Dear Team,
I getting error in below query
Kindly advise how to resolve this issue
Exec StoreProc1 'John','19-08-2013','('CHINA','MALAYSIA','INDIA','JAPAN','USA')','Krin'
I can not able to pass this parameter '('CHINA','MALAYSIA','INDIA','JAPAN','USA')'
i am getting below error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CHINA'.
Thanks,
Chandrahasan S
October 31, 2013 at 4:42 am
You need to escape the single quotes.
Try this:
Exec StoreProc1 'John','19-08-2013','(''CHINA'',''MALAYSIA'',''INDIA'',''JAPAN'',''USA'')','Krin'
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 1, 2013 at 8:52 am
This may be off subject but we wrote a cheater method to handle when you need to pass in multiple parameters to a function
or proc. Sometimes its not really possible to get the quote escaping to work right. We ended up making a helper function that takes a value in as 'ONE, TWO, THREE' and turns it into a table (via a table function). Once you do that you can do a cross apply or do a join with a sub-select on the function. You have to be cautious of the length of the string that you pass in... (see the varchar(255))
Here's an example of the function. You can definitely find a better way that doing a while within the function (slowville), but it works well and we haven't noticed any huge performance issues with the methods that we are using it.
CREATE function [dbo].[ufnt_Split]
(
@StringIn varchar(max),
@Delimiter varchar(1)
)
returns @SplittedValues table
(
Id varchar(255)
)
As
-- Purpose : Reporting Function
-- Split a delimited string into its component values and return them as a table
--Input: StringIn (Delimited List)
--Delimiter (What the seperating character is.
--
--Returns:ID: Single Seperated Value (per row)
--
-- Example SQL:
--select * FROM dbo.ufnt_split('ONE,TWO,THREE,FOUR,FIVE',',');
--
--Example Output:
--Id (Returned as Table output)
--FIVE
--FOUR
--ONE
--THREE
--TWO
--
*/
begin
declare @SplitLength int
while len(@StringIn) > 0
begin
select @SplitLength = (case charindex(@Delimiter,@StringIn) when 0 then
len(@StringIn) else charindex(@Delimiter,@StringIn) -1 end)
insert into @SplittedValues
select LTRIM(RTRIM(substring(@StringIn,1,@SplitLength)))
select @StringIn = (case (len(@StringIn) - @SplitLength) when 0 then ''
else right(@StringIn, len(@StringIn) - @SplitLength - 1) end)
end
return
end
Thanks,
Craig
November 1, 2013 at 9:26 am
craig1768 (11/1/2013)
This may be off subject but we wrote a cheater method to handle when you need to pass in multiple parameters to a functionor proc. Sometimes its not really possible to get the quote escaping to work right. We ended up making a helper function that takes a value in as 'ONE, TWO, THREE' and turns it into a table (via a table function). Once you do that you can do a cross apply or do a join with a sub-select on the function. You have to be cautious of the length of the string that you pass in... (see the varchar(255))
Here's an example of the function. You can definitely find a better way that doing a while within the function (slowville), but it works well and we haven't noticed any huge performance issues with the methods that we are using it.
CREATE function [dbo].[ufnt_Split]
(
@StringIn varchar(max),
@Delimiter varchar(1)
)
returns @SplittedValues table
(
Id varchar(255)
)
As
-- Purpose : Reporting Function
-- Split a delimited string into its component values and return them as a table
--Input: StringIn (Delimited List)
--Delimiter (What the seperating character is.
--
--Returns:ID: Single Seperated Value (per row)
--
-- Example SQL:
--select * FROM dbo.ufnt_split('ONE,TWO,THREE,FOUR,FIVE',',');
--
--Example Output:
--Id (Returned as Table output)
--FIVE
--FOUR
--ONE
--THREE
--TWO
--
*/
begin
declare @SplitLength int
while len(@StringIn) > 0
begin
select @SplitLength = (case charindex(@Delimiter,@StringIn) when 0 then
len(@StringIn) else charindex(@Delimiter,@StringIn) -1 end)
insert into @SplittedValues
select LTRIM(RTRIM(substring(@StringIn,1,@SplitLength)))
select @StringIn = (case (len(@StringIn) - @SplitLength) when 0 then ''
else right(@StringIn, len(@StringIn) - @SplitLength - 1) end)
end
return
end
Thanks,
Craig
You should look at the article in my signature about splitting strings. The function you posted has two major performance hurdles. First it is a multi statement TVF which can actually be worse than a scalar function. Secondly, you are looping inside your function to split your strings.
The article in my signature will demonstrate how to split strings in an iTVF using a set based method. It also has performance charts for a number of different approaches to splitting strings. It is well worth the read.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2013 at 9:30 am
Awesome, thanks!
(Revised): That totally crushes the way I was doing it on performance. Nicely done.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply