dynamic sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dynamic sql

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-06-17 : 08:00:24

Can you check where the syntax error is in this code?


declare @SQL varchar(max), @organisation varchar(100)
set @organisation = '''ATTE ''','''VANT'''
select @SQL = 'select * from tblorg '
select @SQL = @SQL + 'where organisation in (' + @organisation + ')'
Exec (@SQL)

select @SQL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-17 : 08:11:38

declare @SQL varchar(max), @organisation varchar(100)
set @organisation = '''ATTE '',''VANT'''
select @SQL = 'select * from tblorg '
select @SQL = @SQL + 'where organisation in (' + @organisation + ')'
Exec (@SQL)

select @SQL

Too old to Rock'n'Roll too young to die.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-06-17 : 09:16:48

I'm getting error incorrect syntax near ','. I'm using SQL Server 2005

And the last question how I can get the real output instead of just printing the syntax.

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-06-17 : 17:15:57

I got it. I used '''ATE''' + ',' + '''VANT'''

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-17 : 17:19:21

quote:
Originally posted by learning_grsql

how I can get the real output instead of just printing the syntax.

What do you mean?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2014-06-18 : 08:05:04

@tkizer. That also I sorted out.
When I noted "select (@SQL)" just prints the syntax of the query, I wanted to know how I can get the results of the query. Later I understood I have to use "Exec (@SQL)" to get the result.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-18 : 12:33:36

Use sp_executesql instead of exec for dynamic sql.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources