December 12, 2011 at 3:01 am
Hi,
I need to pass comma separated values in my procedure but i have problem.
Example:
Declare @STR nvarchar = '1,2,3,4'
select * from emp where empid in(@str)
when i execute this query i got the following error..
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1,2,3,4' to data type int.
so i need to execute the query like
select * from emp where empid in('1','2','3','4')
or
select * from emp where empid in (1,2,3,4)
so how i can split the @STR value from ('1,2,3,4') to ('1','2','3','4') or (1,2,3,4)[/b]
Help me please.
thank you.
December 12, 2011 at 3:20 am
DECLARE @STR NVARCHAR(MAX) = '1,2,3,4'
SET @STR = '''' + REPLACE(@str,',',''',''') + ''''
SELECT *
FROM emp
WHERE empid IN (@str)
December 12, 2011 at 4:48 am
In SS2005, the following may be another possibility:
select * from emp where empid in
( select i.value('text()[1]','int')
from (select cast('<i>'+replace(@str,',','</i><i>')+'</i>' as xml) as xlist) s
cross apply s.xlist.nodes('/i') X(I)
)
December 12, 2011 at 5:11 am
RowanCollum (12/12/2011)
In SS2005, the following may be another possibility:
select * from emp where empid in( select i.value('text()[1]','int')
from (select cast('<i>'+replace(@str,',','</i><i>')+'</i>' as xml) as xlist) s
cross apply s.xlist.nodes('/i') X(I)
)
If you have to pass a lot of such parameters, you'll find that's a bit slower than other possibilities. Please see the following article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2011 at 5:18 am
Cadavre (12/12/2011)
DECLARE @STR NVARCHAR(MAX) = '1,2,3,4'SET @STR = '''' + REPLACE(@str,',',''',''') + ''''
SELECT *
FROM emp
WHERE empid IN (@str)
That'll only work if you use dynamic SQL or maybe if you use LIKE with a conversion on empid which would destroy SARGability.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2011 at 5:20 am
panneermca35 (12/12/2011)
so how i can split the @STR value from ('1,2,3,4') to ('1','2','3','4') or (1,2,3,4)[/b]Help me please.
thank you.
Please see the following article.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2011 at 6:19 am
DECLARE @NUMBER VARCHAR(50)
DECLARE @FINAL VARCHAR(50)
SET @NUMBER = '1,2,3,4,5'
SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''
SELECT @FINAL
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
December 13, 2011 at 6:59 am
HI You can get the best answer in this topic:
December 13, 2011 at 7:10 am
sami.sqldba (12/13/2011)
DECLARE @NUMBER VARCHAR(50)DECLARE @FINAL VARCHAR(50)
SET @NUMBER = '1,2,3,4,5'
SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''
SELECT @FINAL
How might this work?
DECLARE @NUMBER VARCHAR(50)
DECLARE @FINAL VARCHAR(50)
SET @NUMBER = '1,2,3,4,5'
SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''
SELECT @FINAL
SELECT *
FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d
WHERE d.n IN (@FINAL)
-- (0 row(s) affected)
SELECT *
FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d
WHERE d.n IN (1,2)
-- (2 row(s) affected)
You can force it to work with LIKE but it's not straightforward:
DECLARE @NUMBER VARCHAR(50)
DECLARE @FINAL VARCHAR(50)
SET @NUMBER = '2,4,5,13'
SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''
SELECT @FINAL
SELECT *
FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d
WHERE @FINAL LIKE '%'+d.n+'%'
-- (3 row(s) affected)
SELECT *
FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d
WHERE ' ,'+@FINAL+', ' LIKE '%,'''+d.n+''',%'
-- (1 row(s) affected)
Best to follow Jeff's advice and split the values out.
Edit: changed some values in second declaration of @Final.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 14, 2011 at 9:01 am
You can create a function like this that returns the values in a table:
CREATE FUNCTION dbo.fString2Table (
@String as nvarchar(MAX),
@Delimiter as nvarchar(1)
)
RETURNS @ReturnTable TABLE (Value nvarchar(200))
AS
BEGIN
DECLARE @iLen AS int,
@iStart AS int,
@iEnd AS int,
@Value AS nvarchar(200)
SELECT @iLen = LEN(@String), @iStart = 1
WHILE CHARINDEX(@Delimiter, @String, @iStart) > 0
BEGIN
SET @iEnd = CHARINDEX(@Delimiter, @String, @iStart)
SET @Value = SUBSTRING(@String, @iStart, @iEnd - @iStart)
SET @iStart = @iEnd + 1
INSERT INTO @ReturnTable (Value) VALUES (@Value)
END
SET @iEnd = CHARINDEX(@Delimiter, @String, @iEnd)
SET @Value = SUBSTRING(@String, @iStart, @iLen - @iEnd)
INSERT INTO @ReturnTable (Value) VALUES (@Value)
RETURN
END
And use it in this ways
SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')
Or
SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')
WHERE Value = CONVERT(NVARCHAR(200), @intVar)
Or even this
IF EXISTS (SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')
WHERE Value = CONVERT(NVARCHAR(200), @intVar))
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
I hope this would be helpful for you.
December 14, 2011 at 9:07 am
Narud (12/14/2011)
You can create a function like this that returns the values in a table:<<snip>>
And use it in this ways
SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')Or
SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')WHERE Value = CONVERT(NVARCHAR(200), @intVar)
Or even this
IF EXISTS (SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')WHERE Value = CONVERT(NVARCHAR(200), @intVar))
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
I hope this would be helpful for you.
You could, but the one referred to in Jeff Moden's post above is tried and tested - and much much faster.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply