Passing in multiple Int values into store procedure

Question

Passing in multiple Int values into store procedure

g Field

SSC-Addicted

Points: 479
More actions
January 1, 2008 at 11:11 am

#62996

I have a store procedure where I want to pass in 1 to n applicationsourceIDs to run as part of the query.
@SourceCompany Varchar(4000)
SELECT * FROM REP_VIEW_APPLICATION
WHERE ApplicationSourceID IN (@SourceCompany)
AND Applicationdate BETWEEN @StartDate AND @EndDate
EXEC stp_Dsh_NewApplication '1,2', '2007/09/01', '2007/12/30'
When you exec a single value it converts it to an int and works, but with multiple values it fails.
When the query runs, I don't know how many ApplicationSourceIDs
cheers
Graham

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

In doesn't work with a variable that contains a comma-delimited field. Your best option would be to split up the string with a table-valued function then use IN (SELECT ... FROM <function> )

I know Jeff's got a nice function that can split a string into a table. I can't find it right now, but I'm sure he'll be happy to post it as soon as he spots this thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 2

Or this: http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 3

Have a look at this article by Erland Sommarskog that covers passing in lists to a stored procedure.

http://www.sommarskog.se/arrays-in-sql.html

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Steve Jones - Editor (1/1/2008)
Or this: http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

Heh... you think that's going to work on SQL Server 2000? This is a 2k forum, ya know 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

I appologize for the semantics... but, in SQL Server 2000, unless you take on the TEXT datatype as an input parameter, the largest character based variable you can define is 8000 bytes. Since an INT can be as large as 10 characters, the absolute largest fixed field number of parameters you can safely pass in a single VARCHAR(8000) is 800 INT parameters. If you pass the parameters with a delimiter, it's even less because you must allow for 10 bytes for the INT and an 11th byte for the delimiter.

800 parameters just doesn't sound like "n" to me 😛 Even if you use the TEXT data type as a parameter, you're still only going to have the capability of 21+ million or so. But... when you get that big, it's a whole lot better to have the information in a table instead of trying to pass it as a parameter (although it can still be done).

So, let's talk design and practical limits... what's the maximum number of parameters you really think you'll ever need to pass and, if it's more than 800, lets discuss the possibility of a bit of redesign.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

g Field SSC-Addicted Points: 479 More actions · Answer 6

Thanks for the solution, I have upto 100 int values depending on the options selected, so the Function iter_intlist_to_table works fine and is now up and running.

Solution copied from article - Function to split numbers and assoc store procedure. 🙂

CREATE FUNCTION iter_intlist_to_table (@list ntext)

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

number int NOT NULL) AS

BEGIN

DECLARE @pos int,

@textpos int,

@chunklen smallint,

@STR nvarchar(4000),

@tmpstr nvarchar(4000),

@leftover nvarchar(4000)

SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))

SET @textpos = @textpos + @chunklen

SET @pos = charindex(' ', @tmpstr)

WHILE @pos > 0

BEGIN

SET @STR = substring(@tmpstr, 1, @pos - 1)

INSERT @tbl (number) VALUES(convert(int, @STR))

SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))

SET @pos = charindex(' ', @tmpstr)

END

SET @leftover = @tmpstr

END

IF ltrim(rtrim(@leftover)) <> ''

INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN

END

Create PROCEDURE dbo.STP_DSH_NewApplication

(

@ids varchar,

@StartDate Datetime,

@EndDate Datetime

)

AS

SET NOCOUNT ON

SELECT Convert(varchar(10),ApplicationDate,102) , Count(*) FROM REP_VIEW_APPLICATION

JOIN iter_intlist_to_table(@ids) i ON REP_VIEW_APPLICATION.ApplicationSourceID = i.number

WHERE Applicationdate BETWEEN @StartDate AND @EndDate

GROUP BY Convert(varchar(10),ApplicationDate,102)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 7

Just so everyone knows... that little slice of computational heaven will not parse the entire list if a number just happens to straddle the 4000 character mark. Instead, it returns only a portion of the value that happens to appear at or below the 4000 character mark and then it quits.

In other words, if there's something besides a space at the 4000, 8000, 1200, etc, mark, it quits early and the last element of data is partially truncated... and it gives NO error when it makes that mistake. :sick:

You sure you want this time bomb in your database? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 8

Ok IMHO the easiest and in my experience fastest way to handle this particular exercise.

DECLARE @CommaList varchar(8000)

SET @CommaList '1,2,3,4,5,6'

SELECT *

FROM YourTable

WHERE ','+@CommaList+',' LIKE '%,'+TableColumn+',%'

NOTE: Ok there is the limit of 800 or so integers you can pass in the list but in this case (a limit of aprox 100 integers) it shouldn't be a problem.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 9

Heh... well done, Kenneth... that's one of my favorites, as well. The only time you really need a "split", is if you have to insert the rows into another table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)