SQL Server: fetching a variable name from a table and assigning value inside the stored procedure-CodePudding

I have some variable name stored in a table, which I need to populate in a stored procedure based on a condition.

For example:

Query: select column1 from TestTable
Output of Query: @FromDate

Now inside the stored procedure, I have the following:

DECLARE @FromDate DATE = '2022-06-01'
DECLARE @QueryResult Varchar(50);
DECLARE @SQLCommand Varchar(50);

SELECT @QueryResult = column1 
FROM TestTable

SET @SQLCommand = 'SELECT * FROM emp WHERE joindate >= '''   @QueryResult   ''';'

EXEC (@SQLCommand);

Now I am expecting that result should be all the employee whose joindate >= '2022-06-01'. Or in other words, I am expecting to use @FromDate variable to fetch data. But when i run query, I get the following error:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@FromDate"

When I run:

print @SQLCommand;

I get:

select * from emp where joindate >= '@FromDate';

While I am expecting that @FromDate value should be populated here at run time.

Will be thankful for any help regarding this.

Update: actually, there is a loop inside my sp, which fetches the data from table (data contains variable names to be used in the stored procedure in different logic I) like for a particular record: I need to add 20 days in @fromdate, and for another record I need to add 30 days. Now when my loop will run, it will fetch either dateadd(day, 20, @fromdate) or dateadd(day, 30, @fromdate) from table based on where clause and then I need to fill in the value of @fromdate (this is parametrise variable) and fetch the results accordingly.

Update 2:

Please see below my code

USE [GBI_archive]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Process_Data] 
    (@StartDate DATE = NULL,
     @EndDate DATE = NULL)
AS
    DECLARE @FromDate DATE = ISNULL(@StartDate, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)));
    DECLARE @ToDate DATE = ISNULL(@EndDate, GETDATE());

    DECLARE @CalculationMethodFromDate VARCHAR(255);

    DECLARE @SelectStatement VARCHAR(255);
    DECLARE @TableIntoStatement VARCHAR(255);
    DECLARE @FromStatement VARCHAR(255);
    DECLARE @SQLCommand VARCHAR(255);

    DECLARE cursor_product CURSOR FOR
        SELECT calculation_method_from_date 
        FROM [dbo].[Calculation_Method_Configuration];

    -- Here output can be DATEADD(DAY, -6,  @FromDate) or DATEADD(DAY, -14,  @FromDate) or so on

    OPEN cursor_product;

    FETCH NEXT FROM cursor_product INTO @CalculationMethodFromDate
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @CalculationMethodFromDate

        SET @SelectStatement = 'SELECT CURRENT_TIMESTAMP, * ';
SET @TableIntoStatement = 'INTO [dbo].[Table_For_Function_Output]';
        SET @FromStatement = 'FROM [dbo].[EmployeeData] where joindate >= '''   @CalculationMethodFromDate   ''';'
--      SET @SQLCommand = concat (@SelectStatement , ' ', @TableIntoStatement , ' ',  @FromStatement);

        PRINT @SQLCommand;
        EXEC (@SQLCommand);
        
        FETCH NEXT FROM cursor_product INTO @CalculationMethodFromDate, 
    END;

    CLOSE cursor_product;
    DEALLOCATE cursor_product;
GO

Now for anyone iteration of loop, print @SQLCommand shows this (if @CalculationMethodFromDate = 'DATEADD(DAY, -6, @FromDate)') :

SELECT CURRENT_TIMESTAMP, * INTO [dbo].[Table_For_Function_Output] FROM [dbo].[EmployeeData] where joindate >= 'DATEADD(DAY, -6,  @FromDate)';

and exec command throws this error:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@FromDate"

But if I am passing @FromDate = '2022-06-07' as parameter to this sp, my expectations for print @SQLCommand shows is:

SELECT CURRENT_TIMESTAMP, * INTO [dbo].[Table_For_Function_Output] FROM [dbo].[EmployeeData] where joindate >= '2022-06-01';

In short: @FromDate variable coming from database at runtime, should be assigned a value from stored procedure.

CodePudding user response：

You don't need a cursor here, you just need to build one big UNION ALL statement. And you need to pass the @FromDate and @ToDate into the dynamic SQL.

CREATE OR ALTER PROCEDURE [dbo].[sp_Process_Data] 
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS

DECLARE @FromDate DATE = ISNULL(@StartDate, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)));
DECLARE @ToDate DATE = ISNULL(@EndDate, GETDATE());

DECLARE SQLCommand nvarchar(max) = (
    SELECT STRING_AGG(N'
SELECT CURRENT_TIMESTAMP, e.*
FROM dbo.EmployeeData e
where e.joindate >= '   CAST(cm.calculation_method_from_date AS nvarchar(max))
      , '
UNION ALL ')

    FROM dbo.Calculation_Method_Configuration cm
);

PRINT @SQLCommand;

EXEC sp_executesql
    @SQLCommand,
    N'@FromDate DATE, @ToDate DATE',
    @FromDate = @FromDate,
    @ToDate = @ToDate;

go

The design itself is questionable. You should really just have a column which tells you how many days to add, then you can just do

SELECT CURRENT_TIMESTAMP, e.*
FROM dbo.EmployeeData e
JOIN dbo.Calculation_Method_Configuration cm
  ON e.joindate >= DATEADD(day, -cm.days, @FromDate);

CodePudding user response：

well actually you could simply use sp_executesql for this.

Simplified sample:

-- demo table:
SELECT DATEADD(day, -7, GETDATE()) [Date] INTO [#demo] UNION ALL SELECT DATEADD(day, 1, GETDATE());

-- demo:
DECLARE @CalculationMethodFromDate NVARCHAR(MAX) = N'DATEADD(DAY, -6,  @FromDate)';
DECLARE @FromDate DATE = GETDATE();
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM [#demo] WHERE [Date] >= ' @CalculationMethodFromDate N';';

EXEC sp_executesql @SQL, N'@FromDate DATE', @FromDate=@FromDate;

--cleanup
drop table [#demo];