Handling Parameter Lists in MS SQL Server 2000 stored procedures.
Because of how SQL Server compiles stored procedures, it cannot handle a parameter being used in the IN clause that contains a parameter list.
There are a few ways to handle this, one very common seems to be to generate a dynamic SQL statement and concatenate the contents of the parameter to the SQL statement string, then execute using the sp_executesql function. Easy peezy.
Example 1@vBillingCentres = “'1016','1049','1049A','1122A','1122C','1303','1369','1450','1615','1617'” passed in from Client to stored procedure.
DECLARE @SQLString nvarchar(4000)
SET @SQLString = N’SELECT * FROM tbl_test01 t where t.BillingCentre in ('+ @vBillingCentres +')’
EXECUTE sp_executesql @SQLString
This works nicely, although the use of dynamic SQL negates the performance that one can achieve using pre-compiled SQL Statements. The other major complaint is that as the procedure/SQL gets more and more complex, it can become more difficult to manage the string concatenation leading to issues with maintenance and debugging.
A Nicer Way
Ideally we don’t want to have to parse our stored procedures to differently from the norm, we would also like to take advantage of compiled code, and well, I’m a little bit of a neat freak when it comes to code, so yes, its gotta look right.
Using a SQL Server 2000 user defined functions we can create a function that we pass the parameter list, and returns a table object. Thus we can simple join on its output.
Example 2:
SELECT * FROM tbl_test01 T, fn_GetBillingCentreTble(@vBillingCentres) B
WHERE T.BillingCentre = B.BillingCentre
I think that most will agree that maintaining example 2 is far easier than example 1, there is a catch.
Depending on your requirements, the code sitting behind the function might need to be relatively complex. I have provided an example that I have used, and its by no means optimised. But in places were you have many many procedures needing this processing, it makes sense to invest some time in this functionality in my opinion.
This is the basic structure of the function (it will not compile, you’d need to work out your own logic anyway)
CREATE FUNCTION Fn_GetBillingCentreTble (@vBillCentres varchar(4000))
RETURNS @vTBL_BillingCentres TABLE (BillingCentre varchar(8) primary key)
AS
BEGIN
DECLARE @pos int
DECLARE @TempString varchar(5000)
DECLARE @curBillCentre varchar(50)
DECLARE @tTBL_BillingCentres TABLE (BillingCentre varchar(8) primary key) -- a temporay table variable used to store results
select @TempString = rtrim(ltrim(@vBillCentres)) -- trim and assign input parameter to working variable
if (@TempString <> '') -- split the BillingCentres if input string is not blank
begin
select @pos = charindex(',', @TempString)
while (@pos > 0)
begin
select @curBillCentre = substring(@TempString,1,@pos -1)
select @curBillCentre = rtrim(ltrim(replace(replace(@curBillCentre, '''', ' '),'"', ' ')))
if (LEN(@curBillCentre) >= 1 and LEN(@curBillCentre) <= 8)
begin
insert into @tTBL_BillingCentres values(@curBillCentre)
end
select @TempString = substring(@TempString,@pos +1,LEN(@TempString) - @pos)
select @pos = charindex(',', @TempString)
end
end
-- Copy the content of the local table variable to the one that will be returned
insert into @vTBL_BillingCentres
select BillingCentre from @tTBL_BillingCentres
return
END
I guess the only issue here is that we use a temp table, but really, its very lightweight in this example as there are limits to the size of the parameter list that is passed in.
Either way, in practice, both these approaches work
