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'.
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.
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.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.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".
DECLARE @periodEnd datetime = '2021-12-09 02:41:42.000' EXEC('SELECT DATEADD(MONTH, -3,''' + @periodEnd + ''') , ''' + @periodEnd + '''') and that worked fine
PRINT/SELECTthe 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.