Why dateadd() creating problem

Question

Why dateadd() creating problem

Buell

SSC Journeyman

Points: 79
More actions
August 11, 2003 at 9:29 am

#83038

declare @sec varchar(3), @timeStyle varchar(3)
set @sec = '60'
set @timeStyle = '108'
declare @query nvarchar(200)
set @query =
'select CONVERT (VARCHAR(10), DATEADD(S, '+@sec+', ''01-01-2000''), '+@timeStyle+') AS DURATION'
print @query
exec @query
TakeCare,
Bue!!

TakeCare,
Bue!!

Viewing 8 posts - 1 through 7 (of 7 total)

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

tran008 Ten Centuries Points: 1002 More actions · Answer 1

see http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=15101&FORUM_ID=8&CAT_ID=1&Topic_Title=SUM%28%40Month1%29&Forum_Title=T%2DSQL

mccork SSC-Insane Points: 22288 More actions · Answer 2

your last statement needs to be:

exec (@query)

Cheers,

- Mark

Cheers,
- Mark

timfoster Mr or Mrs. 500 Points: 536 More actions · Answer 3

Am I missing something or is the question that the query doesn't run because the exec is stated incorrectly? When I run the code, admittedly with my exec stated correctly, I get the result I would expect, 00:01:00

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Buell,

I think, although your question seems to be answered, if your statement needs to be run dynamically you might want to consider NOT to use EXEC @query, but rather EXEC sp_executesql @query.

Not only that Microsoft prefer this over EXECUTE, but you'll get a higher probability that execution plans can be reused.

However, results are the same!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Buell SSC Journeyman Points: 79 More actions · Answer 5

Mark's, ur answer did work.

Frank's, ur answer does work even without the (). And since its a recommended. I'm using it finally.

Honest 'Thax' for all support.

TakeCare,

Bue!!

TakeCare,
Bue!!

Buell SSC Journeyman Points: 79 More actions · Answer 6

But does, 'Insert into '+@addDate+'' work with my above EXEC sp_executesql @query.

I defined the @addDate as

DECLARE @addDate table (dated varchar(12))

TakeCare,

Bue!!

TakeCare,
Bue!!

jpipes SSChampion Points: 12230 More actions · Answer 7

quote:
But does, 'Insert into '+@addDate+'' work with my above EXEC sp_executesql @query.
I defined the @addDate as
DECLARE @addDate table (dated varchar(12))

No, sp_executesql will be outside the scope of the current procedures scope, so the table variable won't exist inside the sp_executesql scope...same with temp tables. You can circumvent this problem by using a global scope temp table (i.e. ##MyTable), and then you can access it in both the outer procedure scope and the inner (sp_executesql) scope.