June 6, 2006 at 3:21 am
How can I send a multi-value parameter to a stored procedure and, once sent, how do I interrogate it in T-SQL? Has anyone tried this?
June 6, 2006 at 5:37 am
Search the forum for a "list split" function. Preferably a function that returns a resultset with all parameters numbered.
N 56°04'39.16"
E 12°55'05.25"
June 6, 2006 at 5:15 pm
Jon
Pass the parameter just like you would any other. Procedure needs to define the value as varchar, as it's simply a comma delimited string. Trick is to parse this string into a table that you can then join to, or use in an IN ( clause. For that task I created a function that returns a table.
Hope this helps.
Mike
June 7, 2006 at 2:11 am
Thanks Mike. I can see the table/join approach should be possible, but the neater
if @var in (@paramlist)
doesn't seem to work. Or am I using the wrong syntax?
Cheers.
June 7, 2006 at 5:42 am
@var in (@parmlist) will not work as it is sending the parameter as a single delimited string, such as 'value1,value2,value3', and not as 'value1','value2','value3'.
June 7, 2006 at 8:23 am
Thanks Joe. Whilst we might know better than that (!) we seem to be forced into doing it to get round the problem of a parameter which comes out of Reporting Services as a comma delimited string of values...
June 7, 2006 at 9:38 am
if @var in (@paramlist) This will work if you place your query in the report as text, but not in a procedure.
Mike
June 7, 2006 at 1:23 pm
Hi Jon,
You just need to parse the string in your stored procedure. For example:
--Create a procedure and run it:
CREATE PROCEDURE proc_test
@p1 varchar(60)
AS
declare @var1 varchar(30), @var2 varchar(30), @var3 varchar(30),
@var4 varchar(30), @var5 varchar(30), @var6 varchar(30)
declare @work varchar(60), @Pointer smallint
set @work = @p1
--Locate delimiter
set @Pointer = charindex(',',@work,0)
--Get parameter 1
set @var1 = left(@work, @Pointer-1)
set @work = substring(@work,@Pointer+1,len(@work)-(@Pointer))
--Get parameter 2
set @Pointer = charindex(',',@work,0)
set @var2 = left(@work, @Pointer-1)
set @work = substring(@work,@Pointer+1,len(@work)-(@Pointer))
--Get parameter 3
set @Pointer = charindex(',',@work,0)
set @var3 = left(@work, @Pointer-1)
set @work = substring(@work,@Pointer+1,len(@work)-(@Pointer))
--Get parameter 4
set @Pointer = charindex(',',@work,0)
set @var4 = left(@work, @Pointer-1)
set @work = substring(@work,@Pointer+1,len(@work)-(@Pointer))
--Get parameter 5
set @Pointer = charindex(',',@work,0)
set @var5 = left(@work, @Pointer-1)
set @work = substring(@work,@Pointer+1,len(@work)-(@Pointer))
--Get parameter 6
set @Pointer = charindex(',',@work,0)
set @var6 = @work
select @p1
select @var1 Field1, @var2 Field2, @var3 Field3, @var4 Field4, @var5 Field5, @var6 Field6
--Run the SP passing some data
exec proc_test 'aaaaaa,bb,ccc,dddd,eeeee,ffffff'
This is easy to modify for additional parameters by moving the last parse to the end and adding parses in the middle for each added parameter.
Hope that helps.
June 7, 2006 at 3:06 pm
To add some samples that you can choose from.
This is a user defined function that I use to return a table of parameters passed in a parameter string. Lets say you are concatenating a list of form data from a web page where you take the user's First Name, Last Name and email address.
your concatenated string looks like this: John,Doe,jdoe@company.com
Once you've installed the userdefined function you can execute this:
select * from dbo.fnParseParamString('John,Doe,jdoe@company.com',',')
this returns
iRowId vcParameters
----------- --------------------
1 John
2 Doe
(3 row(s) affected)
now, since you know Parameter 1 is the first name, and parameter 2 is the last name and parameter 3 is the email address you can do something like this:
select vcParameter from dbo.fnParseParamString('John,Doe,jdoe@company.com',',') where iRowId = 3
which returns the email address portion of the parameter string
vcParameters
--------------------
(1 row(s) affected)
--------------------------------------------------------------
NOW THE FUNCTION
--------------------------------------------------------------
IF EXISTS(Select * from sysobjects where id = OBJECT_ID(N'fnParseParamString'))
DROP FUNCTION fnParseParamString
GO
CREATE FUNCTION dbo.fnParseParamString (@ParamString VARCHAR(255),@Delimeter VARCHAR(1))
RETURNS @tblParams TABLE(iRowId INT IDENTITY(1,1),vcParameters VARCHAR(20))
AS
BEGIN
-- PARSE PARAMETER STRING
DECLARE @vcParameter VARCHAR(20)
DECLARE @iStart INT
DECLARE @iEnd INT
DECLARE @iLength INT
SET @iStart = 0
SET @iEnd = len(@ParamString)
WHILE @iEnd <> 0
BEGIN
SET @iEnd = CHARINDEX(@Delimeter,@ParamString,@iStart)
SET @iLength = @iEnd - @iStart
IF @iLength < 0
SET @iLength = len(@ParamString)
SET @vcParameter = SUBSTRING(@ParamString,@iStart,@iLength)
INSERT INTO @tblParams Select @vcParameter
SET @iStart = @iEnd+1
END
RETURN
END
-
June 7, 2006 at 11:26 pm
One small piece of advice. while loops, cursors and UDFs may perform poorly for large record counts because the query optimizer cannot treat them with set operations. If performance is a priority and you have large record sets, you should parse the code without loops or UDFs.
June 8, 2006 at 2:02 am
Thanks guys. I think the table-valued function is probably the way to go.
I conclude then that using an IN clause (in the sproc) is out of the question?!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply