SQL SERVER Dynamic SQL Help - 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

Site Related Forums

Article Discussion

SQL SERVER Dynamic SQL Help

Author

Topic

yaaadman
Starting Member

4 Posts

Posted - 2007-06-27 : 10:09:18

I can't get the below dynamic sql to work. I am getting this error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Any Help?

DECLARE @Sql nvarchar(2000)

SET @Sql = 'SELECT stag.id,
REPLACE(stag.broker,stag.broker,''Staging'' + stag.broker ) '
SET @Sql = @Sql + 'us.cocoid, ua.UserAccountID '
SET @Sql = @Sql + '(CASE WHEN UPPER(BuySell) = ''SELL'' THEN 0 ELSE 1 END) '
SET @Sql = @Sql + 'stag.tradeDate, CONVERT(INT, REPLACE(stag.quantity,''.000'','''')) '
SET @Sql = @Sql + 'stag.symbol '
SET @Sql = @Sql + 'FROM dbo.StagingSchwab stag, Users us, UserAccounts ua '
SET @Sql = @Sql + 'WHERE stag.cusip <> '''' AND us.cocoid = ua.cocoid '
SET @Sql = @Sql + 'AND REPLACE(ua.AccountNumber,''-'','''') = stag.AccountNumber'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-27 : 10:17:25

You are missing commas at the end of each concatenation, Dude!

SET @Sql = 'SELECT stag.id,
REPLACE(stag.broker,stag.broker,''Staging'' + stag.broker ), '
SET @Sql = @Sql + 'us.cocoid, ua.UserAccountID, '
SET @Sql = @Sql + '(CASE WHEN UPPER(BuySell) = ''SELL'' THEN 0 ELSE 1 END), '
SET @Sql = @Sql + 'stag.tradeDate, CONVERT(INT, REPLACE(stag.quantity,''.000'','''')), '
SET @Sql = @Sql + 'stag.symbol '
SET @Sql = @Sql + 'FROM dbo.StagingSchwab stag, Users us, UserAccounts ua '
SET @Sql = @Sql + 'WHERE stag.cusip <> '''' AND us.cocoid = ua.cocoid '
SET @Sql = @Sql + 'AND REPLACE(ua.AccountNumber,''-'','''') = stag.AccountNumber'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 10:25:46

Must read article
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

yaaadman
Starting Member

4 Posts

Posted - 2007-06-27 : 11:27:16

Thanks a bunch for all your help... It was really the missing commas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources