Using sp_executesql with input and output paramters?-CodePudding

Using SQL Server 2016. I am writing a stored procedure. I have the following code that is happening inside a WHILE loop

DELCARE @recordExists int;
SET @recordExistsQuery = 'SELECT @recordExists=COUNT(*) FROM @fullTableName WHERE validFrom <= CAST(@asOfDate as datetime)';
exec sp_executesql @recordExistsQuery, N'@recordExists INT OUT, @fullTableName varchar(60), @asOfdate datetime' @recordExists OUT

I get the error `Must declare the table variable @fullTableName however I have already declared and set this variable (prior to the while loop) and used it multiple times prior the while loop so I know it exists and is valid. It's defined like so -

DECLARE @fullTableName varchar(60);
SET @fullTableName = (SELECT CONCAT(@schema, '.', @TableName));

and I have it printed prior to the while loop and it looks fine, and I have it printer per loop and that works as well.

What is wrong with my dynamic sql here? I am trying to use the paramterization method instead of string building with quotes as I am dealing with a datetime and want to do that with better practices than a bunch of quotes. Is that possible? How can I rewrite

SET @recordExistsQuery = 'SELECT @recordExists=COUNT(*) FROM @fullTableName WHERE validFrom <= CAST(@asOfDate as datetime)';
exec sp_executesql @recordExistsQuery, N'@recordExists INT OUT, @fullTableName varchar(60), @asOfdate datetime' @recordExists OUT

so that it works as expected?

UPDATE: Hardcoding the tablename instead of passing it as a parameter worked. I am now getting the following error from the following code -

SET @recordExistsQuery = 'SELECT @recordExists=COUNT(*) FROM '   @fullTableName   ' WHERE validFrom<=CAST(@asOfDate as datetime)';
PRINT @recordExistsQuery
exec sp_executesql @recordExistsQuery, N'@asOfDate datetime, @recordExists INT OUT', @recordExistsOut`

The print statement shows

SELECT @recordExists=COUNT(*) FROM [MySchema].[MyTable] WHERE validFrom<=(@asOfDate as datetime)

The error I now get is

Msg 8162, Level 16, State 2, Line 0
The formal parameter "@asOfDate" was not declared as an OUTPUT paramter, but the actual paramter passed in requested output.

I have @asOfDate as a paramter of my stored proecedure defined as @asOfDate DATETIME=NULL and the first line of my sp sets a default value if none is passed in

IF @asOfdate IS NULL 
    SET @asOfDate = GETDATE();

Anyone know what is going wrong now?

UPDATE 2: Using this line instead

'SELECT @recordExists=COUNT(*) FROM '  @fullTableName  ' WHERE validFrom <= CAST(' @asOfDate' ' as datetime)'

Now gets me the error Msg 241, Level 16, State 1, Conversion failed when converting date and/or time from character string.

@asOfDate is defined as a paramter of my sp like @asOfDate datetime=NULL and initialized to getdate(). I am testing this with @asOfDate=null, so it is using getdate() to initialize the value.

CodePudding user response：

Try splitting the string like this


'SELECT COUNT(*) FROM'  @fullTableName  'WHERE validFrom <= CAST(' @asOfDate ' as datetime)'

since @fullTableName is going as a string and not a variable, it was not working. If you change to the above, the final string will have the table name you wanted

This update should fix the asofdate issue

Also, you need to remove @recordExists and use the response from execution as @recordExists

CodePudding user response：

Could not quite get it to work as it was, so I ended up doing the following - when I initialized the date I also made a formatted version of it -

paramters - 
@asOfDate DATETIME=NULL,
...
BEGIN

IF @asOfdate IS NULL
    SET @asOfDate = GETDATE()
DECLARE @asOfDateFormatted NVARCHAR(30);
SET @asOfDateFormatted = CONVERT(NVARCHAR(30), @asOfDate, 126);
...
Dynamic sql - 
SET @recordExistsQuery = 'SELECT COUNT(*) FROM '  @fullTableName  ' WHERE validFrom <= ''' asOfDateFormatted '''';
exec sp_executesql @recordExistsQuery, N'@recordExists INT OUT', @recordExists OUT

If someone knows a better way please let me know but this is how I got it to work for now.

CodePudding user response：

When using dynamic SQL, bear in mind the following:

Table names (and other objects such as columns) cannot be passed as parameters, they must be injected.
Only do this using QUOTENAME to ensure proper escaping
Object names should be stored in sysname (alias for nvarchar(128)) and queries in nvarchar(max)
Data should always be passed as parameters, not injected

DECLARE @asOfDate DATETIME='20210923',

IF @asOfdate IS NULL
    SET @asOfDate = GETDATE();

DECLARE @recordExistsQuery nvarchar(max) = N'
SELECT @recordExists = COUNT(*)
FROM '   QUOTENAME(@schema)   '.'   QUOTENAME(@TableName)   '
WHERE validFrom <= @asOfdate;
';

PRINT @recordExistsQuery; -- for testing

EXEC sp_executesql
  @recordExistsQuery,
  N'@recordExists INT OUT, @asOfdate DATETIME',
  @recordExists = @recordExists OUT,
  @asOfdate = @asOfdate;