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?
Advertisement
Answer
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;