Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic SQL and DATEADD with User Defined Variable in Number/2nd position Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 3:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 3:02 PM
Points: 4, Visits: 24
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?
Post #1489929
Posted Thursday, August 29, 2013 3:45 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:37 PM
Points: 867, Visits: 7,574
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
Post #1489938
Posted Thursday, August 29, 2013 3:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1489943
Posted Thursday, August 29, 2013 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 3:02 PM
Points: 4, Visits: 24
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
Post #1489952
Posted Thursday, August 29, 2013 10:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490004
Posted Friday, August 30, 2013 6:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 20, 2013 12:59 AM
Points: 59, Visits: 49
Declare all variable used in dynamic query as nvarchar or varchar. It will work for sure.
Post #1490117
Posted Friday, August 30, 2013 7:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1490140
Posted Friday, August 30, 2013 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 3:02 PM
Points: 4, Visits: 24
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
Post #1490190
Posted Friday, August 30, 2013 9:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 20,860, Visits: 32,886
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;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1490203
Posted Friday, August 30, 2013 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 3:02 PM
Points: 4, Visits: 24
Lynn,

Ah, yes I see what you mean. Thank you for further insight on this solution!
Post #1490223
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse