Executing Dynamic SQL

Question

Post reply

Executing Dynamic SQL

ksatpute123

Hall of Fame

Points: 3325
More actions
September 25, 2013 at 8:18 pm

#298291

Comments posted to this topic are about the item Executing Dynamic SQL
September 26, 2013 at 1:10 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Mr. Kapsicum

SSCertifiable

Points: 6128

September 25, 2013 at 10:13 pm

#1653401

nice question.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 2

Thanks, nice one-ish

Hope this helps...

Ford Fairlane
Rock and Roll Detective

sqlnaive SSCoach Points: 17437 More actions · Answer 3

sqlnaive

SSCoach

Points: 17437

September 26, 2013 at 12:25 am

#1653417

Good question and equally good explanation.

Carlo Romagnano SSC-Insane Points: 22745 More actions · Answer 4

Be aware of case:

DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE SP_EXECUTESQL @STR

It should be:

DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE sp_executesql @STR

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 5

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Stuart Davies SSCoach Points: 19505 More actions · Answer 6

Nice and easy today - thanks

Knew the answer to this through bitter experience

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

etsav Old Hand Points: 321 More actions · Answer 7

It should be underlined that this

[font="Courier New"]DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

EXECUTE SP_EXECUTESQL @STR[/font]

works on MSSQL 2008 -->

On MSSQL 2005 it returns

[font="Courier New"]Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.[/font]

Equivalent for 2005 is:

[font="Courier New"]DECLARE @STR VARCHAR(8000)

SET @STR = N'SELECT * FROM sys.objects'

EXECUTE SP_EXECUTESQL @STR[/font]

Toreador SSChampion Points: 11375 More actions · Answer 8

ksatpute123 (9/25/2013)
Comments posted to this topic are about the item <A HREF="/questions/Dynamic+SQL/102252/">Executing Dynamic SQL</A>

Nice idea, but not sure about the execution. I knew it was going to fail due to the data type, but any of the options was feasible so it came down to a guess as to which one the MS developers decided to return. Luckily I guessed right

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 9

Stuart Davies
Nice and easy today - thanks
Knew the answer to this through bitter experience Smile

+1.

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 10

etsav (9/26/2013)
It should be underlined that this
[font="Courier New"]DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'
EXECUTE SP_EXECUTESQL @STR[/font]
works on MSSQL 2008 -->
On MSSQL 2005 it returns
[font="Courier New"]Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.[/font]
Equivalent for 2005 is:
[font="Courier New"]DECLARE @STR VARCHAR(8000)
SET @STR = N'SELECT * FROM sys.objects'
EXECUTE SP_EXECUTESQL @STR[/font]

Thanks for adding this. Lot of guys I know have directly started working from 2008. This is helpful for them.

psingla Hall of Fame Points: 3840 More actions · Answer 11

Nice one..

Used it frequently

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 12

Carlo Romagnano (9/26/2013)
Be aware of case:
DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'
EXECUTE SP_EXECUTESQL @STR
It should be:
DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'
EXECUTE sp_executesql @STR

Only if the instance is case sensitive

In that case the correct answer would be

Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'SP_EXECUTESQL'.

PRAMANA.DBA SSCertifiable Points: 5507 More actions · Answer 13

PRAMANA.DBA

SSCertifiable

Points: 5507

September 26, 2013 at 3:40 am

#1653472

nice question.

Executing Dynamic SQL

Cookies on SQLServerCentral