Svlss
asked on
using (IN) in Stored procedure
Hi i have stored procedure which has a parameter (@product). so i am using this SP in a SSRS report. Its a string and i want to pass multiple values. So in sp is said (product in (@product)). I expected the above line to handle multiple input values. But in report i have drop down values so if i select all the values and i expect to see all the values in report but its not bringing all the values. Its only bringing the first product values which is listed in drop dowm values. And if i select any one of the product other than ALL then its workign fine. I think its expecting '' and , after each parameter values. But if i use aquery and use IN then report is working fine.. But the thing is i have to use only a SP according to requirement....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree with bhess1 that a Split function is the way to go, but I find that for better performance particularly in the case of large datasets it is helpful to used a set-based solution rather than a procedural one.
The function below relies on you having a Numbers table on your database (basically a list of integers from 1 to x - mine is 100,000 but the example below is 10,000). Having a numbers table is handy for lots more than setting up a Split function, as a Google search will confirm - but 90% of the time I use mine for converting delimited strings into tables.
Anyway, that Numbers table would be set up like this:
CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY CLUSTERED)
DECLARE @num INT= 1
WHILE @num <= 10000
BEGIN
INSERT dbo.Numbers(Number)
VALUES (@num);
SET @num=@num+1
END
The Split function I use returns two columns - the individual strings plus a row number relating to the order they were included in the original string. I find this useful because it allows me to run the function on two or more related strings (e.g. a list of part numbers and a list of quantities) and match them up in the correct order (via INNER JOIN) to output as a single table.
As well as the Numbers speeding things up, a further marginal performance gain is also achieved by making this an Inline function, i.e. a straight SELECT statement inside a UDF, without explicitly defining the table. I think this also avoids the need for CAST, or at least I have never had to use it.
With this function your sp would use:
SELECT * FROM MyTable
WHERE product IN (SELECT string from dbo.ListToTable(@products, ','))
The function below relies on you having a Numbers table on your database (basically a list of integers from 1 to x - mine is 100,000 but the example below is 10,000). Having a numbers table is handy for lots more than setting up a Split function, as a Google search will confirm - but 90% of the time I use mine for converting delimited strings into tables.
Anyway, that Numbers table would be set up like this:
CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY CLUSTERED)
DECLARE @num INT= 1
WHILE @num <= 10000
BEGIN
INSERT dbo.Numbers(Number)
VALUES (@num);
SET @num=@num+1
END
The Split function I use returns two columns - the individual strings plus a row number relating to the order they were included in the original string. I find this useful because it allows me to run the function on two or more related strings (e.g. a list of part numbers and a list of quantities) and match them up in the correct order (via INNER JOIN) to output as a single table.
As well as the Numbers speeding things up, a further marginal performance gain is also achieved by making this an Inline function, i.e. a straight SELECT statement inside a UDF, without explicitly defining the table. I think this also avoids the need for CAST, or at least I have never had to use it.
With this function your sp would use:
SELECT * FROM MyTable
WHERE product IN (SELECT string from dbo.ListToTable(@products,
CREATE FUNCTION [dbo].[ListToTable] (@String VARCHAR(4000),@delim VARCHAR(5))
RETURNS TABLE
AS
RETURN
(
WITH splitter (Num, String)
AS
(
SELECT Number as Num, SUBSTRING(@String,
CASE Number + DATALENGTH(@Delim)
WHEN DATALENGTH(@Delim) THEN DATALENGTH(@Delim)
ELSE Number + DATALENGTH(@Delim)
END - DATALENGTH(@Delim),
CASE CHARINDEX(@Delim, @String, Number)
WHEN 0 THEN LEN(@String) - Number + DATALENGTH(@Delim)
ELSE CHARINDEX(@Delim, @String, Number) - Number
END
) AS string
FROM dbo.Numbers
WHERE Number <= LEN(@String)
AND (SUBSTRING(@String, Number - DATALENGTH(@Delim),
DATALENGTH(@Delim)) LIKE @Delim
OR Number = 0)
)
SELECT ROW_NUMBER() OVER (ORDER BY Num) AS rowNum, string
FROM Splitter
)
ASKER
bhess1 i tried your suggestion but for soem reason it not pulling all records its only pulling 1 st parameter value information. but my data type is char see the code below. Let me knwo if i have to do some thing else
CREATE Procedure myProc
@MyIDs varchar(1000)
AS
-- We always use a comma as the separator value
SELECT *
FROM Product p
WHERE productID in (
SELECT Cast(data as char (20))
FROM dbo.Split(@MyIDs, ',')
)
CREATE Procedure myProc
@MyIDs varchar(1000)
AS
-- We always use a comma as the separator value
SELECT *
FROM Product p
WHERE productID in (
SELECT Cast(data as char (20))
FROM dbo.Split(@MyIDs, ',')
)
Can you show the data that was passed in? The @MyIDs string?
Also, since the data is CHAR, you can remove the CAST - char and varchar will work with each other.
ASKER
as you suggested i removed Char but it still dint work. I am passing data which is some thing like this
AgLink ,GPLink etc
AgLink ,GPLink etc
Do a REPLACE function to remove any spaces from the string - that may be the issue.
ASKER
where and how would i do that
Try:
SELECT *
FROM Product p
WHERE productID in (
SELECT Cast(data as char (20))
FROM dbo.Split(REPLACE(@MyIDs, ' ',''), ',')
)
SELECT *
FROM Product p
WHERE productID in (
SELECT Cast(data as char (20))
FROM dbo.Split(REPLACE(@MyIDs, ' ',''), ',')
)
ASKER
bhess1 - Dude same old thing i am not gettign compleate data
Have you tried just checking the function on its own? i.e.
SELECT * FROM dbo.Split('ABC','DEF','GHI ','JKL','M NO','PQR', 'STU','VWX ')
SELECT * FROM dbo.Split('ABC','DEF','GHI
ASKER
it gives me following error
Msg 8144, Level 16, State 3, Line 1
Procedure or function dbo.Split has too many arguments specified.
Msg 8144, Level 16, State 3, Line 1
Procedure or function dbo.Split has too many arguments specified.
Doh sorry, I meant:
SELECT * FROM dbo.Split('ABC,DEF,GHI,JKL ,MNO,PQR,S TU,VWX',', ')
SELECT * FROM dbo.Split('ABC,DEF,GHI,JKL
Can you post an actual string of data sent to the query? This would be very helpful in debugging this issue. Sometimes, the data passed in is not what you think it is.....
ASKER
do you have an email is that i can send it to as its a bit confi information
ASKER
dan
SELECT * FROM dbo.cSplit('ABC,DEF,GHI,JK L,MNO,PQR, STU,VWX',' ,') this works
so what do i edit now
SELECT * FROM dbo.cSplit('ABC,DEF,GHI,JK
so what do i edit now
try this.
declare @sql varchar(1024)
declare @products varchar(4000)
set @products = 'abc,def,ghi'
select @products = ''''+REPLACE(@products,',',''',')+''''
set @sql = 'Select * from myFile where product in (' + @products + ')'
exec (@sql)
You can send to:
Processed Meat Product @ gmail dot com
Remove all of the spaces......
Processed Meat Product @ gmail dot com
Remove all of the spaces......
Svlss,
If that works then, as bhess1 suggested, it looks like a problem with the string you're passing in.
If you edit your script to include 'SELECT @product' as a separate line you will be able to see exactly what the sp is seeing, and hopefully spot the mistake by comparing it with that ABC... string.
If that works then, as bhess1 suggested, it looks like a problem with the string you're passing in.
If you edit your script to include 'SELECT @product' as a separate line you will be able to see exactly what the sp is seeing, and hopefully spot the mistake by comparing it with that ABC... string.
ASKER
bhess1 i emailed you the data
Did you try my query http:#a28602609 ?
ASKER
Sharath i tried your query but its giving an error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'xyz'.
i
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'xyz'.
i
corrected the error
declare @sql varchar(1024)
declare @products varchar(4000)
set @products = 'abc,def,ghi'
select @products = ''''+REPLACE(@products,',',''',''')+''''
set @sql = 'Select * from myFile where product in (' + @products + ')'
exec (@sql)
ASKER
but still its taking first value
What do you mean by that? It will generate the dynamic sql as below and execute.
Select * from myFile where product in ('abc','def','ghi')
If you check the WHERE condition, its checking for products abc,def or ghi.
declare @sql varchar(1024)
declare @products varchar(4000)
set @products = 'abc,def,ghi'
select @products = ''''+REPLACE(@products,',',''',''')+''''
set @sql = 'Select * from myFile where product in (' + @products + ')'
print (@sql)
-- Select * from myFile where product in ('abc','def','ghi')
exec (@sql)
ASKER
Sharath i tried your code and for some reason its just taking first value as parameter value and displays records only for first value
I wonder you may not have records for other values in the IN clause. Can you check that?
ASKER
no buddy if i pass single parameter its showing up data. All the input values have data.
Can you post some sample data. I will try at my end.
ASKER
I used this followign function and now its taking first 2 values
select * from xyz
where item in (SELECT *
FROM dbo.CSplit(parametersplit, ','))
what could bethe reason
select * from xyz
where item in (SELECT *
FROM dbo.CSplit(parametersplit,
what could bethe reason
ALTER FUNCTION [dbo].[parametersplit]
(@InputString VARCHAR(max), @SplitChar varCHAR(5))
RETURNS @ValuesList TABLE
(
param VARCHAR(255)
)
AS
BEGIN
DECLARE @ListValue VARCHAR(max)
Declare @lenIn as int
SET @InputString = @InputString + ','
Set @lenIn = Len(@InputString)
While (@lenIn) <> 0
Begin
Set @ListValue = SUBSTRING(@InputString,1, CHARINDEX(',',@InputString)-1)
INSERT INTO @ValuesList(param) Select @ListValue
Set @InputString = SUBSTRING(@InputString, CHARINDEX(',',@InputString)+ 1,LEN(@InputString)-CHARINDEX(',',@InputString))
Set @lenIn = len(@InputString)
End
RETURN
END
ASKER
My bad man i gave datatye lenght 20 and i was expecting it to take all the parametes... I am sorry and i appriciate your great help
assuming @product looks like this 'product1','product2','pro
declare @sql varchar(1024)
set @sql = 'Select * from myFile where product in (' + @products + ')'
execute (@sql)