CODEWITHSUNDEEP
sql-server-2008stored-proceduresparametersdynamic-sql
Ram
Ram

Reputation: 825

Printing dynamic SQL with parameter values

I want to print the SQL query which is used in the below stored procedure with parameter values. Currently it is printing the SQL query without parameter values substitution while executing the stored procedure. It is printing the the parameters as it is (for example AND TP.F_LANGUAGE = @LANGUAGE)

CREATE PROCEDURE [dbo].[SEARCH]
    @LANGUAGE VARCHAR(2),
    @SUBFORMAT NVARCHAR(2000),
    @ICO NVARCHAR(4000),
AS
    SET NOCOUNT ON

    DECLARE @sqlQuery AS NVARCHAR(MAX)
    DECLARE @pdfQuery AS NVARCHAR(MAX)
    DECLARE @htmlQuery AS NVARCHAR(MAX)
    DECLARE @param AS NVARCHAR(3000)
    DECLARE @paramDefinition AS NVARCHAR(3000)

    DECLARE @AllSubformats AS SubformatType
    DECLARE @InputSubformats AS SubformatType

    INSERT INTO @AllSubformats(Val)
        SELECT S.Val
        FROM (SELECT IsNull(TLK.f_value, '') As FValue
              FROM T_LOOKUP TLK
              WHERE TLK.f_parent = 'WEBVIEWER_INT_SUB') TLV
        CROSS APPLY dbo.dfnSplit(TLV.FValue, ',') S;

    IF (@SUBFORMAT <> '-1')
    BEGIN
        INSERT INTO @InputSubformats(Val)
            SELECT S.Val
            FROM dbo.dfnSplit(@SUBFORMAT, ',') S
    END;

    SET @pdfQuery = 'SELECT   TOP 1001 TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS NAME,
        FROM PDF_DETAILS TP '
        WHERE TP.F_PRODUCT<>'''''


SET @param = ' AND TP.F_AUTHORIZED IN(1,3) AND EXISTS (SELECT 1 FROM @AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)'

IF NOT(@LANGUAGE IS NULL OR @LANGUAGE = '')
        SET @param = @param + '  AND TP.F_LANGUAGE = @LANGUAGE '

IF NOT(@SUBFORMAT IS NULL OR @SUBFORMAT = '')
        SET @param = @param + ' AND EXISTS (SELECT 1 FROM @InputSubformats ISF WHERE ISF.Val = TP.F_SUBFORMAT) '

IF NOT(@ICO IS NULL OR @ICO = '')
        SET @param = @param + ' AND (TP.F_ICO_DATA LIKE @ICO) '


    SET @ParamDefinition =  ' @SUBFORMAT NVARCHAR(2000),
                             @LANGUAGE VARCHAR(2), @ICO NVARCHAR(4000),@AllSubformats SubformatType READONLY, @InputSubformats SubformatType READONLY ' 

SET @sqlQuery = @pdfQuery +  @param 


    EXECUTE SP_EXECUTESQL @sqlQuery, @paramDefinition, @SUBFORMAT, @LANGUAGE, @ICO, @AllSubformats, @InputSubformats

Print @sqlQuery

Please help.I want to debug and execute the SQL QUERY.

Upvotes: 0

Views: 1874

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

You could PRINT the result of a REPLACE function. You would need to nest a REPLACE for every parameter in the string:

PRINT REPLACE(
        REPLACE(@sqlQuery, '@Parameter1', @Parameter1)
      , '@Parameter2', @Parameter2
);

Note this assumes all of your parameters are string types. If any of them are non-string, you will need to CAST them to strings inside the REPLACE.

Upvotes: 1

Related Questions