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