SQL Server stored procedure parameters syntax error-CodePudding

I have the following query:

DECLARE @periodEnd datetime = '2021-12-09 02:41:42.000'
DECLARE @ID VARCHAR(50) = '35915D4B-E210-48C0-ADD5-C68AAEB62C36'

EXEC('SELECT COUNT(*) AS count FROM pageloads nolock WHERE domainId = '''   @ID  ''' AND paid = 1 AND source IN (2) AND clickedOn BETWEEN DATEADD(MONTH, -3,'   @periodEnd   ') AND '   @periodEnd)

but I get an error:

Incorrect syntax near '9'.

CodePudding user response：

There is no need for dynamic SQL here. The actual code you have won't generate that error either; it would likely generate this error instead:

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string

This is because you add (as in addition) the value 'select...MONTH, -3,' to the datetime value 2021-12-09T02:41:42.000; obviously the former is not a valid datetime and the conversion fails.

Use a parametrised non-dynamic statement, and you'll get no errors.

DECLARE @periodEnd datetime = '2021-12-09T02:41:42.000'; -- Use an unambiguous datetime format
DECLARE @ID uniqueidentifier = '35915D4B-E210-48C0-ADD5-C68AAEB62C36'; --This is clearly a GUID, so use the right data type.
SELECT COUNT(*) AS count
FROM dbo.pageloads pl --nolock is an odd alias. I've gone for a better one.
WHERE domainId = @ID
  AND paid = 1
  AND source IN (2) --Why IN when you only supply one value?
  AND clickedOn BETWEEN DATEADD(MONTH, -3, @periodEnd) AND @periodEnd;

Also note that the BETWEEN may not be doing what you expect. For the above, this would resolve to effectively the following:

  AND clickedOn >= '2021-09-09T02:41:42.000'
  AND clickedOn <= '2021-12-09T02:41:42.000'

Most times I see people use such logic with BETWEEN that want exclusive of time, and >= and < logic; though I am not going to guess that is what you want here.

CodePudding user response：

You are simply missing a few single quotes around your date variables. Since you are building your query in dynamic SQL, your dates are passed into the dynamic query as date literals, so therefore they need single quotes around them.

To represent a single quote in your dynamic SQL, you actually need 3 single quotes, as you already have around your @ID variable. At the end, you need 4 single quotes to both add the single quote into the dynamic and also to terminate the EXEC string.

So it would look like this:

DECLARE @periodEnd datetime = '2021-12-09 02:41:42.000'
DECLARE @ID VARCHAR(50) = '35915D4B-E210-48C0-ADD5-C68AAEB62C36'

EXEC('SELECT COUNT(*) AS count FROM pageloads nolock WHERE domainId = '''   @ID  ''' AND paid = 1 AND source IN (2) AND clickedOn BETWEEN DATEADD(MONTH, -3,'''   @periodEnd   ''') AND '''   @periodEnd   '''')

Any time I pass variables into Dynamic SQL I always end up with code littered with single quotes like this.