can't create SP - 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

can't create SP

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-09-05 : 09:00:10

create proc usp_CatchAddressException @tableName varchar(500) as

set nocount on
begin
declare @sql varchar(500)

truncate table #tempException

set @sql = 'insert #tempexception (id_town, id_voter, ad_str1, nmb_exception )
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex(' ', reverse(a.ad_str1))), '20'
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex(' ', reverse(a.ad_str1))+1) not in (select nm_street from state_streets )
and a.id_town = '00519''

print (@sql)
exec(@sql)
end

Server: Msg 170, Level 15, State 1, Procedure usp_CatchAddressException, Line 10
Line 10: Incorrect syntax near ', reverse(a.ad_str1))), '.

i cant find where my mistake is. can someone give some inputs?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-05 : 09:05:18

You have to replace every single quote with two quotes when using dynamic sql as below:

set @sql = 'insert #tempexception (id_town, id_voter, ad_str1, nmb_exception )
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20''
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in (select nm_street from state_streets )
and a.id_town = ''00519'''

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 09:09:20

Learn to keep track of your '

set @sql = 'insert #tempexception (id_town, id_voter, ad_str1, nmb_exception )
select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20''
from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a
where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1) ) + 1) not in (select nm_street from state_streets )
and a.id_town = ''00519'''

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-05 : 09:55:34

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources