DaveWentzel.com            All Things Data

Tips on Writing Better Stored Procedures

 

[Freelinking: unknown plugin indicator "Pattern"]
Stored Procedure Logging ... this is a routine that captures runtime metrics (IO, cpu time, duration) within any stored procedure. 
 
how to use a stored procedure as a derived table

Here’s a code sample which uses the recordset returned by the execution of a stored procedure as a derived table.


SELECT a.pub_id, b.pub_name, a.title_id, a.price, a.pubdate

FROM OPENROWSET('SQLOLEDB','servername'; 'username'; 'password',

   'set fmtonly off exec Pubs.dbo.reptq1') AS a

inner join publishers b

on a.pub_id = b.pub_id

 
You first have to enable this using:
sp_configure 'show advanced options', 1

reconfigure

go

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure
 
The provider SQLNCLI can be used instead of SQLOLEDB on 2005/2008.  This might give you slightly better performance. 
 
This is also a useful trick to store the results of a stored procedure in a table without first declaring the table.  Remember, you can perform an INSERT...EXEC, but that requires a table with a matching schema first. 
 
Running SET FMTONLY OFF immediately before the exec will ensure you get the results and not just the metadata. 
 
This will not work if a resultset has duplicate column names (rare, but it happens with sp_who2 for instance). 
 
 
How to Share Data Between Stored Procedures
There are lots of caveats, these are just some hints I've found worked well, your mileage may vary. 
 
When sharing temp tables the caller must always create the temp table.  Of course this leads to having to modify lots of procedures when a new data element is needed.  Solution is to create the temp table in the caller with a DUMMY column.  Then the called procedure simply adds cols as it needs them using ALTER.  This logically makes the most sense.  Makes writing a test harness much easier as well. 
 
You can also use a Process-Keyed table or spid-keyed table.  This is a permanent table in the db that predefines the cols you will need.  @@spid is used to ensure concurrency in the permanent table.  Add a clustered index to at least the spid column.  Consider disabling auto stats for the table as well (sp_autostats). 
 
You can also use Table-valued Parameters, with some caveats. 
 
 

Add new comment