SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rebecca 79612
rebecca 79612
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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?
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 8586
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
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2105 Visits: 872
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
rebecca 79612
rebecca 79612
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85376 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
C Hrushi
C Hrushi
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 49
Declare all variable used in dynamic query as nvarchar or varchar. It will work for sure.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85376 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
rebecca 79612
rebecca 79612
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39111 Visits: 38518
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;




Cool
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)
rebecca 79612
rebecca 79612
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 27
Lynn,

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search