Select all records matching a passed in list of ids
-
Hi all.
Can someone help me with the following (Sql Server 2000): -
I have a table as follows
intID int
strDescription varchar(50)
strID varchar(25)
I want to pass in a comma seperated list of strIDs and return all rows that have a strID in the list.
Eg pass ‘1,2,789,1004,27’ to the following: -
Select intID, strDescription, strID
From tblTable
Where strID in (@strList)
Doing this returns nothing. Any suggestions on where I am going wrong.
Thank you.
CCB
-
The problem is basically... This won't work. SQL interprets your passed in parameter as a LITERAL and not an ARRAY. There are 2 different ways to approach this.
1. Create a #TEMP table and PARSE the comma-delimited list and INSERT them into #TEMP and then do a WHERE strID IN (SELECT .. FROM #TEMP)
OR
2. Use dynamic SQL
#1 would be my #1 choice....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Hi CCB,
I prefer the Dynamic approach i'm afraid.
I am just taking as red that your key value is an integer or some form of incrementing number.
You are asking SS to decide wether a number is in a string. It wont do it without implicit conversion so it can evaluate like datatypes.
The way forward is to convert your whole SQL query into a string, add the string variable to it and execute it dynamically (Oh no -
- I can hear the screams of dismay even now......)E.G:
DECLARE @sql nvarchar(2000),
@VARS varchar(255)
SET @VARS = '1, 3, 7, 10'
SET @sql = 'Select intID, strDescription, strID From tblTable Where strID in (' + @VARS + ')'
EXEC (@SQL)
Have fun
Steve
We need men who can dream of things that never were.
-
You can do this your way by using Dymanic SQL. But I would suggest you look for the article on the Curses and Blessings of Dynamic SQL, (the URL is all over this site) and think about parsing through this list and handling it that way. Otherwise, if you decide to use Dynamic SQL, this will work:
DECLARE @strList varchar(100),
@SQL varchar(1000)
CREATE TABLE #TEST( intID int,
strDescription varchar(50),
strID varchar(25))
INSERT INTO #TEST SELECT 1, 'TRUE', '1'
INSERT INTO #TEST SELECT 2, 'TRUE', '2'
INSERT INTO #TEST SELECT 3, 'FALSE', '3'
INSERT INTO #TEST SELECT 27, 'TRUE', '27'
SELECT @strList = '1, 2, 789, 1004, 27'
SELECT @sql = 'SELECT intID, strDescription, strID ' + CHAR(10) +
'FROM #TEST ' + CHAR(10) +
'WHERE strID IN( ' + @strList + ') '
EXEC( @sql)
DROP TABLE #TEST
I wasn't born stupid - I had to study.
-
Hi Steve.
thanks for the advice, I have tried this but the column on the where clause is a string (strID). When I try to run it in Query Analyser, I get the following message
Syntax error converting the varchar value '24d' to a column of data type int.
How do i set the following as individual varchars within the outer quotes: -
SET @VARS = '1, 3, 7, 10'
Thanks
CCB
-
SELECT @sql = 'SELECT intID, strDescription, strID ' + CHAR(10) +
'FROM #TEST ' + CHAR(10) +
'WHERE strID IN( ''' + @strList + ''') '
This should work
Prasad Bhogadi
www.inforaise.com -
Hi CCB, Howdy Farrell,
Thats going to hurt.......
DECLARE @VARS nvarchar(2000)
SET @VARS = '''1''' + ',' + '''3''' + ',' + '''7''' + ',' + '''10'''
Don't forget the brackets.
Farrel - isn't that the same code I put up with a temp table added in?
Have fun
Steve
We need men who can dream of things that never were.
-
There are a number of user defined functions that can be found on this site that will parse a delimited string and return a table that can be used in your select statement like:
Select * from table_a where id IN (Select * from parse_list(@list) )
Here is just one example: http://www.sqlservercentral.com/scripts/contributions/319.asp
-
http://www.sommarskog.se/arrays-in-sql.html is really worth reading on this topic.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks guys.
went with Ron K's seggestion in the end.
Regards.
CCB
-
Here's a nice variation that is set based :
CREATE PROCEDURE [dbo].[SPNVisitesEffacerOrdreAffichage] @FkTech as smallint, @Items as varchar(7998)
AS
SET NOCOUNT ON
DELETE FROM dbo.VisitesClientsOrdreAffichageParTech
WHERE FkTech = @FkTech
--delete all items all the tech or only the ones listed in @items
AND (@Items = 'ALL' OR CHARINDEX (',' + CAST(FkVisiteClient AS VARCHAR(10)) + ',', ',' + @Items + ',', 0) > 0)
SET NOCOUNT OFF
GO
-
Yeah Steve. You type too darn fast!!! 
I was still typing when you posted and I was too lazy to edit my post and indicate you beat me... 
I wasn't born stupid - I had to study.
-
Hi All,
It's not a competition mate
Good to see you still around (and yes, i'm still trying to get an incremented number to append itself to a variable name in my spare (?) time).If you are going down the table route, here is a little sproc that i found in these forums a long time ago and still use regularly today. Really handy:-
/*########################################################################################
PURPOSE
Takes in a list of values in a string, a delimiting character, a table name and a column name and seperates the values
based around the delimiting character
PARAMETERS
@vcList - List of values, delimited by @vcDelimiter
@vcDelimiter - Delimiting character
@TableName - Name of Table to pass seperated values into
@ColumnName - Names of Column to pass seperated values into
NOTES
############################################################################################*/
CREATE PROCEDURE [bq_fce_ListToTable]
@vcList VARCHAR(8000),
@vcDelimiter VARCHAR(8000),
@TableName SYSNAME,
@ColumnName SYSNAME
AS
SET NOCOUNT ON
DECLARE @iPosStart INT,
@iPosEnd INT,
@iLenDelim INT,
@iExit INT,
@vcStr varchar(8000),
@vcSql varchar(8000)
SET @iPosStart = 1
SET @iPosEnd = 1
SET @iLenDelim = LEN(@vcDelimiter)
SET @vcSql = 'INSERT ' + @TableName + ' (' + @ColumnName + ') VALUES ('''
SET @iExit = 0
WHILE @iExit = 0
BEGIN
SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)
IF @iPosEnd <= 0
BEGIN
SET @iPosEnd = LEN(@vcList) + 1
SET @iExit = 1
END
SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)
EXEC(@vcSql + @vcStr + ''')')
SET @iPosStart = @iPosEnd + @iLenDelim
END
RETURN 0
GO
Have fun
Steve
We need men who can dream of things that never were.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply