Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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 5, 2013 7:53 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, March 10, 2016 9:46 AM
Points: 124, Visits: 462
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 5, 2013 2:20 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: Monday, April 25, 2016 11:50 AM
Points: 537, Visits: 1,222
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 5, 2013 2:28 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, February 17, 2016 11:43 AM
Points: 604, Visits: 959
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 5, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:38 PM
Points: 14,209, Visits: 37,142
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1520338
Posted Friday, December 6, 2013 6:48 AM


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: Monday, April 25, 2016 11:50 AM
Points: 537, Visits: 1,222
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 6, 2013 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:50 AM
Points: 7,640, Visits: 8,709
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 6, 2013 7:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, March 10, 2016 9:46 AM
Points: 124, Visits: 462
the (@sqlStr) seems to have worked, thanks
Post #1520582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse