0

I have lot many Stored Procedures that takes 5 to 50 parameters and my client's requirement is to logging each parameters value dynamically while SP is running. Currently we are writing manually code for each SP. I have to create some generic solution for this. But as variables can not be used in child object or in dynamic query it looks difficult.

I want list of parameters with values like

@ID=1 , @NAME='ABC' ,..., @STGFlag=0

CREATE PROCEDURE [dbo].[LOAD_TEST]
(
    @ID INT,
    @NAME NVARCHAR(MAX)
    .
    .
    .
    @STGFlag    bit
)
AS
BEGIN

    DECLARE @PARAMETERS NVARCHAR(MAX);  
    --this will be generic code in all SPs to get parameters with value.
    SELECT @PARAMETERS=[dbo].[PARAMS]( OBJECT_ID(@@PROCID))

    --EXPECTED RESULT in @PARAMETERS
    @ID=1 , @NAME='ABC' ,..., @STGFlag=0

END

I am trying to create function that can return a dynamic query that can run by main caller Sp and can get expected result. But I am not able to find any way to bind variable in dynamic query, as even I don't have fix name of variables. I am trying to get it from INFORMATION_SCHEMA.PARAMETERS.

ALTER FUNCTION [dbo].[PARAMS](@PROC_NAME VARCHAR(200))
RETURNS NVARCHAR(MAX) 
AS
BEGIN
        Declare     @Parameters nvarchar(max)=''
                ,   @ParamSingle nvarchar(max)
        Declare     @ParamList AS TABLE
        (
            PARAMETER_NAME varchar(max)
            ,DATA_TYPE varchar(max)
            ,ORDINAL_POSITION int
        );
        Declare @counter int=1;

        INSERT INTO @ParamList
        SELECT PARAMETER_NAME,DATA_TYPE,ORDINAL_POSITION        
        from INFORMATION_SCHEMA.PARAMETERS
        WHERE SPECIFIC_NAME=@PROC_NAME

        WHILE((select count(1) FROM @ParamList) >= @counter)
        BEGIN
            SELECT @ParamSingle = 
            CONCAT('"',PARAMETER_NAME,'="',',',
            CASE WHEN DATA_TYPE in ('int'
                                    ,'decimal'
                                    ,'smallint'
                                    ,'numeric'
                                    ,'tinyint'
                                    ,'bigint'
                                    ,'bit')
                 THEN PARAMETER_NAME
                 WHEN DATA_TYPE in ( 'varchar'
                                    ,'uniqueidentifier'
                                    ,'datetime'
                                    ,'nvarchar')
                 
                 THEN '"""",'+PARAMETER_NAME+',"""" '
                 ELSE ''
            END
            )
            FROM @ParamList WHERE ORDINAL_POSITION = @counter

            SET @Parameters = CONCAT(@Parameters, @ParamSingle,'," ,", ')
            SET @counter=@counter+1;            
        END
        SET @Parameters=REPLACE(@Parameters,'"','''');
        return concat('SELECT @PARAMETERS=CONCAT(',LEFT(@Parameters,LEN(@Parameters)-6),')');
END
Jigar Parekh
  • 595
  • 10
  • 23
  • You'll need to hard-code the parameter names at some point to get the values. Perhaps tracing is a better solution. – Dan Guzman Apr 21 '21 at 10:36
  • 1
    What about using `FOR XML` to get it as one big XML blob that you can dump into a logging table? 50 parameters on a stored procedure sounds like bad design to be honest – Charlieface Apr 21 '21 at 10:37
  • No I can't use XML, that is what challanging requirement and it looks like not possible to achieve as parameter list is dynamic – Jigar Parekh Apr 27 '21 at 10:41

0 Answers0