April 23, 2004 at 7:37 am
Currently I have a standard query that passes multiple (dynamic) values(1,2,3,5,6,7,8,9,0,11,12) from a checkbox selection(middle tier- .net) to the database. The query then runs on the values added:
Something like this:
SELECT name FROM table WHERE id IN(1,2,3,5,6,7,8,9,0,11,12)
I would like to convert this into a stored procedure...How would I pass these values through (the number of variables may be between 0 and 1000 - for the sake of this example)?
Can anybody help me with this problem, and possibly the syntax?
Thanks
Kevin
April 23, 2004 at 8:03 am
Can you pass this list as a comma-delimted string e.g. "1,2,3,4,5,6,7"?
If so, you could use
CREATE PROC dbo.spProc
@inputstring varchar(8000)
AS
EXEC('SELECT name FROM table WHERE id IN('+@inputstring+')')
GO
If not, the only other way I could think of, albeit slightly messy, is to write a procedure with 1000 optional parameters, i.e. set each one with a default equal to some input you couldn't get back from the app, and then create a string from these. If you named the variables with a number at the end e.g. @var1, @var2 etc. then you could easily run this in a loop.
HTH
April 23, 2004 at 8:04 am
http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 23, 2004 at 7:14 pm
Was doing that today myself for a project.
We pass in with a varchar or multiple varchars if it is going to be longer than one can handle.
My example only allows for one thou.
CREATE PROC ip_MyProc
@vals varchar(8000)
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tblVals') IS NOT NULL
DROP TABLE #tblVals
CREATE TABLE #tblVals (
val int
)
DECLARE @pos int
SET @pos = charindex(',',@vals)
WHILE @pos > 0
BEGIN
INSERT #tblVals (val) VALUES (cast(left(@vals, @pos - 1) as int))
SET @vals = right(@vals, LEN(@vals) - @pos)
SET @pos = charindex(',',@vals)
END
IF LEN(@vals) > 0
INSERT #tblVals (val) VALUES (cast(@vals AS INT))
SELECT * FROM
tblName N
INNER JOIN
#tblVals V
ON
N.columnname = V.val
DROP TABLE #tblVals
This works in both 2000 and 7
However if using 2000 you might do this instead.
CREATE FUNCTION dbo.fn_ParsedTable (@vals varchar(8000))
RETURNS @tblVals TABLE (
val int
)
AS
BEGIN
DECLARE @pos int
SET @pos = charindex(',',@vals)
WHILE @pos > 0
BEGIN
INSERT @tblVals (val) VALUES (cast(left(@vals, @pos - 1) as int))
SET @vals = right(@vals, LEN(@vals) - @pos)
SET @pos = charindex(',',@vals)
END
IF LEN(@vals) > 0
INSERT @tblVals (val) VALUES (cast(@vals AS INT))
RETURN
END
Then you can use like so
SELECT * FROM
tblName N
INNER JOIN
dbo.fn_ParsedTable('1,2,3') V
ON
N.columnname = V.val
but the great thing is it can be reused for other items.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply