Nested stored procedures are simply stored procedures that were called by other stored procedures. Using SQL Server 2005, it is possible to do 32 levels of nesting. You can investigate the current nesting level using the @@nestlevel function. This section explores methods for passing recordsets between a nested stored procedure and its caller.
Some programming languages (such as Visual Basic and Pascal) use the concept of global and module variables. These types of variables are very useful for passing complex parameters (like arrays or recordsets) to a procedure when its parameter list supports only basic data types.
The same problem exists with stored procedures. You cannot pass a recordset through a parameter list to a stored procedure from the current batch or stored procedure, and neither recordsets nor local variables from the outer stored procedure (or batch) are visible to the inner stored procedure unless they are passed as a parameter to that procedure.
Unfortunately, SQL Server does not support "user-defined global variables." Modules, and therefore module variables, do not even exist in Transact-SQL.
One way to pass a recordset is to create and fill a temporary table and then reference that temporary table from the inner stored procedure, which will be able to see and access its contents. The following example consists of two stored procedures. The first is business-oriented and collects a list of properties associated with an inventory asset. The list is implemented as a temporary table:
Alter Procedure dbo.ap_InventoryProperties_Get_wTempTb1Outer /* Return comma-delimited list of properties that are describing asset. i.e.: Property = Value unit;Property = Value unit;Property = Value unit; Property = Value unit; Property = Value unit; Property = Value unit; --test: exec dbo.ap_InventoryProperties_Get_wTempTblOuter 5 */ @intInventoryId int As set nocount on declare @chvProperties varchar(max) Create table #List(Id int identity(1,1), Item varchar(255)) -- identify Properties associated with asset insert into #List (Item) select Property + '=' + Value + ' ' + Coalesce(Unit, '') + '; ' from InventoryProperty inner join Property on InventoryProperty.PropertyId = Property.PropertyId where InventoryProperty.InventoryId = @intlnventoryld -- call sp that converts records to a single varchar exec util.ap_TempTb12Varchar @chvProperties OUTPUT -- display result select @chvProperties Properties drop table #List return 0 go
The second stored procedure, the nested stored procedure, is not business-oriented—unlike the caller stored procedure, the nested stored procedure does not implement the business rule. It simply loops through the records in the temporary table (which was created in the caller stored procedure) and assembles them into a single varchar variable:
Alter Procedure util.ap_TempTbl2Varchar -- Convert information from #List temporary table to a single varchar @chvResult varchar(max) output As set nocount on declare @intCountItems int, @intCounter int, @chvItem varchar(255) -- set loop select @intCountItems = Count(*), @intCounter = 1, @chvResult = ' ' from #List -- loop through list of items while @intCounter <= @intCountItems begin -- get one property select @chvItem = Item from #List where Id = @intCounter -- assemble list set @chvResult = @chvResult + @chvItem -- let's go another round and get another item set @intCounter = @intCounter + 1 end return 0 go
You can execute the outer stored procedure from Management Studio:
exec dbo.ap_InventoryProperties_Get_TempTblOuter 5
SQL Server will return the result in the form of a string:
Properties ---------------------------------------------------------------- CPU=Pentium II ; RAM=64 MB; HDD=6.4 GB; Resolution=1024x768 ; Weight=2 kg; Clock=366 MHz;
You may question when this kind of solution is justified and whether these stored procedures are coupled. It is true that neither of these stored procedures can function without the other. If you have other stored procedures that also use util.ap_TempTbl2Varchar, I would consider this solution justified.
Similar solutions can be implemented using cursors. Cursors are also visible to, and accessible from, nested stored procedures.
The following example also consists of two stored procedures. The first is business-oriented and creates a cursor with properties associated with specified inventory:
create procedure dbo.ap_InventoryProperties_Get_wNestedCursor /* Return comma-delimited list of properties that are describing asset. i.e.: Property = Value unit;Property = Value unit;Property = Value unit; Property = Value unit; Property = Value unit; Property = Value unit; --test: declare @chvResult varchar(max) exec dbo.ap_InventoryProperties_Get_wNestedCursor 5, @chvResult OUTPUT, 1 select @chvResult */ ( @intInventoryId int, @chvProperties varchar(max) OUTPUT, @debug int = 0 ) As Select @chvProperties = '' Declare curltems Cursor For Select Property + '=' + [Value] + ' ' + Coalesce([Unit], '') + '; ' Item From InventoryProperty Inner Join Property On InventoryProperty.PropertyId = Property.PropertyId Where InventoryProperty.InventoryId = @intInventoryId Open curItems Exec util.ap_Cursor2Varchar @chvProperties OUTPUT, @debug Close curItems Deallocate curItems Return 0 Go
The second stored procedure is generic and converts information from a cursor into a single variable:
ALTER Procedure util.ap_Cursor2Varchar -- Process information from cursor initiated in calling sp. -- Convert records into a single varchar. ( @chvResult varchar(max) OUTPUT, @debug int = 0 As Declare @chvItem varchar(255) set @chvResult = ' ' Fetch Next From curItems Into @chvItem While (@@FETCH_STATUS = 0) Begin If @debug <> 0 Select @chvItem Item -- assemble list Set @chvResult = @chvResult + @chvItem If @debug <> 0 Select @chvResult chvResult Fetch Next From curItems Into @chvItem End Return
You can execute the outer stored procedure from Management Studio:
declare @chvResult varchar(max) exec dbo.ap_InventoryProperties_Get_wNestedCursor 5, @chvResult OUTPUT, 1 select @chvResult
From time to time, you will encounter stored procedures that return result sets that you need to process. This is not as simple as it sounds.
One option is to receive the result set in a client application or middleware component and process it from there. Sometimes this option is not acceptable, for a variety of reasons. For example, the result set might be too big, in which case network traffic could be considerably increased. Since the result set needs to be transferred to the middleware server before it is processed, the performance of the system could be degraded. There might be security implications—for example, you may determine that a user should have access only to a segment of a result set and not to the complete result set.
An alternative option is to copy the source code of the stored procedure into your stored procedure. This could be illegal, depending on the source of the original stored procedure. It also reduces the maintainability of your code, since you have two copies to maintain; if the other stored procedure is a system stored procedure, Microsoft can change its internals with the release of each new version of SQL Server. Your stored procedure will then need to be changed.
It is possible to collect the result set of a stored procedure in Transact-SQL code. You need to create a (temporary) table, the structure of which matches the structure of the result set exactly, and then redirect (insert) the result set into it. Then you can do whatever you want with it.
The following stored procedure uses the sp_dboption system stored procedure to obtain a list of all database options and to obtain a list of database options that are set on the Asset5 database. Records that have a structure identical to that of the result set as returned by the stored procedure are collected in temporary tables. The Insert statement can then store the result set in the temporary table. The contents of the temporary tables are later compared and a list of database options not currently set is returned to the caller.
Create Procedure util.ap_NonSelectedDBOption_List -- return list of non-selected database options -- test: exec util.ap_NonSelectedDBOption_List 'Asset5' ( @chvDBName sysname ) As Set Nocount On Create Table #setable (name nvarchar(35)) Create Table #current (name nvarchar(35)) -- collect all options Insert Into #setable Exec sp_dboption -- collect current options Insert Into #current Exec sp_dboption @dbname = @chvDBName -- return non-selected Select name non_selected From #setable Where name not in (Select name From #current) Drop Table #setable Drop Table #current Return 0
The only trouble with this method is that you need to know the structure of the result set of the stored procedure in advance in order to create a table with the same structure, although this is not a problem for user-defined stored procedures. It used to be a problem for system stored procedures, but SQL Server Books OnLine now provides information regarding the result sets generated by these stored procedures.
Note | Unfortunately, it is not possible to capture the contents of a result set if a stored procedure returns more than one result set. |
This technique also works with the Exec statement. For example, if you try to collect a result set from the DBCC command in this way, SQL Server will return an error. But you can encapsulate the DBCC statement in a string and execute it from Exec.
The following stored procedure returns the percentage of log space used in a specified database:
Create Procedure util.ap_LogSpacePercentUsed_Get /* -- Return percent of space used in transaction log for -- the specified database. --test: declare @fltUsed float exec util.ap_LogSpacePercentUsed_Get 'Assets', @fltUsed OUTPUT select @fltUsed Used */ ( @chvDbName sysname, @fItPercentUsed float OUTPUT ) As Set Nocount On Create Table #DBLogSpace ( dbname sysname, LogSizelnMB float, LogPercentUsed float, Status int ) -- get log space info, for all databases Insert Into #DBLogSpace Exec ('DBCC SQLPERF (LogSpace)') -- get percent for specified database select OfItPercentUsed = LogPercentUsed from #DBLogSpace where dbname = @chvDbName drop table #DBLogSpace return
You can test this stored procedure from Management Studio, as shown on Figure 15-3.
Figure 15-3: Percentage of log space used in specified database
These techniques were extremely important before SQL Server 2000. In the last two versions it is now possible to use the table data type as a return value for user-defined functions. You learned how to use table-valued user-defined functions in Chapter 10. Unfortunately, it is still not possible to use a table variable as the output parameter of a stored procedure.
You have another option available when you want to pass a result set (or multiple result sets) to a calling stored procedure—you can use the cursor data type as the output parameter of a stored procedure. In the following example, ap_InventoryProperties_Get_wCursor creates and opens a cursor. The content of the cursor is then returned to the calling procedure.
Create Procedure dbo.ap_InventoryProperties_Get_wCursor -- Return Cursor that contains properties -- that are describing selected asset. ( @intInventoryId int, @curProperties Cursor Varying Output ) As Set @curProperties = Cursor Forward_0nly Static For Select Property, Value, Unit From InventoryProperty inner join Property On InventoryProperty.PropertyId = Property.PropertyId Where InventoryProperty.InventoryId = @intInventoryId Open @curProperties Return
The preceding stored procedure will be called from the following stored procedure:
Create Procedure dbo.ap_InventoryProperties_Get_UseNestedCursor -- return comma-delimited list of properties -- that are describing asset. -- i.e.: Property = Value unit;Property = Value unit; -- Property = Value unit;Property = Value unit;... ( @intInventoryId int, @chvProperties varchar(max) OUTPUT, @debug int = 0 ) As Declare @intCountProperties int, @intCounter int, @chvProperty varchar(50), @chvValue varchar(50), @chvUnit varchar(50), @insLenProperty smallint, @insLenValue smallint, @insLenUnit smallint, @insLenProperties smallint Set @chvProperties = ' ' Declare @CrsrVar Cursor Exec dbo.ap_InventoryProperties_Get_wCursor @intInventoryId, @CrsrVar Output Fetch Next From @CrsrVar Into OchvProperty, @chvValue, @chvUnit While (@@FETCH_STATUS = 0) Begin Set @chvUnit = Coalesce(@chvUnit, '') If @debug <> 0 Select @chvProperty Property, @chvValue [Value], @chvUnit [Unit] -- assemble list Set @chvProperties = @chvProperties + @chvProperty + '=' + @chvValue + ' ' + @chvUnit + '; ' If @debug <> 0 Select @chvProperties chvProperties Fetch Next From @CrsrVar Into OchvProperty, @chvValue, @chvUnit End Close @CrsrVar Deallocate @CrsrVar Return
It is the responsibility of the caller to properly close and deallocate the cursor at the end.
Tip | You should not use a cursor as an output parameter of a stored procedure unless you have to. Such a solution is inferior because procedures are coupled and prone to errors. You should use table-valued user-defined functions as part of your Select statements instead. |