Dynamic SQL stored procedure and datetime

I have a simple query that I want to convert to dynamic SQL. I have 2 input parameters: a table and a datetime. And the output is the rowcount for the table and this specific datetime.

CREATE PROCEDURE [etl].[ROWCOUNT_TST2]
    (@P_LOAD_TARGET nvarchar(250),
     @P_LOAD_DATE DATETIME)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)

    SET @SQL = 'SELECT COUNT(*) as Inserted FROM'+@P_LOAD_TARGET +' WHERE VALID_FROM ='''+  @P_LOAD_DATE+''' AND VALID_TO IS NULL'

    EXEC  (@SQL)
END;
GO

I tried different solutions. I tried the query with execute sp_executesql, I tied to add the the ”’ before and after the @P_LOAD_DATE. I am probably missing something here.

When I execute the stored procedure with a table name and datetime like 2021-05-06 06:41:52.557, I get the following error:

Conversion failed when converting date and/or time from character string.

But why?

I even tried to add a conversion to datetime like this, but I still get the same error.

 SET @SQL = 'SELECT COUNT(*) as Inserted FROM'+@P_LOAD_TARGET +' WHERE VALID_FROM = convert(datetime,'''+  @P_LOAD_DATE+''') AND VALID_TO IS NULL'

But when I execute SELECT convert(datetime, ‘2021-05-06 06:41:52.557’) it works out fine. I am just confused right now and can’t find the root of the problem.

Edit: valid_from is a datetime in the target table. So that is also not the reason for the problem

Answer

You need to properly and safely inject your dynamic object name and parametrise your parameter:

CREATE PROCEDURE [etl].[ROWCOUNT_TST2](@P_LOAD_SCHEMA sysname = N'dbo', --Always define your schema
                                       @P_LOAD_TARGET sysname, --sysname is the data type for objects, a synonym of nvarchar(128) NOT NULL
                                       @P_LOAD_DATE datetime) AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = N'SELECT COUNT(*) AS Inserted' + @CRLF +
               N'FROM ' + QUOTENAME(@P_LOAD_SCHEMA) + N'.' + QUOTENAME(@P_LOAD_TARGET) + @CRLF +
               N'WHERE VALID_FROM = @P_LOAD_DATE' + @CRLF +
               N'  AND VALID_TO IS NULL;';

    --PRINT @SQL; --Your best friend.
    EXEC sys.sp_executesql @SQL, N'@P_LOAD_DATE datetime', @P_LOAD_DATE;

END;