June 18, 2008 at 1:58 am
I need a procedure accepting series of ID's and retrieve data according to those ID numbers. Like this:
CREATE PROCEDURE tmp
(
@var nvarchar(MAX)
)
AS
select * from Table where ID IN(@var)
I thought of sending ID's like this: '1,2,3'
Of course it doesn't work and gives "can not convert int to nvarchar" error. How can I do this? Dynamic SQL? Or is there a better way?
June 18, 2008 at 2:07 am
deastr (6/18/2008)
I need a procedure accepting series of ID's and retrieve data according to those ID numbers. Like this:CREATE PROCEDURE tmp
(
@var nvarchar(MAX)
)
AS
select * from Table where ID IN(@var)
I thought of sending ID's like this: '1,2,3'
Of course it doesn't work and gives "can not convert int to nvarchar" error. How can I do this? Dynamic SQL? Or is there a better way?
You could create a table variable with a single column, populate it with the relevant ids, and then join it with your original query.
Regards,
Andras
June 18, 2008 at 2:09 am
Sorry, you can pass table variables as parameters only in SS2008. You could use temp tables though (or a communication table).
A simple example is:
CREATE TABLE commtable
(
spid INT,
ids INT,
CONSTRAINT commpatable_pk_1 PRIMARY KEY ( spid, ids )
)
go
-- The procedure
CREATE PROC getstuff ( @spid INT )
AS
BEGIN
SELECT *
FROM sys.objects AS o
JOIN commtable AS c ON c.spid = @spid
AND c.ids = o.object_id
END
GO
-- populate the commtable with some data you want to query:
DELETE FROM commtable WHERE spid = @@spid
INSERT INTO commtable VALUES ( @@spid, 5 )
INSERT INTO commtable VALUES ( @@spid, 6 )
INSERT INTO commtable VALUES ( @@spid, 7 )
-- you pass the @@spid to the stored procedure, and join the
EXEC getstuff @@spid
GO
DELETE FROM commtable WHERE spid = @@spid
but if you only have a few values to check for, you could also use dynamic SQL, like
CREATE PROC p1 ( @v VARCHAR(100) )
AS
BEGIN
DECLARE @q VARCHAR(2000)
SET @q = 'select * from sys.objects where object_id IN (' + @v + ')'
EXEC ( @q )
END
go
EXEC p1 '6,7'
Regards,
Andras
June 18, 2008 at 5:58 am
There are many possible ways to achieve your goal... You can find a pretty detailed description here:
or, in case you have SQLS2000 and posted in a wrong forum by mistake, you'll find a link to older (7.0 and 2000) version inside the article.
June 19, 2008 at 11:54 am
You could try something like:
WHERE CHARINDEX(',' + CONVERT(VARCHAR, ID) + ',', @Var) <> 0
If the passed string didn't start and end with a comma you'd have to put one there before running the query.
I don't recommend this technique, however, since the WHERE clause can't use an index on ID.
I'd rather create a quick temp table on the fly by splitting the string and inserting the values (as INT) into the temp table and then join to it.
There have been various posts recently on splitting and concatenating strings lately so I won't go into how to do that.
Todd Fifield
June 19, 2008 at 1:31 pm
Please... make life easy on your self... please read the following article...
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2008 at 10:45 am
Jeff,
Great post. I'd forgotten about that particular post of yours. I've actually been using tally type tables for dates for years but had forgotten how useful they can be for just plain old numbers.
Todd
June 20, 2008 at 1:41 pm
Thanks for the great feedback, Todd...
It's amazing... about 1/3rd of all posts that I respond to lately can easily be solved by a Tally table... I finally convinced the guys at work that they were the way to go, too. They use them to generate similar rows, do all manner of splits, find the missing "man" so far as dates and certain sequences go, generate interim calendar tables, etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply