Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sending a Variable Number of Elements in a Parameter

By Alex Grinberg,

Introduction

In this article I would like to go over the options that DBAs or developers can use when a table or the multiple rows of values need to be sent to a stored procedure as a parameter. With today’s technology we have at least three different options to accomplish this task. Let’s list all the possibilities and then go over the details:

  1. Use delimited string
  2. Use XML data type
  3. Use User-Defined Table type

Calling a stored procedure to send each individual row in a cursor-based process is not considered a table and is not discussed in this article. 

The Delimited String Option

Using a delimited string is a good option when a parameter’s values will be used with the WHERE clause with an IN predicate as a multi-value filter for a query. A delimited string is easy to build either in .NET or SQL Server.

For example, suppose we received a task to create a stored procedure to return a result set when the WHERE clause could have more than one value. We want then to pass a number of such values as one parameter (with values separated by a delimiter character) to a Stored Procedure or User-Defined Function (UDF).  A traditional way to parse such delimited sting would be in a loop, cutting the string with T-SQL String functions, as in this example:

-- This would be the input parameter of a stored procedure, if you want to do it that way, or a UDF:
declare @string varchar(500)
set @string = ‘ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z’

-- Parsing the comma-delimited string:
declare @pos int
declare @piece varchar(500)

– Need to tack a delimiter onto the end of the input string if one doesn’t exist
if right(rtrim(@string),1) <> ‘,’
 set @string = @string  + ‘,’

set @pos =  patindex(‘%,%’ , @string)
while @pos <> 0 
begin
 set @piece = left(@string, @pos – 1)
 
 – You have a piece of data, so insert it, print it, do whatever you want to with it.
 print cast(@piece as varchar(500))

 set @string = stuff(@string, 1, @pos, ”)
 set @pos =  patindex(‘%,%’ , @string)
end

In other RDBMS/other flavors of SQL languages (or most other programming languages, for that matter) this code would look similar.

Starting from MS SQL Server 2005 and onwards, T-SQL provides a more efficient (yet more complicated) way of parsing a delimited string, using a number of new features available in T-SQL of the latest versions of SQL Server such as CTE. To illustrate parsing a delimited string, let us use the Northwind sample database. We will do this within a stored procedure using a CTE against the Orders and Categories tables.  The table Categories contains the names of categories of products, and both tables are joined by a common field CategoryID. The column CategoryName will be used as a filter.

The task is simple in this demo: to display a list of products that belong to a variable number of categories. A sample result set is shown in Figure 1.1.

One time we call the procedure below, named usp_DelimitedString, and we may want to see all products belonging to the Condiments category, but another time we need a report on products associated with Contiments, Produce, and Meat/Paultry. The next time we may want to pass twenty different categories, separating one value from another by comma  as a delimeter.

How do we pass this variable number of values to a stored procedure as parameter(s)? A delimited string has been a traditional option for quite some time. A string consisting of a varied number of categories separated by comma or any other delimiter of user choice. This is how you can process a delimited string via T-SQL using CTE feature.

Figure 1.1.

First, create the stored procedure:

USE Northwind;
GO

CREATE PROC usp_DelimitedString
     @CategoryList nvarchar(500)
AS
declare @pDelimiter char(1)

SET @pDelimiter = ','

;WITH NUM1(N) AS
(
     SELECT 1 UNION ALL SELECT 1 UNION ALL
     SELECT 1 UNION ALL SELECT 1 UNION ALL
     SELECT 1 UNION ALL SELECT 1 UNION ALL
     SELECT 1 UNION ALL SELECT 1 UNION ALL
     SELECT 1 UNION ALL SELECT 1
),
NUM2(N) AS (SELECT 1 FROM NUM1 a, NUM1 b),
NUM4(N) AS (SELECT 1 FROM NUM2 a, NUM2 b),
CTE_Parse(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM NUM4),
CTE_Filter AS
(
     SELECT SUBSTRING(@CategoryList, N, CHARINDEX(@pDelimiter, @CategoryList + @pDelimiter, N) - N) AS Item
     FROM CTE_Parse
     WHERE N <= LEN(@CategoryList) AND SUBSTRING(@pDelimiter + @CategoryList, N, 1) = @pDelimiter
)   
SELECT dbo.Categories.CategoryName, dbo.Products.ProductName, dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice
FROM dbo.Categories
     JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID
     JOIN CTE_Filter ON Categories.CategoryName = CTE_Filter.Item
GO

Next, call the stored procedure, usp_Delimitedstring

	DECLARE @pString VARCHAR(2000)
	SELECT @pString = 'Produce,Condiments,Meat/Poultry,Seafood'
	EXEC usp_DelimitedString @pString

In the example above, using a delimited string is an acceptable and well-known working option for processing a “one column table” or a single dimensional array as a parameter. Developers are familiar with this technique, which is similar in a number of programming languages. It is however not possible to represent a multi-dimensional array (or multi-row table) in one character string parameter. Another disadvantage with delimited strings is that they require a loop-based parsing process, which can become not only cumbersome but also resource consuming, depending on a size of the string being passed as an input parameter and parsed. The CTE is new and not clear to many developers yet.

Let’s review another option.

The XML data type option

Using the XML data type for purposes similar to the above, we have more flexibility and options because XML can reflect, or contain, a table-like structure of data. This can be represented either as hierarchical or multi-dimensional array of information.

One of the major differences between XML and the delimited string is that XML can be parsed as a multi-column table using an XPath query. The result can then be used in SELECT, INSERT, UPDATE or DELETE statements for any other kind of additional processing in accordance with business rules and requirements. This demo stored procedure usp_XMLTest acceps  an input parameter, which is formatted as the XML shown in Figure 1.2.

<Orders>
  <Product>
    <ProductID>1</ProductID>
    <ProductName>Chai</ProductName>
    <CompanyName>Lehmanns Marktstand</CompanyName>
    <ShippersName>Speedy Express</ShippersName>
    <OrderDate>12/07/2013</OrderDate>
    <UnitPrice>18.0000</UnitPrice>
    <Quantity>40</Quantity>
    <Discount>0.15</Discount>
  </Product>
  <Product>
    <ProductID>2</ProductID>
    <ProductName>Chang</ProductName>
    <CompanyName>Lehmanns Marktstand</CompanyName>
    <ShippersName>Speedy Express</ShippersName>
    <OrderDate>12/07/2013</OrderDate>
    <UnitPrice>19.0000</UnitPrice>
    <Quantity>20</Quantity>
    <Discount>0.15</Discount>
  </Product>
  <Product>
    <ProductID>16</ProductID>
    <ProductName>Pavlova</ProductName>
    <CompanyName>Lehmanns Marktstand</CompanyName>
    <ShippersName>Speedy Express</ShippersName>
    <OrderDate>12/07/2013</OrderDate>
    <UnitPrice>17.4500</UnitPrice>
    <Quantity>30</Quantity>
    <Discount>0.15</Discount>
  </Product>
</Orders>

Figure 1.2

It goes without saying that there can be a lot more <Product></Product> nodes in the XML fragment.

There are multiple ways XML in a similar format may be produced or constructed. Here is one of the way we can simulate it for testing purposes:

The first step is to declare the XML fragment.

declare @x xml
-- simulate an XML fragment (for the demo further shown below in 
-- step 3  as a stored procedure call):

    SELECT @x = ( SELECT Products.ProductID
                      , Products.ProductName
                      , Customers.CompanyName
                      , Shippers.CompanyName AS ShippersName
                      , CONVERT(VARCHAR(20), GETDATE(), 101) AS OrderDate
                      , [Order Details].UnitPrice
                      , [Order Details].Quantity
                      , CAST([Order Details].Discount AS VARCHAR(9)) AS Discount
                    FROM Products
                        INNER JOIN [Order Details]
                        ON Products.ProductID = [Order Details].ProductID
                        INNER JOIN Orders
                        ON [Order Details].OrderID = Orders.OrderID
                        INNER JOIN Customers
                        ON Orders.CustomerID = Customers.CustomerID
                        INNER JOIN Shippers
                        ON Orders.ShipVia = Shippers.ShipperID
                    WHERE Orders.OrderDate = '1998-05-05'
                FOR
                  XML PATH('Product')
                    , ROOT('Orders')
                )
    Go

The result of the above assignment is shown above, in Figure 1.2.

Next we create our stored procedure.

   Use Northwind;
      GO
      CREATE PROC usp_XMLTest @x XML
      AS -- use XML /xPath:
          SELECT c.value('ProductID[1]', 'int') AS ProductID
                , c.value('ProductName[1]', 'nvarchar(100)') AS ProductName
                , c.value('CompanyName[1]', 'nvarchar(100)') AS CompanyName
                , c.value('ShippersName[1]', 'nvarchar(100)') AS ShippersName
                , c.value('OrderDate[1]', 'datetime') AS OrderDate
                , c.value('UnitPrice[1]', 'money') AS UnitPrice
                , c.value('Quantity[1]', 'int') AS Quantity
                , c.value('Discount[1]', 'real') AS Discount
                , Products.ProductID
                , Shippers.ShipperID
                , Customers.CustomerID
              FROM @x.nodes('Orders/Product') AS T ( C )
                  JOIN Products
                  ON Products.ProductID = c.value('ProductID[1]', 'int')
                  JOIN Shippers
                  ON Shippers.CompanyName = c.value('ShippersName[1]',
                                                    'nvarchar(100)')
                  JOIN Customers
                  ON Customers.CompanyName = c.value('CompanyName[1]',
                                                     'nvarchar(100)')
      Go

Step 3 is to call the stored procedure, passing the above XML to it.

      EXEC usp_XMLTest @x
      GO

This is the result of calling the above demo stored procedure in Northwind database:

Figure 1.3.

The advantage of XML type as a parameter is that one XML fragment can hold more than one table. The disadvantage is it requires the use of the XPath language. Depending on XML complexity, it can also be server resource hungry. However, it is still the most common choice for UI developers.

Let’s review the next option.

User-Defined Table type option (UDTT)

The UDTT is available from SQL Server 2008 onwards. Finally developers can send an array of data to a stored procedure that can be used without any additional work. Compared to the XML type, the User-Defined Table type is much lighter and ready to use. No additional parsing or processing is required when a stored procedure has a parameter declared as a Table type, but the Table still needs to be defined while declaring the type variable. Let’s go over this process:

First, we create User-Defined Table type:

    USE [Northwind]
    GO
    CREATE TYPE TblParam AS TABLE(
    [ProductName] [nvarchar](40) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [ShippersName] [nvarchar](40) NOT NULL,
    [OrderDate] [varchar](20) NULL,
    [UnitPrice] [money] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [Discount] [varchar](9) NULL
    )
    GO  

Now we create the stored procedure with a User-Defined Table type parameter:

  CREATE PROCEDURE usp_TableTypeTest @tbl dbo.TblParam READONLY
  AS
      SELECT TblType.ProductName
            , TblType.CompanyName
            , TblType.ShippersName
            , TblType.OrderDate
            , TblType.UnitPrice
            , TblType.Quantity
            , TblType.Discount
            , Products.ProductID
            , Shippers.ShipperID
            , Customers.CustomerID
          FROM @tbl AS TblType
              JOIN Products
              ON Products.ProductName = TblType.ProductName
              JOIN Shippers
              ON Shippers.CompanyName = TblType.ShippersName
              JOIN Customers
              ON Customers.CompanyName = TblType.CompanyName
  GO

Now we simulate User-Defined Table parameter assignment to be used in a call to the above stored procedure:

  DECLARE @TblParam dbo.TblParam

  -- populate the in-memory table (for this demo purpose):

  INSERT @TblParam
          SELECT Products.ProductName
                , Customers.CompanyName
                , Shippers.CompanyName AS ShippersName
                , CONVERT(VARCHAR(20), GETDATE(), 101) AS OrderDate
                , [Order Details].UnitPrice
                , [Order Details].Quantity
                , CAST([Order Details].Discount AS VARCHAR(9)) AS Discount
              FROM Products
                  INNER JOIN [Order Details]
                  ON Products.ProductID = [Order Details].ProductID
                  INNER JOIN Orders
                  ON [Order Details].OrderID = Orders.OrderID
                  INNER JOIN Customers
                  ON Orders.CustomerID = Customers.CustomerID
                  INNER JOIN Shippers
                  ON Orders.ShipVia = Shippers.ShipperID
              WHERE Orders.OrderDate = '1998-05-05'

Call the stored procedure:

EXEC usp_TableTypeTest @TblParam
GO

NOTE: Steps 3 and 4 must be run as one batch, as shown in Figure 1.4 below:

Figure 1.4.

The only disadvantage (or rather limitation) of using the User-Defined Table type is that UDTT is only available from SQL Server 2008 onward, so if you have SQL Server 2005 then UDTT is not an option for you. It also must be noted and remembered that it is necessary to pre-create an object (Table type) before using one in a stored procedure, which may seem to some as an inconvenience. Data elements in User-Defined Table type cannot be modified, but it is a rather logical implementation of a Type, which is not supposed to ever be changed in definition under the same type name. To send a table variable to a stored procedure you must declare a variable as the type. If you declare a table variable the common way, for example:

  DECLARE @tbl TABLE
      (
        [ProductName] [nvarchar](40) NOT NULL
      , [CompanyName] [nvarchar](40) NOT NULL
      , [ShippersName] [nvarchar](40) NOT NULL
      , [OrderDate] [varchar](20) NULL
      , [UnitPrice] [money] NOT NULL
      , [Quantity] [smallint] NOT NULL
      , [Discount] [varchar](9) NULL
      )

and not as type (DECLARE @TblParam dbo.TblParam), you will receive the following error message:

Msg 206, Level 16, State 2, Procedure usp_TableTypeTest, Line 0 Operand type clash: table is incompatible with TblParam

Conclusion

In this article I outlined different options to send a table or multi row values into a stored procedure. Of course, everyone has personal preferences how to design a stored procedure with such tasks. My preference is the User-Defined Table type. I found that this technique is better optimized than the other methods discussed in this article. 

Total article views: 8273 | Views in the last 30 days: 160
 
Related Articles
FORUM

String Operation in Stored Procedure

String Operation in Stored Procedure

FORUM

Passing multi-valued parameter selections in to stored procedures

Problems with passing multi-valued parameter selections as a csv string in to stored procedure param...

FORUM

Sort a string in a stored procedure: not order by

Is there a way to sort a string in a stored procedure

SCRIPT

Search for a String in all Stored Procedures and beyond?

Search for String Occurence with this script in your stored procedures.

FORUM
Tags
stored procedures    
t-sql    
user-defined data types    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones