Generating Print Statements for Stored Procedure Arguments

 

Here’s a little utility I wrote which, given a target stored procedure name, generates PRINT statements for all the arguments. This can be included at the top of the target sproc and run to view the actual values passed at runtime.

I was in two minds whether to post this at all, as it has very little use beyond trying to log runtime values for a target sproc which is called internally inside another sproc. For all other cases the SQL SERVER profiler should suffice. But, since we do have nested calls in our application and I thought maybe some one else does too, I am posting it.

A word on TSQL_SPs Trace Template:

Now I know we can profile nested sproc calls using the TSQL_SPs Trace Template, but Profiler does not capture the actual values passed in to the nested call if they are dynamic.

For example:

case 1: This gets profiled correctly

CREATE PROCEDURE OUTER_SPROC
(
	:
	:
	EXEC INNER_SPROC 'THIS','IS','HARD-CODED'
	:
	:
)
Profiler output:

EXEC INNER_SPROC 'THIS','IS','HARD-CODED'

case 2: This does not

CREATE PROCEDURE OUTER_SPROC
(
	DECLARE @VAR1 VARCHAR
	DECLARE @VAR2 VARCHAR
	DECLARE @VAR3 VARCHAR
	
	SET @VAR1 = 'THIS'
	SET @VAR1 = 'IS'
	SET @VAR1 = 'DYNAMIC'
	:
	EXEC INNER_SPROC @VAR1,@VAR2,@VAR3
	:
	:
)

EXEC INNER_SPROC @VAR1,@VAR2,@VAR3

And so, my little utility

USE [UtilsDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sajjan Sarkar
-- Create date: 
-- Description:	Utility to generate print statements for a target sproc
-- =============================================
ALTER PROCEDURE [dbo].[usp_Utils_PrintArgs]
    (
      @DBName VARCHAR(100) = '' ,
      @SprocName VARCHAR(100) = ''
    )
AS 
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON ;
	
        DECLARE @execGetParms VARCHAR(500)
        DECLARE @ArgName VARCHAR(100)
        DECLARE @ArgType VARCHAR(100)
        DECLARE @PrintString VARCHAR(8000)
         
        -- this string will contain the print statements
        SET @PrintString = ''
		-- sp_procedure_params_rowset returns the arguments  for a sproc. this has to be a dynamic 
		-- SQL so that we can  run it for sprocs across databases
        SET @execGetParms = 'exec [' + @DBName + ']..sp_procedure_params_rowset N''' + @SprocName + ''',1,NULL,NULL'
  	
		-- temp table to consume the results of the sp_procedure_params_rowset call
        CREATE TABLE #tmpArgs
            (
              PROCEDURE_CATALOG VARCHAR(100) ,
              PROCEDURE_SCHEMA VARCHAR(100) ,
              PROCEDURE_NAME VARCHAR(100) ,
              PARAMETER_NAME VARCHAR(100) ,
              ORDINAL_POSITION INT ,
              PARAMETER_TYPE INT ,
              PARAMETER_HASDEFAULT INT ,
              PARAMETER_DEFAULT VARCHAR(100) ,
              IS_NULLABLE INT ,
              DATA_TYPE INT ,
              CHARACTER_MAXIMUM_LENGTH INT ,
              CHARACTER_OCTET_LENGTH INT ,
              NUMERIC_PRECISION INT ,
              NUMERIC_SCALE INT ,
              DESCRIPTION VARCHAR(100) ,
              TYPE_NAME VARCHAR(100) ,
              LOCAL_TYPE_NAME VARCHAR(100)
            )
		
        INSERT  INTO #tmpArgs
                ( PROCEDURE_CATALOG ,
                  PROCEDURE_SCHEMA ,
                  PROCEDURE_NAME ,
                  PARAMETER_NAME ,
                  ORDINAL_POSITION ,
                  PARAMETER_TYPE ,
                  PARAMETER_HASDEFAULT ,
                  PARAMETER_DEFAULT ,
                  IS_NULLABLE ,
                  DATA_TYPE ,
                  CHARACTER_MAXIMUM_LENGTH ,
                  CHARACTER_OCTET_LENGTH ,
                  NUMERIC_PRECISION ,
                  NUMERIC_SCALE ,
                  DESCRIPTION ,
                  TYPE_NAME ,
                  LOCAL_TYPE_NAME
		        )
                EXEC ( @execGetParms
                    )
        --  loop thru each arg and build the string          
        DECLARE cursorDB CURSOR READ_ONLY
        FOR
            SELECT  PARAMETER_NAME ,
                    TYPE_NAME
            FROM    #tmpArgs AS TA
            ORDER BY ORDINAL_POSITION ASC 
                   
        OPEN cursorDB
        
        FETCH NEXT FROM cursorDB INTO @ArgName, @ArgType
        WHILE ( @@fetch_status <> -1 ) 
            BEGIN
                IF ( @@fetch_status <> -2 ) 
                    BEGIN   
                        IF @ArgName != '@RETURN_VALUE'  -- ignore the default @RETURN_VALUE arg
                            BEGIN
								-- cast to string depending on type of arg
                                SET @PrintString = @PrintString + ' PRINT ''' + @ArgName + ':''+ CASE WHEN ' + @ArgName + ' IS NULL THEN ''NULL'' ELSE '
                                    + CASE WHEN @ArgType IN ( 'int', 'bit', 'numeric' ) THEN ' CAST(' + @ArgName + ' AS VARCHAR(100)) '
                                           WHEN @ArgType LIKE 'date%' THEN ' CAST(' + @ArgName + ' AS VARCHAR)'
                                           ELSE @ArgName
                                      END + ' END ' + CHAR(13)
                            END                
                       
                    END 
                FETCH NEXT FROM cursorDB INTO @ArgName, @ArgType
            END--WHILE (@@fetch_status <> -1)                   
                    
        CLOSE cursorDB
        DEALLOCATE cursorDB
	
		-- print output
        PRINT @PrintString
        
        DROP TABLE #tmpArgs
	             
    END

Example Usage:

exec UtilsDB..usp_Utils_PrintArgs 'MyTargetDB' ,'INNER_SPROC'

output

PRINT '@VAR1:'+ CASE WHEN @VAR1 IS NULL THEN 'NULL' ELSE @VAR1 END 
PRINT '@VAR2:'+ CASE WHEN @VAR2 IS NULL THEN 'NULL' ELSE @VAR2 END 
PRINT '@VAR2:'+ CASE WHEN @VAR2 IS NULL THEN 'NULL' ELSE @VAR2 END 

It also handles casting non-string arguments to their string forms, so if your arguments were String,INT and DATETIME

PRINT '@VAR1:'+ CASE WHEN @VAR1 IS NULL THEN 'NULL' ELSE @VAR1 END 
PRINT '@VAR2:'+ CASE WHEN @VAR2 IS NULL THEN 'NULL' ELSE CAST(@VAR2 AS VARCHAR(100)) END 
PRINT '@VAR3:'+ CASE WHEN @VAR3 IS NULL THEN 'NULL' ELSE CAST(@VAR3 AS VARCHAR) END 

Now just paste the PRINT statements into INNER_SPROC and run the outer one :)