Avatar of woodwyn
woodwyn
Flag for United States of America asked on

Use a single SQL stored procedure parameter containing multiple expressions in a WHERE column_name IN (parameter) clause

The intention here is to allow users to search for information on a random quantity of assets via one call to a SQL stored procedure .  For example, the SP might receive 'A101' or 'A101,A102,A103,...' as the parameter and need to return info on each asset identified.

In simple testing, the following statement returns the expected results:

DECLARE @lcAssets CHAR(400)
SET @lcAssets = 'A101'

SELECT Assets.Number,AssetsHistory.dtMoved
FROM Assets LEFT OUTER JOIN AssetsHistory ON
    AssetsHistory.keyAssets = Assets.keyAssets
WHERE Assets.Number IN (@lcAssets)

However, if I change @lcAssets to 'A101,A102,A103' I don't get an error message or any results.  I have tried multiple variations for getting this to work, but seem to be stumped.
Microsoft SQL Server

Avatar of undefined
Last Comment
woodwyn

8/22/2022 - Mon
Jim P.

Since the input is a string, to use the IN in a query the match would have to be done as 'A101','A201','A301'.

So if you do it as:

select @lcAssets = replace(@lcAssets, Char(44), Char(39)+Char(44)+Char(39))

SELECT Assets.Number,AssetsHistory.dtMoved
FROM Assets LEFT OUTER JOIN AssetsHistory ON
    AssetsHistory.keyAssets = Assets.keyAssets
WHERE Assets.Number IN (@lcAssets)

Open in new window


That should get you results.
igordevelop

Hi,

@jimpen
That wouldn't work since the sql will see it as a one string so you can not use it in IN clause.

Either make something to split the string into different parameters so the IN clause can recognize this or just declare more parameters (I guess you know what might be the maximum).
SOLUTION
Jim P.

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Saurabh Bhadauria

Better approach will be to convert the input parameter in table form and use it in inner join..

I have used XMl to convert input parameter to table form..

Complete code is below... have a look and let me know if you have any concerns...

create  procedure your_procedure (@lcAssets nvarchar(max))
as
begin


declare @ta table(assets nvarchar(100))
Declare @x xml =  cast('<Rows><Row>'+replace(@lcAssets,',','</Row><Row>') + '</Row></Rows>' as xml)    -- Convert input parameter to xml form 

insert into @ta
select a.b.value('.','nvarchar(100)')
 from @x.nodes('Rows/Row') a(b) ---- generate tabular for through xquery

--select * from @ta

SELECT Assets.Number,AssetsHistory.dtMoved
FROM @ta t join Assets on    ----input parameter table in inner join 
     t.assets=Assets.Number      
 LEFT OUTER JOIN AssetsHistory ON 
    AssetsHistory.keyAssets = Assets.keyAssets


end 

go

exec your_procedure 'A101,A102,A103'

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
RehanYousaf

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
RehanYousaf

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
krtyknmsql

Check this..
DECLARE @lcAssets CHAR(400)
SET @lcAssets = 'A101,A102,A103';

WITH CTESplit
AS
(
    SELECT 
        0 AS Pos,
        1 AS PosStart,
        CHARINDEX(',',@lcAssets + ',') AS PosEnd
    UNION ALL
    SELECT
        Pos + 1 AS Pos,
        PosEnd + 1 AS PosStart,
        CHARINDEX(',', @lcAssets + ',',PosEnd + 1 ) AS PosEnd
    FROM CTESplit
    WHERE  CHARINDEX(',', @lcAssets + ',',PosEnd + 1 ) > 1
),
CTEValue
AS
(
    SELECT 
	    SUBSTRING(@lcAssets,PosStart,PosEnd - PosStart) Value
    FROM CTESplit  
)


SELECT Assets.Number,AssetsHistory.dtMoved
FROM Assets LEFT OUTER JOIN AssetsHistory ON 
    AssetsHistory.keyAssets = Assets.keyAssets
INNER JOIN CTEValue ON 
    CTEValue.Value = Assets.Number

Open in new window

woodwyn

ASKER
Thanks all.  I tested all these and most worked.  I also found one other solution that someone else might find useful.

CREATE TABLE [dbo].[Student](
      [Student ID]   [varchar] (6) Not NULL ,
      [Student Name] [varchar](50) NOT NULL)
GO
 
Insert into dbo.[Student] values ('STD001','Bob')
Insert into dbo.[Student] values ('STD002','Alexander')
Insert into dbo.[Student] values ('STD003','Hosanna')
Insert into dbo.[Student] values ('STD004','William')
Insert into dbo.[Student] values ('STD005','Hulda')
Insert into dbo.[Student] values ('STD006','Jacoba')

Declare  @SQL         VARCHAR(MAX)
Declare  @WhereClause VARCHAR(MAX)
Set @WhereClause='Bob,Hulda,Jacoba'
 
SET @WhereClause=REPLACE(@WhereClause,',',''',''')
Set @SQL='Select *
from dbo.[Student]
Where  [Student Name] In (''' + @WhereClause + ''')'

Taken from http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/
woodwyn

ASKER
Excellent Solutions!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.