April 3, 2009 at 5:40 am
dear friends,
I need some guidence regarding below pasted code.....
select * from oatable065 where column004 in ('fe44fcb1-49c5-4a03-93de-6fadfbb5dbd0','FD857BFA-13F9-4051-91B8-6677D2A31C52')
above's code is giving me the desired result... but when i want the way it is below its not working...
declare @Ids varchar(100)
set @Ids='''fe44fcb1-49c5-4a03-93de-6fadfbb5dbd0'',''FD857BFA-13F9-4051-91B8-6677D2A31C52'''
print @ids
select * from oatable065 where column004 in (@Ids)
its not giving any result...if i take single parameter then it works but for multiple parameter its not working?
Can any one give me the solution or explaination to it without using any temp table or dynamic sql?
Thanks in Advance
Regards,
Mithun
April 3, 2009 at 6:19 am
in one way or another, a comma delimited parameter needs to use one of the many SPLIT() functions from the Scripts section on SSC, or use dynamic SQL; there's no two ways about it;
i prefer to use a tally table version, but that's because I've become a Jeff Moden disciple, who really has given us a lot of performance examples with the tally table.
select * from oatable065 where column004 in dbo.Split(@Ids,',') --second parameter is the delimiter
Lowell
April 3, 2009 at 6:26 am
Its good idea... but again we have to use temporary table inside the function.. Anyhow I am also using same function and here is the function which does that work....
CREATE FUNCTION [dbo].[fnSplitter] (@IDs nvarchar(Max))
Returns @Tbl_IDs Table (ID Int) As
Begin
Set @IDs = @IDs + ',' -- Append comma
Declare @Pos1 Int -- Indexes to keep the position of searching
Declare @pos2 Int
Set @Pos1=1 -- Start from first character
Set @Pos2=1
While @Pos1<Len(@IDs)
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs
Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As bigint)
Set @Pos2=@Pos1+1 -- Go to next non comma character
Set @Pos1 = @Pos1+1 -- Search from the next charcater
End
Return
End
Regards,
Manohar
April 3, 2009 at 6:27 am
but when i m passing only one values to that parameter it is working and for more then one its not working, actually i wanted to know the reason of it,,, sir if u can help me it would be big favour....
Thanks
Mithun
April 3, 2009 at 6:32 am
yeah thanks for that function actually, we have this function and we are using it also... and we handling such situation thru dynamic sqll......but this question has come when we were writing one sp and we tried this way but it didnt work so i just want to know y not working that way?
May be my knowledge could be poor but forgive me for this and expalin me pls..
Mithun
April 3, 2009 at 6:33 am
Mithun....
I will tell what i know.....In case of ur query , in where clause u have specified column of integer type...
if u pass any numeric value as varchar then it will consider it as integer , if u pass any delimeter(in ur case, its ","), then it wont consider as integer field....
April 3, 2009 at 6:33 am
you sure THAT function is working?
it's definition is:
Returns @Tbl_IDs Table (ID Int)
but you are passing varchar GUIDS...so i doubt it returns anything valid.
set @Ids='''fe44fcb1-49c5-4a03-93de-6fadfbb5dbd0'',''FD857BFA-13F9-4051-91B8-6677D2A31C52'''
won't be right, something like this function is better:
CREATE FUNCTION SPLIT (
@str_in VARCHAR(8000),
@separator VARCHAR(4) )
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS
BEGIN
DECLARE
@Occurrences INT,
@Counter INT,
@tmpStr VARCHAR(8000)
SET @Counter = 0
IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator
SET @str_in = @str_in + @separator
SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
SET @tmpStr = @str_in
WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @strtable
VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))
SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)
IF DATALENGTH(@tmpStr) = 0
BREAK
END
RETURN
END
Lowell
April 3, 2009 at 6:39 am
manohar (4/3/2009)
Mithun....I will tell what i know.....In case of ur query , in where clause u have specified column of integer type...
if u pass any numeric value as varchar then it will consider it as integer , if u pass any delimeter(in ur case, its ","), then it wont consider as integer field....
But the value getting stored is the varchar only... and i m declaring the varchar varibale only.. bit confuse... pls expalin me more
Thanks
April 3, 2009 at 6:40 am
mithun.gite (4/3/2009)
yeah thanks for that function actually, we have this function and we are using it also... and we handling such situation thru dynamic sqll......but this question has come when we were writing one sp and we tried this way but it didnt work so i just want to know y not working that way?May be my knowledge could be poor but forgive me for this and expalin me pls..
Mithun
the reason it doesn't work is because your variable is an OBJECT, and not a TABLE...
if you say WHERE COLUMN IN(@VARIABLE), since there is only one object in the IN() group, it executes as COLUMN = @VARIABLE
the in statement expects WHERE COLUMN IN(@VARIABLE,@VARIABLE2,@VARIABLE3) Or it expects a table/select statement for multiple values: IN(SELECT SOMEVALUE FROM SOMETABLE) Or a @Table with a single column
Lowell
April 3, 2009 at 6:42 am
manohar (4/3/2009)
Mithun....I will tell what i know.....In case of ur query , in where clause u have specified column of integer type...
if u pass any numeric value as varchar then it will consider it as integer , if u pass any delimeter(in ur case, its ","), then it wont consider as integer field....
Lowell,,
Thx for the function its just working like butter on the bread.....cool but pls make me understand why we cant pass it to paarameter straight a way... pls
Thanks & regards
Mithun
April 3, 2009 at 6:45 am
Lowell (4/3/2009)
mithun.gite (4/3/2009)
yeah thanks for that function actually, we have this function and we are using it also... and we handling such situation thru dynamic sqll......but this question has come when we were writing one sp and we tried this way but it didnt work so i just want to know y not working that way?May be my knowledge could be poor but forgive me for this and expalin me pls..
Mithun
the reason it doesn't work is because your variable is an OBJECT, and not a TABLE...
if you say WHERE COLUMN IN(@VARIABLE), since there is only one object in the IN() group, it executes as COLUMN = @VARIABLE
the in statement expects WHERE COLUMN IN(@VARIABLE,@VARIABLE2,@VARIABLE3) Or it expects a table/select statement for multiple values: IN(SELECT SOMEVALUE FROM SOMETABLE) Or a @Table with a single column
Here i got my anser this is really good one for me.... thanks lowell thanks u very much now i can expaint others also....yeah thsi is the reason thx...
Mithun
April 3, 2009 at 6:57 am
Sorry. .. I was telling related to integer datatype.. actually i also agree with Lowell
April 3, 2009 at 7:19 am
it's the way the IN function works:
the IN() function requires an array() of values, NOT a single variable that happens to have commas in it. that's what you have...a single variable. so you can compare a single variable with operators like = !=. IN(onevalue) just compares the column to that single value, no matter what the variable is composed of.
nothing automatically chops up a variable with a comma into a list...you have to do it manually. that's what your Split function is doing...you decide where it's appropriate to use, and Split() your data accordingly.
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply