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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Sql

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2010-02-22 : 20:52:33

How can we include the Quote " in dynamic sql

SET @Sql_Statement = 'SELECT
(' + '"' + 'c.code' + '"' + ') as custnum
(' + '"' + 'mi.prospect' + '"' + ') as prospect
FROM Orders o

EXEC @Sql_Statement

How can I change the above?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 02:08:24

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

konark
Yak Posting Veteran

60 Posts

Posted - 2010-02-23 : 02:55:27

declare @Sql_Statement varchar(100)
SET @Sql_Statement = 'SELECT o.code,o.prospect from orders o'
EXEC ( @Sql_Statement)

Try this..works 100%

Chandragupta Mourya

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 03:07:14

I don't think quotes is your problem.
Your SET statement (which is missing one trailing ' ) generates this:


SELECT
("c.code") as custnum
("mi.prospect") as prospect
FROM Orders o

which is not valid in a number of ways.

I think you are trying to reference c.code - but there is no table aliased as "c", similarly with mi.prospect there is no table alias "mi"

If you need to surround the column names I recommend you use [...], rather than " quote, and you should not include the alias name inside the quotes/brackets:


SELECT
    c.[code] as custnum
    mi.[prospect] as prospect
FROM Orders o
... Join other tables for C and MI aliases ...

You also need parenthesis around your EXEC statement, as konark said:


EXEC ( @Sql_Statement )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources