Dynamic SQL and DATEADD with User Defined Variable in Number/2nd position

Question

Dynamic SQL and DATEADD with User Defined Variable in Number/2nd position

rebecca 79612

Old Hand

Points: 342
More actions
August 29, 2013 at 3:28 pm

#282568

This syntax
SET @sql = 'SELECT * FROM <sometable> WHERE dateadd(hh, ''' + @tzoffset + '''), END_DATETIME) BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''''
returns this error
Conversion failed when converting the varchar value 'SELECT * FROM <sometable> WHERE dateadd(hh, '' to data type int.
How do I get around this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Hard to tell, but I'd bet one of your @variables is declared as int rather than varchar so the '+' becomes an addition operator rather than a concatenation operator. When concatenating, everything needs to be char or varchar.

And then again, I might be wrong ...
David Webb

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

It's as simple as this:

SET @sql = 'SELECT * FROM <sometable>

WHERE dateadd(hh, @tzoffset, END_DATETIME) BETWEEN @StartDate AND @EndDate'

When you work with dynamic SQL you should use parameterised statements for many reasons, and one is exactly that it is simpler.

The way you run the query is this:

EXEC sp_executesql @sql, N'@tzoffset smallint, @StartDate datetime, @EndDate datetime',

@tzoffset, @StartDate, @EndDate

You can read more about sp_executesql in my article on dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

By the way, I think a better solution is

SET @sql = 'SELECT * FROM <sometable>

WHERE END_DATETIME BETWEEN dateadd(hh, -@tzOffset, @StartDate) AND dateadd(hh, -@tzOffset, @EndDate'

While longer, the first solution runs the risk that the optimzer will not use any index on END_DATETIME.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

rebecca 79612 Old Hand Points: 342 More actions · Answer 3

David, Thank you. I will keep that in mind.

Erland, I think this is exactly the information I needed! I will post back tomorrow when I know for sure it works.

Rebecca

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

rebecca 79612 (8/29/2013)
This syntax
SET @sql = 'SELECT * FROM <sometable> WHERE dateadd(hh, ''' + @tzoffset + '''), END_DATETIME) BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''''
returns this error
Conversion failed when converting the varchar value 'SELECT * FROM <sometable> WHERE dateadd(hh, '' to data type int.
How do I get around this?

Why do you need this to be dynamic SQL? You're not dynamically changing anything that requires dynamic SQL unless you're changing <sometable> to something else.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

C Hrushi SSC-Addicted Points: 469 More actions · Answer 5

Declare all variable used in dynamic query as nvarchar or varchar. It will work for sure.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

churi.hrushikesh (8/30/2013)
Declare all variable used in dynamic query as nvarchar or varchar. It will work for sure.

Better than that, don't use dynamic SQL because it doesn't look like it's even needed here. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rebecca 79612 Old Hand Points: 342 More actions · Answer 7

Erland,

Thank you very much. This solved my problem and works on the ACTUAL query I am using and not just the simple version posted on the forum.

Also, I cannot tell you how many times I have visited (and printed) the article 'The curse and blessings of dynamic SQL' over the last couple of years! Thank you for your effort in writing the article. It has served me well. Obviously I need to visit it a little more. 😉

Rebecca

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

You may find that the following will work better as the execution plans will more likely be cached, plus it will use an index on END_DATETIME if it exists and will help the query. Your code will require a table or clustered index scan since the DATEADD function will have to applied to every row in the table to determine if the criteria matches or not.

DECLARE @SQLCmd NVARCHAR(MAX),

@SQLParam NVARCHAR(MAX),

@StartDate1 DATETIME,

@EndFate1 DATETIME,

@tzoffset1 INT;

SET @SQLCmd = N'

SELECT *

FROM <sometable>

WHERE

END_DATETIME between DATEADD(hh, -1 * @tzoffset , @StartDate) and DATEADD(hh, -1 * @tzoffset , @EndDate)

';

SET @SQLParam = N'@StartDate DATETIME, @EndDate DATETIME, @tzoffset INT';

EXEC sp_executesql @SQLCmd, @SQLParam, @StartDate = @StartDate1, @EndDate = @EndFate1, @tzoffset = @tzoffset1;

rebecca 79612 Old Hand Points: 342 More actions · Answer 9

Lynn,

:w00t: Ah, yes I see what you mean. Thank you for further insight on this solution!

ScottPletcher SSC Guru Points: 101122 More actions · Answer 10

You don't want to use a function against the END_DATETIME column if you can avoid it, since any function will prevent SQL from using the full seek capabilities against that column in any index(es) in which it might appear.

I think the code below will do SELECT the same rows as your original query, but adjust the code if/as needed to match you need:

SELECT *

FROM dbo.tablename

WHERE

END_DATETIME >= DATEADD(HOUR, -@tzoffset, @StartDate) AND

END_DATETIME <= DATEADD(HOUR, -@tzoffset, @EndDate)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

ScottPletcher (9/3/2013)
You don't want to use a function against the END_DATETIME column if you can avoid it, since any function will prevent SQL from using the full seek capabilities against that column in any index(es) in which it might appear.
I think the code below will do SELECT the same rows as your original query, but adjust the code if/as needed to match you need:
SELECT *
FROM dbo.tablename
WHERE
END_DATETIME >= DATEADD(HOUR, -@tzoffset, @StartDate) AND
END_DATETIME <= DATEADD(HOUR, -@tzoffset, @EndDate)

Same thing I suggested in my post.