dynamic sql statement insertion issue

Question

I have created a Temp table(#TempTable). I am trying to insert the date to get but I am getting an error. It's a dynamic query.

I am trying to get the date from another table and inserting the date to temp table

Just to make sure you understand the problem I have given an example

DECLARE @OfferEndDateTime datetime
SELECT @OfferEndDateTime = getdate()-1
print @VOfferEndDateTime


DECLARE @SQL VarChar(1000)
SELECT @SQL ='INSERT INTO #TempTable '+
'SELECT D,Points,@OfferEndDateTime '
exec(@sql)

Please Let me know where I am going wrong

may be @OfferEndDateTime should be outside single quote.means u have to concat it to ur query string — reshma k
– reshma k, Commented Jan 4, 2012 at 11:05

StuartLC · Accepted Answer · 2012-01-04 11:05:02Z

3

You need to use sp_executesql when passing a parameter to dynamic sql

exec sp_executesql @sql, N'@OfferEndDateTime datetime', @OfferEndDateTime=@OfferEndDateTime

answered Jan 4, 2012 at 11:05

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aF. · Accepted Answer · 2012-01-04 11:43:57Z

1

You have, at least, three problems:

The variable needs to be outside:

SELECT @SQL ='INSERT INTO #TempTable '+
'SELECT D,Points,' + @OfferEndDateTime

The variable needs to be varchar type or similar
What is D,Points? They are not defined anywhere. If they are varchar values you nead to quote them (use " or '') for that purpose.

If you need to use the parameter like datetime you should use sp_executesql instead. Check HERE for some info on it!

edited Jan 4, 2012 at 11:43

answered Jan 4, 2012 at 11:05

aF.

67k45 gold badges141 silver badges201 bronze badges

Collectives™ on Stack Overflow

dynamic sql statement insertion issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related