SQL Server stored procedure parameters syntax error

Question

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'.

The easiest way to debug dynamic SQL is to `PRINT`/`SELECT` the statement first. Then you can debug that SQL first, and solve the problem before propagating the solution to your SQL that generates the dynamic statement. Often you'll find that the problems are quite simple, such as a typographical error that is difficult to stop in the literal strings, a missing whitespace/linebreak, or leading/trailing delimiters. Taking the time to get the non-dynamic statement working first is really important, as if that doesn't work the dynamic one will have no chance of working correctly. — Thom A, Jan 10 '22 at 10:16
You are trying to concatenate a *datetime* with a *string*, but this entire dynamic statement serves no purpose. — Stu, Jan 10 '22 at 10:22

Thom A · Answer 1 · 2022-01-10T10:30:24.867

2

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.

edited Jan 10 '22 at 10:30

answered Jan 10 '22 at 10:23

Thom A

88,727
11
45
75

1

what if i want to use the EXEC syntax? – billi j Jan 10 '22 at 10:28
3

*Why* do you want to use `EXEC` syntax @billij ? If your statement isn't dynamic, there is no requirement to do so. You, without offence, clearly are not familiar with SQL/T-SQL and so you should not be trying to overly complicate the problem. – Thom A Jan 10 '22 at 10:28
because its not the full statement. this is part of a stored procedure – billi j Jan 10 '22 at 10:34
Then you should be showing us what you are really trying to achieve, @billij . You'll need to now ask a [new question], as otherwise you'll significantly change the content of the question after receiving answer(s), which is severely frowned up by the community. [Don't be a Chameleon](https://meta.stackoverflow.com/q/332820) – Thom A Jan 10 '22 at 10:37
1

my question was with EXEC – billi j Jan 10 '22 at 10:50
1

And the `EXEC` wasn't needed, @billij . In fact, *because* you were using `EXEC` and then trying to therefore use additional between a `varchar` and `datetime` you were getting the errors. As I mentioned, if the above isn't representative of the problem you *really* have, you'll need to ask a [new question](https://stackoverflow.com/questions/ask). – Thom A Jan 10 '22 at 10:56

OwlFace · Answer 2 · 2022-01-10T16:27:20.493

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.

It looks like you are trying to use the WITH(NOLOCK) table hint to speed up the process and to prevent any performance impact while you're running this count, which is a good idea. You will get a lot of pushback on this website about using WITH(NOLOCK), but not from me. It's a very useful feature for this type of query, where you want a quick number and don't want to grind your logging process to a halt while you get it.

The correct syntax to use WITH(NOLOCK) would be:

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 pl WITH(NOLOCK) WHERE domainId = ''' + @ID+ ''' AND paid = 1 AND source IN (2) AND clickedOn BETWEEN DATEADD(MONTH, -3,''' + @periodEnd + ''') AND ''' + @periodEnd + '''')

Previously you were using "nolock" as a table alias, which is not a great idea. I changed the table alias to "pl".

No, because I don't have OPs pageloads table to run it against. I did test the logic by running: `DECLARE @periodEnd datetime = '2021-12-09 02:41:42.000' EXEC('SELECT DATEADD(MONTH, -3,''' + @periodEnd + ''') , ''' + @periodEnd + '''')` and that worked fine — OwlFace, Jan 10 '22 at 12:22

SQL Server stored procedure parameters syntax error

2 Answers2