Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
COURSE of the MONTH
9 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Use a single SQL stored procedure parameter containing multiple expressions in a WHERE column_name IN (parameter) clause
Posted on 2012-12-26
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
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2-
2
- +3
9 Comments
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:
That should get you results.
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)
That should get you results.
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).
igor,
Good point. You're right.
Add this function to your DB:
Then your query becomes:
Good point. You're right.
Add this function to your DB:
CREATE FUNCTION dbo.fnParseList
(
@Delimiter CHAR,
@Text TEXT
)
-- Grabbed from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS
BEGIN
DECLARE @NextPos INT,
@LastPos INT
SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0
WHILE @NextPos > 0
BEGIN
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END
IF @NextPos <= @LastPos
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)
RETURN
END
Then your query becomes:
SELECT Assets.Number,AssetsHistory.dtMoved
FROM Assets LEFT OUTER JOIN AssetsHistory ON
AssetsHistory.keyAssets = Assets.keyAssets
WHERE Assets.Number IN (SELECT Data FROM dbo.fnParseList(Char(44), @lcAssets) )
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'
One option is to use dynamic sql
CREATE TABLE #Assets (
Number VARCHAR(10)
,keyAssets VARCHAR(10)
)
INSERT INTO #Assets VALUES ('A101', '1')
INSERT INTO #Assets VALUES ('A102', '2')
INSERT INTO #Assets VALUES ('A103', '3')
INSERT INTO #Assets VALUES ('A104', '2')
INSERT INTO #Assets VALUES ('A105', '3')
CREATE TABLE #AssetsHistory (
dtMoved DATETIME
,keyAssets VARCHAR(10)
)
INSERT INTO #AssetsHistory VALUES ('2012-12-05', '1')
INSERT INTO #AssetsHistory VALUES ('2012-12-10', '2')
DECLARE @SQL VARCHAR(MAX)
DECLARE @lcAssets VARCHAR(400)
SET @lcAssets = 'A101'',''A102'',''A103'
SET @SQL = '
SELECT
a.Number
,h.dtMoved
FROM
#Assets a
LEFT OUTER JOIN #AssetsHistory h ON h.keyAssets = a.keyAssets
WHERE
a.Number IN (''' + @lcAssets + ''')'
EXEC (@SQL)
DROP TABLE #Assets
DROP TABLE #AssetsHistory
Other option is to disect lcAssets and insert it into a variable table
-----------------------------------------------------
CREATE TABLE #Assets (
Number VARCHAR(10)
,keyAssets VARCHAR(10)
)
INSERT INTO #Assets VALUES ('A101', '1')
INSERT INTO #Assets VALUES ('A102', '2')
INSERT INTO #Assets VALUES ('A103', '3')
INSERT INTO #Assets VALUES ('A104', '2')
INSERT INTO #Assets VALUES ('A105', '3')
-----------------------------------------------------
CREATE TABLE #AssetsHistory (
dtMoved DATETIME
,keyAssets VARCHAR(10)
)
INSERT INTO #AssetsHistory VALUES ('2012-12-05', '1')
INSERT INTO #AssetsHistory VALUES ('2012-12-10', '2')
-----------------------------------------------------
DECLARE @lcAssetsTable TABLE (
lcAsset VARCHAR(400)
)
DECLARE @lcAssets VARCHAR(400)
DECLARE @x INT
DECLARE @y INT
-----------------------------------------------------
SET @lcAssets = 'A101,A102,A103'
SET @x = 1
SET @y = LEN(@lcAssets) - LEN(REPLACE(@lcAssets, ',', ''))
WHILE @x <= @y
BEGIN
INSERT INTO @lcAssetsTable VALUES(LEFT(@lcAssets, CHARINDEX(',', @lcAssets) - 1))
--PRINT LEFT(@lcAssets, CHARINDEX(',', @lcAssets) - 1) + ' - ' + @lcAssets + ' - ' + SUBSTRING(@lcAssets, CHARINDEX(',', @lcAssets) + 1,LEN(@lcAssets))
SET @lcAssets = SUBSTRING(@lcAssets, CHARINDEX(',', @lcAssets) + 1,LEN(@lcAssets))
SET @x = @x + 1
END
INSERT INTO @lcAssetsTable VALUES(@lcAssets)
-----------------------------------------------------
SELECT
a.Number
,h.dtMoved
FROM
#Assets a
LEFT OUTER JOIN #AssetsHistory h ON h.keyAssets = a.keyAssets
WHERE
a.Number IN (SELECT lcAsset FROM @lcAssetsTable)
DROP TABLE #Assets
DROP TABLE #AssetsHistory
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
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
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/
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/
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month9 days, 5 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.