Pass value into stored procedure in T-SQL-CodePudding

I am trying to pass a variable @date into my T-SQL command (in DB in Synapse). What I want it to do is to delete data from a table by given date defined by user.

Here is my script, which works just fine, when I define date inside it:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [DPIT].[proc_nhs_delete_by_date] @date datetime, @TableName sysname AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

DECLARE @SchemaName sysname;

SET @SchemaName = N'DPIT';

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'DELETE FROM '   QUOTENAME(@SchemaName)   N'.'   QUOTENAME(@TableName)   N' WHERE datum = @date;';
EXEC sys.sp_executesql @SQL, N'@date datetime', @date

END

But when I execute this stored procedure and pass parameters into it, I get the following error:

Parse error at line: 3, column: 1: Incorrect syntax near 'EXEC'.

This is the code that creates the error:

DECLARE @return_value int

EXEC    @return_value = [DPIT].[proc_nhs_delete_by_date]
        @date = N'20231021',
        @TableName = sluzba_G

SELECT  'Return Value' = @return_value

GO

Do you know where the problem might be? I am inclining to believe that the problem is that Synapse probably does not support EXEC command and I will have to use sys.sp_executesql in some way, but to be honest, I do not know how.

Thank you very much for your help!

CodePudding user response：

Dynamic SQL aside, the problem you are experiencing is tooling. You cannot call a Stored Procedure in the default SSMS way in Synapse Dedicated SQL Pool (SQW Data Warehouse) using return.

Change this:

DECLARE @return_value int

EXEC    @return_value = [DPIT].[proc_nhs_delete_by_date]
        @date = N'20231021',
        @TableName = sluzba_G

SELECT  'Return Value' = @return_value

GO

To this:

EXEC    [DPIT].[proc_nhs_delete_by_date]
        @date = N'20231021',
        @TableName = sluzba_G

And it should work fine.

CodePudding user response：

@Larnu's comment needs highlighting:

Why are you using "dynamic" SQL here, when the names of the objects are static; you assign the variable a static value. There's not need for this logic

Your code is vastly overcomplex and scary (dynamic SQL should put the fear in to everyone!).

Your code can be simplified to this:

ALTER PROCEDURE DPIT.proc_nhs_delete_by_date (
 @date datetime
)
  AS
BEGIN
  SET NOCOUNT ON;

  DELETE
  FROM   dbo.some_table
  WHERE  datum = @date
  ;
END
;