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

executing dynamic sql string Expand / Collapse
Author
Message
Posted Thursday, December 05, 2013 7:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:25 AM
Points: 116, Visits: 402
hi (me again)

i'm trying to get a proc to execute by passing in a dynamic sql string.

this works...

exec [MediaCreateCustomerDataSet] @accountId, @yr, @stockGroup, @stockCode, @stockDescription

but this doesn't (error: The name '[MediaCreateCustomerDataSet] @accountId, @yr, @stockGroup, @stockCode, @stockDescription' is not a valid identifier.)...

set @sqlStr = @dataSet + ' @accountId, @yr, @stockGroup, @stockCode, @stockDescription'

exec @sqlStr

i also tried (error: Incorrect syntax near ',')...

set @sqlStr = @dataSet + ' ' + @accountId, @yr, @stockGroup, @stockCode, @stockDescription

exec @sqlStr

thanks
Post #1520095
Posted Thursday, December 05, 2013 2:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 8:17 AM
Points: 243, Visits: 785
Why the square brackets around [MediaCreateCustomerDataSet] ?

They aren't generally needed unless you're using a SQL Server reserved word or have spaces in the name. Have you tried your dynamic query without them?


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1520330
Posted Thursday, December 05, 2013 2:28 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 532, Visits: 787
Can you post the entire statement that is causing the error?



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520333
Posted Thursday, December 05, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
This way be an object name, and optionally parameters after object name
exec @sqlStr  

THIS way, you can execute a string command: note the parenthesis!
exec(@sqlStr ) 



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520338
Posted Friday, December 06, 2013 6:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 8:17 AM
Points: 243, Visits: 785
Lowell,

Good catch! Something bothered me about that EXEC statement but I didn't stop to think about it. An idle question for anybody: are those brackets a problem or not?


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1520563
Posted Friday, December 06, 2013 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 AM
Points: 6,795, Visits: 6,267
Sigerson (12/6/2013)
Lowell,are those brackets a problem or not?


Nope, no problem.
If you script anything or use the shortcut menu to execute a procedure in SSMS it puts brackets around everything.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1520567
Posted Friday, December 06, 2013 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:25 AM
Points: 116, Visits: 402
the (@sqlStr) seems to have worked, thanks
Post #1520582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse