Stored procedure expects parameter 'Incorrect syntax near ':'.'

Question

I have the following stored procedure in SQL Server:

CREATE PROCEDURE [PROC_SET_BRN_RPT_STSTC]  
    @I_RPT_I varchar(max),
    @I_MSTR_TBL varchar(max)
AS 
BEGIN
    DECLARE
        @VD_PSTG_D datetime2(0),
        @V_SQL varchar(4000),
        @BMG_RPT_I varchar(5) = ISNULL(@I_RPT_I, '') + 'M',
        @MAM_RPT_I varchar(5) = ISNULL(@I_RPT_I, '') + 'P';

    /*vd_pstg_d   :=  GET_DATE ('TDTD');*/
    SELECT @VD_PSTG_D = max(TIME_DIMN.DATE_D)
    FROM TIME_DIMN
    WHERE TIME_DIMN.TME_DIMN_I IN ( 'TDTD', 'TDSH' );

    DECLARE @SQLString NVARCHAR(500); 
    SET @SQLString = 'Delete From RPT_STSTC Where TXN_PSTG_D = :1 and Rpt_i = :2';
    Execute sp_executesql @SQLString, @vd_pstg_d, @I_Rpt_I;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

When I try to execute the stored procedure, I get the following error:

Incorrect syntax near ':'.

Do you know what would be the issue?

Answer 1

As pointed out in the comment on your question, there is no need for dynamic SQL in this stored procedure. You could just rewrite it:

CREATE PROCEDURE [PROC_SET_BRN_RPT_STSTC]  
    @I_RPT_I varchar(max),
    @I_MSTR_TBL varchar(max)
AS 
BEGIN
    DECLARE
        @VD_PSTG_D datetime2(0),
        @V_SQL varchar(4000),
        @BMG_RPT_I varchar(5) = ISNULL(@I_RPT_I, '') + 'M',
        @MAM_RPT_I varchar(5) = ISNULL(@I_RPT_I, '') + 'P';

    /*vd_pstg_d   :=  GET_DATE ('TDTD');*/
    SELECT @VD_PSTG_D = max(TIME_DIMN.DATE_D)
    FROM TIME_DIMN
    WHERE TIME_DIMN.TME_DIMN_I IN ( 'TDTD', 'TDSH' );

    DELETE FROM RPT_STSTC 
    WHERE TXN_PSTG_D = @vd_pstg_d AND Rpt_i = @I_Rpt_I;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

Only use sp_executesql if you absolutely need to use it - here you do not need it.

Just to answer your question, here is what you could have done (but it is not advised in this simple scenario):

CREATE PROCEDURE [PROC_SET_BRN_RPT_STSTC]  
    @I_RPT_I varchar(max),
    @I_MSTR_TBL varchar(max)
AS 
BEGIN
    DECLARE
        @VD_PSTG_D datetime2(0),
        @V_SQL varchar(4000),
        @BMG_RPT_I varchar(5) = ISNULL(@I_RPT_I, '') + 'M',
        @MAM_RPT_I varchar(5) = ISNULL(@I_RPT_I, '') + 'P';

    /*vd_pstg_d   :=  GET_DATE ('TDTD');*/
    SELECT @VD_PSTG_D = max(TIME_DIMN.DATE_D)
    FROM TIME_DIMN
    WHERE TIME_DIMN.TME_DIMN_I IN ( 'TDTD', 'TDSH' );

    DECLARE @SQLString NVARCHAR(500); 
    SET @SQLString = CONCAT('Delete From RPT_STSTC Where TXN_PSTG_D =''', @VD_PSTG_D, ''' AND Rpt_i=''', @I_RPT_I, '''');
    Execute sp_executesql @SQLString, @vd_pstg_d, @I_Rpt_I;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;