I'm going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years
Here's a challenge many developers face. I'll whittle it down to a very simple example, but one where the pattern applies to many situations.
Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id.
create
procedure
dbo.UpdateVendorOrders
@VendorID
int
As
Begin
update
Purchasing.PurchaseOrderHeader
set
Freight = Freight
+ 1
where
VendorID
=
@VendorID
end
go
Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If you've worked with SQL Server, you can probably guess where I'm going with this. The big question is – how do we pass a variable number of Vendor IDs? Or, stated more generally, how do we pass an array, or a table of keys, to a procedure? Something along the lines of…
exec
dbo.UpdateVendorOrders
@SomeListOfVendors
Over the years, developers have come up with different methods:
- Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (http://code-magazine.com/articleprint.aspx?quickid=0503071&printmode=true, Tip #3)
- In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (http://code-magazine.com/articleprint.aspx?quickid=0703041&printmode=true, Tip #12)
- Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure.
All of these certainly work, and developers have had to use these techniques before – because for years there was NO WAY to directly pass a table to a SQL Server stored procedure.
Until SQL Server 2008 – when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure.
Now, it does require a few steps. We can't just pass "any old table" to a procedure. It has to be a pre-defined type (a template). So let's suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc.
CREATE
TYPE
IntKeysTT
AS
TABLE
(
[IntKey]
[int]
NOT
NULL
)
GO
So I've created a Table Type…called IntKeysTT. It's defined to have one column – an IntKey.
Now…suppose I want to load it with Vendors who have a Credit Rating of 1…..and then take that "list" of Vendor keys and pass it to a procedure:
DECLARE
@VendorList
IntKeysTT
INSERT
INTO
@VendorList
SELECT
BusinessEntityID
from
Purchasing.Vendor
WHERE
CreditRating
= 1
So, I now have a table type variable – not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). It's in server memory (unless it needs to spill to tempDB) and is therefore "private" to the connection/process.
OK, can I pass it to the stored procedure now? Well, not yet – we need to modify the procedure to receive a table type. Here's the code:
create
procedure
dbo.UpdateVendorOrdersFromTT
@IntKeysTT
IntKeysTT
READONLY
As
Begin
update
Purchasing.PurchaseOrderHeader
set
Freight = Freight
+ 1
FROM
Purchasing.PurchaseOrderHeader
JOIN
@IntKeysTT
TempVendorList
ON
PurchaseOrderHeader.VendorID =
TempVendorList.IntKey
End
go
Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you won't want to – you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable.
So there you have it. A bit of work to set up the table type, but in my view, definitely worth it.
Additionally, if you pass values from .NET, you're in luck. You can pass an ADO.NET data table (with the same tablename property as the name of the Table Type) to the procedure. For .NET developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit!!!
Finally – I want to talk about another approach people have used over the years….SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way:
set
nocount
on
DECLARE
@VendorID
int
DECLARE
db_cursor
CURSOR
FAST_FORWARD
FOR
SELECT
BusinessEntityID
from
Purchasing.Vendor
where
CreditRating
= 1
OPEN
db_cursor
FETCH
NEXT
FROM
db_cursor
INTO
@VendorID
WHILE
@@FETCH_STATUS
= 0
BEGIN
EXEC
dbo.UpdateVendorOrders
@VendorID
FETCH
NEXT
FROM
db_cursor
INTO
@VendorID
END
CLOSE
db_cursor
DEALLOCATE
db_cursor
The best thing I'll say about this is that it "works". And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. That's not a good idea in a large production environment. Additionally, the more the # of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be.
When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger # of vendors (84 vendors), the different was staggering – 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster.
Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature that's more closely modeled to the way developers are thinking – specifically, "how do we pass a table to a procedure?" Now we have an answer!
Hope you find this feature help. Feel free to post a comment.
KG