Pass A Variable Number Of Parameters
Pass A Variable Number Of Parameters To Your Stored Procedures With A User Defined Function
By Ben Taylor
Introduction
Frequently SQL Developers need to provide methods to utilize an unknown set of parameters in a stored procedure. I have seen some procedure which have a finite set of parameters and ignore the ones that are null. However, there are times when the possible list of parameters can be cumbersome to manage in this fashion. Applications that allow users to select one or more options to include as a filter for a query are good examples of this problem. It can be difficult to convert and pass to a stored procedure all the options chosen by the user to be used as filtering criteria. It's easy to write dynamic SQL with syntax like "WHERE SOMEFIELD IN (SOME LIST)". But how do you get SOME LIST into a query without writing dynamic SQL? There are about a dozen different ways to kill this beast. All of them have been well documented on the web. In this article I am going to demonstrate a method to utilize a User Defined Function (UDF) to implement one of the many methods. By using a UDF I have created re-usable code so that I don't have to solve the same problem each time I pass a list to a stored procedure. Additionally I demonstrate how to use the results as an Inclusion filter (return all records in the list) and an Exclusion filter (return all records not in the list).
The Problem
I was reading an SQL Server BLOG and found the following excerpt:
Question:
I am passing a parameter to a stored procedure through a string variable by name strlist which contains a list of comma separated values like "a,b,c,d," which I intend to use in a stored procedure, using it as filter criteria for one of the sub-queries like
select ... where o in strlist.
Is this possible in SQL Server to use a variable for a criteria like this?
This question posed on 08 July 2002
Answer:
Yep, but you have to dynamically build the query. You can not hand a list to a variable and then directly use that single variable.
Well, technically the answer provided is correct. You cannot pass a list of values and introduce them into your query as a single variable. However, there are many options. BizDataSolutions.com has put together a pretty comprehensive list of methods in "Faking arrays in T-SQL stored procedures". One of the options happens to use the comma separated list provided in the original question from the BLOG. In this article I will show you a UDF which converts a comma separated value list (or any delimiter you choose) into a table which can then be joined to a table in your database functioning as a filter.
Note: This is the inverse of my previous article where I described a User Defined Function to convert multiple records from a table into a single comma separated values list (see Use SQL 2K Functions To Optimize Your Queries - Part 1.
Convert the String To A Table
The first task is to convert the string passed to the stored procedure into a table. CLICK HERE to get the script. Figure 1 provides a code listing to take a string and a delimiter and convert the string values into a table . to download the source for this function along with others returning date, Integer or NVARCHAR(3900) data types. The table only contains non-null and non-empty string values. Spaces are also not returned. This function returns all values in the delimited string as a VARCHAR(7900) data type.
PRINT 'Function fnSPLIT_VARCHAR'
GO
IF EXISTS (
SELECT 1
FROM SYSOBJECTS
WHERE NAME = 'fnSPLIT_VARCHAR')
BEGIN
PRINT ' DROP FUNCTION fnSPLIT_VARCHAR'
DROP FUNCTION fnSPLIT_VARCHAR
END
GO
PRINT ' Create Function fnSPLIT_VARCHAR'
GO
/**********************************************************************************************/
/* fnSPLIT_VARCHAR */
/**********************************************************************************************/
/* AUTHOR Benjamin S. Taylor */
/**********************************************************************************************/
/* TEST */
/**********************************************************************************************/
/* select * from fnSPLIT_VARCHAR('123,=+4564,=+789',',=+') */
/* Description Function to take a delimited string, parse the results with a passed */
/* delimiter and return a table with one column of type varchar(7900) for each */
/* valid delimited option. Does not return empty string or null. */
/**********************************************************************************************/
/* Version History */
/* Date Author Description */
/* 04/16/2005 Ben Taylor Initial Creation */
/* select * from fnSPLIT_VARCHAR('123,4564,789',',') */
/* select * from fnSPLIT_VARCHAR('123~4564~789','~') */
/* select * from fnSPLIT_VARCHAR('Ben Is A Really Great Guy',' ') */
/* */
/**********************************************************************************************/
CREATE FUNCTION fnSPLIT_VARCHAR (
@STRING TEXT,
@DELIMITER VARCHAR(10))
RETURNS @VALUES TABLE (
VARCHAR_DATA VARCHAR(7500) NOT NULL)
AS
BEGIN
DECLARE @CONTINUE CHAR(1)
DECLARE @START_POS INT
DECLARE @END_POS INT
DECLARE @NEW_STRING VARCHAR(7500)
DECLARE @PAD TINYINT
SELECT @CONTINUE = 'T', @START_POS = 1, @PAD = DATALENGTH(@DELIMITER)
WHILE @CONTINUE = 'T'
BEGIN
SET @END_POS = CHARINDEX(@DELIMITER, @STRING, @START_POS)
IF @END_POS = 0
BEGIN
SET @END_POS = DATALENGTH(@STRING) + 1
SET @CONTINUE = 'F'
END
SET @NEW_STRING = RTRIM(LTRIM(SUBSTRING(@STRING, @START_POS, @END_POS -
@START_POS)))
IF @NEW_STRING <> ''
BEGIN
INSERT INTO @VALUES SELECT @NEW_STRING
END
SET @START_POS = @END_POS + @PAD
END
RETURN
END
GO
IF @@ERROR = 0
BEGIN
PRINT ' Function fnSPLIT_VARCHAR Created Successfully'
ELSE
BEGIN
PRINT ' Create Function fnSPLIT_VARCHAR Failed'
END
GO
Figure 1 - Create Function To Convert A Delimited String Into A Table
Included in the comments for this function are some sample test scripts. Let's see how a couple of them work.
The first sample script returns a table with three numbers as string values:
Query:
SELECT VARCHAR_DATA FROM fnSPLIT_VARCHAR('123,4564,789',',')
Results:
VARCHAR_DATA
123
4564
789
The second sample returns the same values as the first sample. The difference is that the delimiter has been modified to use two characters instead. This function has been designed to allow up to a 10 character delimiter. You can modify it to allow more as needed.
Query:
SELECT VARCHAR_DATA FROM fnSPLIT_VARCHAR('123~+4564~+789','~+')
Results:
VARCHAR_DATA
123
4564
789
This last option simply splits a sentence into the individual words found in it.
Query:
SELECT VARCHAR_DATA FROM fnSPLIT_VARCHAR('Ben Is A Really Great Guy',' ')
Results:
VARCHAR_DATA
Ben
Is
A
Really
Great
Guy
How It Works
This function simply parses the delimited string using the delimiter passed to it. For each delimiter found it locates the position and extracts the substring between the beginning of the string and the first delimiter, between delimiters or finally between the last delimiter (if there is one) and the end of the string.
First we declare the function and it's parameters.
CREATE FUNCTION fnSPLIT_VARCHAR (
@STRING TEXT,
@DELIMITER VARCHAR(10))
The first parameter @STRING is of type TEXT. This is the only way you can create an user definable variable of the type TEXT. It works...so this way you are not limited to the 8000 byte maximum of a VARCHAR or the 4000 byte maximum of an nVARCHAR. The second parameter is @DELIMITER which is the string that separates each of the values in @STRING. Change the length if you need a bigger delimiter.
RETURNS @VALUES TABLE (
VARCHAR_DATA VARCHAR(7500) NOT NULL)
AS
BEGIN
This is not an inline function. An inline function would simply have RETURN followed by a SELECT statement. Since we need to branch and loop in order to parse the string we can not use an inline function. Therefore when we create the function we define a variable with the data type of table as the return value. So, now we have to parse the string and place the data value(s) found in it into this table variable (if there are any).
NOTE: This function has been defined to return a VARCHAR(7500) data type. This is not mandatory. If you download my sample code I have a number of functions returning different data types so that the conversion is already performed when the data elements are found. This should make the use of the User Defined Function perform better when it is utilized because the data will not have to be converted during a join process (described later in this article).
The first thing I do is declare the variables needed to parse the string. Then I initialize the variables appropriately.
DECLARE @CONTINUE CHAR(1)
DECLARE @START_POS INT
DECLARE @END_POS INT
DECLARE @NEW_STRING VARCHAR(7500)
DECLARE @PAD TINYINT
SELECT @CONTINUE = 'T', @START_POS = 1, @PAD = DATALENGTH(@DELIMITER)
Variable Description
@CONTINUE A working variable I use to tell the process when it has reached the end of the string
@START_POS The current starting position within the string for the value being extracted
@END_POS The end position within the string for the value being extracted
@NEW_STRING A working variable to store the contents of the value being extracted so the substring function does not have to be performed twice...once to test for a valid value and the second time to insert it into our table variable
@PAD Stores the data length of our Delimiter value in @DELIMITER. This is used to determine the length of each string found and the true starting position of a string if the delimiter is more than one byte long
Now that we have our variables defined and set to initial values we begin a loop.
WHILE @CONTINUE = 'T'
BEGIN
Next we find the next delimiter (the first delimiter if this is the first time through the loop) in the string that follows the previous delimiter.
SET @END_POS = CHARINDEX(@DELIMITER, @STRING, @START_POS)
Now we test to see if a new delimiter has been found. If not, we set the ending position to the end of the string so that we can always parse the last value after the last delimiter. In the case of a string with no delimiters it will simply parse the first value. Then we set @CONTINUE to 'F' so that the loop will end after completing each step.
IF @END_POS = 0
BEGIN
SET @END_POS = DATALENGTH(@STRING) + 1
SET @CONTINUE = 'F'
END
Now we have our pointers to the values in the string. We have a pointer to the first character and to the last character. We extract the data value that falls between these positions and put the value into our working variable @NEW_STRING
SET @NEW_STRING = RTRIM(LTRIM(SUBSTRING(@STRING, @START_POS, @END_POS -
@START_POS)))
Now we want to write the data value found into our memory table if it is a valid value. Since we performed an RTRIM(LTRIM()) on the value extracted with the SUBSTRING function it will contain to leading or trailing spaces. If the value was simply a space it will now be an empty string. We test for an empty string. If the value is not an empty string we can write it to our table...we have a valid data value.
IF @NEW_STRING <> ''
BEGIN
INSERT INTO @VALUES SELECT @NEW_STRING
END
Now we simply need to increment our pointers so that we process the next value after the one which we just processed.
SET @START_POS = @END_POS + @PAD
END
At this point we have finished the looping and processed all data values. A simple RETURN at this point returns from the function a table value that can be used as a table in a query. You can join to it, select from it, etc. as demonstrated in the examples above.
RETURN
END
GO
Use fnSPLIT_VARCHAR as a filter
Now that you have the function written we can simply use it in our query. This method works much the same way we used to write our stored procedures in versions of SQL Server prior to 7.0. Whenever you had multiple values you wanted to exclude or include in your query and the way it would naturally be implemented would be with multiple OR logical values for the same attribute, or if you had a list of values using IN (some list) (which by the way SQL Server converts to a series of ORs then we would take that list and manually put the options into a table to be used as a filter. Prior to SQL Server 7.0 the query optimizer would make multiple passes through the index or table for each option included between OR. For performance we would put all the options into a single table and join on that table requiring only a single pass through the table being filtered. I found out in a training session that SQL Server 7.0 has actually enhanced the performance for this problem. I wouldn't be surprised if Microsoft was doing the same thing we were doing manually in the back end. Anyway, let's demonstrate how to take advantage of the table we created above. Figure 2 provides the query which will only include the options passed through in the list as a filter.
SELECT E.*
FROM employee E
JOIN dbo.fnSPLIT_VARCHAR('5,6', ',') F
ON E.JOB_ID = f.VARCHAR_DATA
Figure 2 - Use fnSPLIT_VARCHAR As An Inclusion Filter
This same list can be used to implement the notion of NOT IN (some list). Figure 3 demonstrates this method.
SELECT E.*
FROM Employee E
LEFT OUTER JOIN dbo.fnSPLIT_VARCHAR('5,6', ',') F
ON E.JOB_ID = f.VARCHAR_DATA
WHERE F.VARCHAR_DATA IS NULL
Figure 3 - Use fnSPLIT_VARCHAR As An Exclusion Filter
Conclusion
When you have to pass an unknown number of parameters to a stored procedure you can use different methods which can be converted into a table. Once converted to a table the multiple parameters can be used to filter inclusively or exclusively. here are other uses as well which are outside of the scope of this article. There is a lot of code that can be re-used to parse the data being passed to the stored procedure. Therefore, a multi-purpose UDF can save you a great amount of time each time you have to solve this same problem. Theses kinds of UDFs are handy to keep in your tool box. Since they will be transportable and useful in YUKON there is no need to wait to start developing or collecting your own.
About The Author
Ben has been working with relational databases since 1983. SQL Server has always been a favorite, although Ben has worked with many different relational engines, most of which are SQL based. He enjoys solving set based problems that are not solved as efficiently with linear tools. His skills in database design and solutions are frequently sought after. Ben writes frequently for the SQL Server World Wide Users Group when he is not trying to solve new problems.
Bạn đang đọc truyện trên: AzTruyen.Top