woodwyn
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,AssetsHistor y.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.
In simple testing, the following statement returns the expected results:
DECLARE @lcAssets CHAR(400)
SET @lcAssets = 'A101'
SELECT Assets.Number,AssetsHistor
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.
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).
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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,Ja coba'
SET @WhereClause=REPLACE(@Wher eClause,', ',''',''')
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/
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,Ja
SET @WhereClause=REPLACE(@Wher
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/
ASKER
Excellent Solutions!
So if you do it as:
Open in new window
That should get you results.