• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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....
0
Svlss
Asked:
Svlss
  • 14
  • 6
  • 6
  • +2
1 Solution
 
Bob HoffmanDeveloperCommented:
You would need to script the sql statment, IN wont take a variable


assuming @product looks like this 'product1','product2','product3'


declare @sql varchar(1024)

set @sql = 'Select * from myFile where product in (' + @products + ')'
execute (@sql)
0
 
Brendt HessSenior DBACommented:
Many people will advise you to use dynamic SQL, but I disagree.  I have found it to be more efficient to use a User-defined function that returns a table of the elements passed in, and use that in the IN statement.

Note that the data type returned must match the data type being compared.  I will use the dbo.Split function I use (see code below) and use CAST to compare integer values:

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 Int)
    FROM dbo.Split(@MyIDs, ',')
    )

CREATE FUNCTION [dbo].[Split] (
    @Data varchar(2000),
    @delim varchar(5)
    ) 
RETURNS @t table (
    Data varchar(20)
    ) 
AS  
BEGIN
    Declare @ptr int
    Set @data = @data + @Delim


    While Len(@Data) > 0
    Begin
        Insert Into @t (data)
        Select 
            ltrim(rtrim(Substring(@Data,1,Charindex(@delim,@Data)-1)))

        Set @Data = Substring(@Data,Charindex(@delim,@Data)+Len(@delim) ,len(@Data))
    End
	
    Return
END

Open in new window

0
 
dan_masonCommented:
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,','))
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
)

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SvlssAuthor Commented:
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, ',')
    )
0
 
Brendt HessSenior DBACommented:
Can you show the data that was passed in?  The @MyIDs string?
0
 
Brendt HessSenior DBACommented:
Also, since the data is CHAR, you can remove the CAST - char and varchar will work with each other.
0
 
SvlssAuthor Commented:
as  you suggested i removed Char but it still dint work. I am passing data which is some thing like this
AgLink ,GPLink etc
0
 
dan_masonCommented:
Do a REPLACE function to remove any spaces from the string - that may be the issue.
0
 
SvlssAuthor Commented:
where and how  would i do that
0
 
Brendt HessSenior DBACommented:
Try:

SELECT *
FROM Product p
WHERE productID in (
    SELECT Cast(data as char (20))
    FROM dbo.Split(REPLACE(@MyIDs, ' ',''), ',')
    )
0
 
SvlssAuthor Commented:
bhess1 - Dude same old thing i am not gettign compleate data
0
 
dan_masonCommented:
Have you tried just checking the function on its own? i.e.

SELECT * FROM dbo.Split('ABC','DEF','GHI','JKL','MNO','PQR','STU','VWX')

0
 
SvlssAuthor Commented:
it gives me following error
Msg 8144, Level 16, State 3, Line 1
Procedure or function dbo.Split has too many arguments specified.
0
 
dan_masonCommented:
Doh sorry, I meant:

SELECT * FROM dbo.Split('ABC,DEF,GHI,JKL,MNO,PQR,STU,VWX',',')
0
 
Brendt HessSenior DBACommented:
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.....
0
 
SvlssAuthor Commented:
do you have an email is that i can send it to as its a bit confi information
0
 
SvlssAuthor Commented:
dan
SELECT * FROM dbo.cSplit('ABC,DEF,GHI,JKL,MNO,PQR,STU,VWX',',') this  works
so what do i edit now
0
 
SharathData EngineerCommented:
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)

Open in new window

0
 
Brendt HessSenior DBACommented:
You can send to:

Processed Meat Product @ gmail dot com

Remove all of the spaces......
0
 
dan_masonCommented:
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.
0
 
SvlssAuthor Commented:
bhess1 i emailed you the data
0
 
SharathData EngineerCommented:
Did you try my query http:#a28602609 ?
0
 
SvlssAuthor Commented:
Sharath i tried your query but its giving an error

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'xyz'.

i
0
 
SharathData EngineerCommented:
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)

Open in new window

0
 
SvlssAuthor Commented:
but still its taking first value
0
 
SharathData EngineerCommented:

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)

Open in new window

0
 
SvlssAuthor Commented:
Sharath i tried your code and for some reason its just taking first value as parameter value and displays records only for first value
0
 
SharathData EngineerCommented:
I wonder you may not have records for other values in the IN clause. Can you check that?
0
 
SvlssAuthor Commented:
no buddy if i pass single parameter its showing up data. All the input values have data.
0
 
SharathData EngineerCommented:
Can you post some sample data. I will try at my end.
0
 
SvlssAuthor Commented:
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
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

Open in new window

0
 
SvlssAuthor Commented:
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
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 14
  • 6
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now