Nested Stored Procedures


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.

Using Temporary Tables to Pass a Recordset to a Nested Stored Procedure

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.

Using a Cursor to Pass a Recordset to a Nested Stored Procedure

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 

How to Process the Result Set of a Stored Procedure

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.

image from book
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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net