SQL Select * FROM table where date is dynamic-CodePudding

I'm trying to run a parametrized SELECT query on a table, where date is equal to my @date stored in a variable and for some reason it does not work:

DECLARE @date DATETIME;
set @date = '09/01/2022';

select * from dba.hello where datum = @date

This works the way that I want to, but when i try to parametrize the query as follows:

 declare @table_name nvarchar(255);
 set @table_name = 'dba.hello';

 DECLARE @date DATETIME;
 set @date = '09/01/2022';

 exec('select * from ' @table_name ' where datum = ' @date)

I'm getting this error: Parse error at line: 1, column: 58: Incorrect syntax near '2022'.

Does anyone know what could be the issue, please?

Thanks

CodePudding user response：

You need to, just like any statement, parametrise your dynamic statement. If you do that, you have no problems. I also split your schema and table names into 2 variables and safely inject them:

DECLARE @SchemaName sysname,
        @TableName sysname;

SET @SchemaName = N'dba';
SET @TableName = N'Hello';

DECLARE @date datetime = '20220109'; --Should this not be a date is it's only a date?

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