CODEWITHSUNDEEP
sqlsql-server
Jon Sud
Jon Sud

Reputation: 11661

Why datetime is not apply to the parameter in SQL Server?

I have a application runs this T-SQL command:

exec sp_executesql @statement = N'mysp @timestamp=0',
                   @params = N'@0 datetime',
                   @0 = '2021-04-08 20:59:10.987'

I log the timestamp value and I get empty for some reason:

1900-01-01 00:00:00.000

This is the stored procedure code:

ALTER PROCEDURE [dbo].[mysp]
    @TimeStamp DATETIME = NULL
AS
    SET NOCOUNT ON

    DECLARE 
        ....
    BEGIN TRY
        SELECT @TimeStamp
        ...

What is wrong in the T-SQL command? How should it be in this structure?

Upvotes: 0

Views: 59

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416083

What I see is procedure named mysp which expects one parameter named @timestamp. The =0 part sets the value of this parameter to 0. It is not the name of another paramter. When interpreted as a datetime, a 0 value matches the 1900-01-01 you observed.

I expect this is what the application is trying to do:

exec sp_executesql @statement = N'mysp @timestamp',
                   @params = N'@timestamp datetime',
                   @timestamp = '2021-04-08 20:59:10.987'

Alternatively:

exec sp_executesql @statement = N'mysp @timestamp=@0',
                   @params = N'@0 datetime',
                   @0 = '2021-04-08 20:59:10.987'

In communicating with developers about how to fix this on their end, keep in mind they are probably not writing the exec sp_executesql code directly. Rather, they are more likely using a tool to just call mysp that alters the code to use sp_executesql behind the scenes for them. This is common practice as a safe way for developers to use parameterized queries in their code and avoid Sql Injection security issues.

Upvotes: 2

Related Questions