Solved

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

Posted on 2012-12-26
9
299 Views
Last Modified: 2012-12-27
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.
0
Comment
Question by:woodwyn
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 38722688
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.
0
 
LVL 1

Expert Comment

by:igordevelop
ID: 38722710
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).
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 167 total points
ID: 38722747
igor,

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

Open in new window


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) )

Open in new window

0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38722753
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

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 333 total points
ID: 38723260
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

Open in new window

0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 333 total points
ID: 38723292
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

Open in new window

0
 
LVL 4

Expert Comment

by:krtyknmsql
ID: 38723404
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

0
 

Author Comment

by:woodwyn
ID: 38725097
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/
0
 

Author Closing Comment

by:woodwyn
ID: 38725102
Excellent Solutions!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now