Author |
Topic |
gongxia649
So Suave
344 Posts |
Posted - 2006-08-23 : 08:52:19
|
-- drop proc SP_Trio_Popul_Stg_Tbls1CREATE PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS-- alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) ASdeclare @sql varchar (20) set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last) SELECT nm_name FROM dbo.' + quotename(@tableName)print @sqlexec (@sql)----------------------------------------------------------------------exec SP_Trio_Popul_Stg_Tbls1 '00485'----------------------------------------------------------------------INSERT INTO dbo.NameServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'Name'.im trying to run this SP but im getting an error. Can you help me to fix it?-------------------------gongxia649------------------------- |
|
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-08-23 : 09:16:02
|
[code]alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) ASdeclare @sql varchar (200)set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last)SELECT nm_name FROM dbo.' + quotename(@tableName)print @sqlexec (@sql)[/code]RegardsNThe revolution won't be televised! |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 10:26:26
|
I just want to make sure you see where your error is. This occured becuase you declared only a varchar(20) in your original SQL and tried to populate it with 100+ characters. The first 20 chars of the set command are:INSERT INTO dbo.NameThat is why you get the error. Use Norwich's code and see if you get a little further.-ec |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-23 : 10:48:15
|
i think i'm stupid, i have made the same mistake like 4 times already. Looking at the monitor for a long period of time makes you dumb.thank you guys for the inputs-------------------------gongxia649------------------------- |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 11:09:37
|
quote: Originally posted by gongxia649 i think i'm stupid, i have made the same mistake like 4 times already. Looking at the monitor for a long period of time makes you dumb.
ok then. So be prepared for an ass-kicking the next time you post a problem with this same error -ec |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-23 : 14:33:01
|
create proc [dbo].[usp_Trio_updateDCDE] @tableName varchar (500) as-- alter proc [dbo].[usp_Trio_updateDCDE] @tableName varchar (200) as declare @sql varchar (500)set @sql = 'update hava_DCDE.dbo.' + quotename(@tableName) + 'set ad_num = a.ad_num, ad_str1 = a.ad_str1, ad_num_suffix_a = a.ad_num_suffix_a, ad_num_suffix_b = a.ad_num_suffix_b, ad_unit = a.ad_unit from hava_dev.dbo.TempAddressParsingTable a inner join hava_DCDE.dbo.' + quotename(@tablebame) +' dcde on dcde.id_town = a.id_town and dcde.id_voter = a.id_voter'print @sqlexec (@sql)-- exec usp_Trio_updateDCDE '00519'Server: Msg 137, Level 15, State 2, Procedure usp_Trio_updateDCDE, Line 13Must declare the variable '@tablebame'.can you help me on this one?-------------------------gongxia649------------------------- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 14:51:34
|
Are you even looking at the error? Your variable isn't spelled correctly. @tablebameTara Kizer |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-23 : 15:03:28
|
quote: Originally posted by tkizer Are you even looking at the error? Your variable isn't spelled correctly. @tablebameTara Kizer
let the ass-kicking commence..-ec |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-23 : 15:14:57
|
declare @tableName varchar(500)declare @Sql varchar (500)set @tableName = '00513'set @Sql = 'update hava_DCDE.dbo. + quotename(@tableName ) set ad_num = a.ad_num, ad_str1 = a.ad_str1, ad_num_suffix_a = a.ad_num_suffix_a, ad_num_suffix_b = a.ad_num_suffix_b, ad_unit = a.ad_unit from hava_dev.dbo.TempAddressParsingTable a inner join hava_DCDE.dbo. + quotename(@tablename) dcde on dcde.id_town = a.id_town and dcde.id_voter'print (@sql)print (@tablename)--------------------------------------------------------------------------------result:update hava_DCDE.dbo. + quotename(@tableName ) set ad_num = a.ad_num, ad_str1 = a.ad_str1, ad_num_suffix_a = a.ad_num_suffix_a, ad_num_suffix_b = a.ad_num_suffix_b, ad_unit = a.ad_unit from hava_dev.dbo.TempAddressParsingTable a inner join hava_DCDE.dbo. + quotename(@tablename) dcde on dcde.id_town = a.id_town and dcde.id_voter00513----------------------------------------------------------------------------------------why @tableName is not turning into 00513-------------------------gongxia649------------------------- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 15:17:40
|
Because it's part of the string. I don't understand your use of quotename. Try this:declare @tableName varchar(500)declare @Sql varchar (500)set @tableName = '00513'set @Sql = 'update hava_DCDE.dbo.' + @tableName + 'set ad_num = a.ad_num,ad_str1 = a.ad_str1,ad_num_suffix_a = a.ad_num_suffix_a,ad_num_suffix_b = a.ad_num_suffix_b,ad_unit = a.ad_unitfrom hava_dev.dbo.TempAddressParsingTable ainner join hava_DCDE.dbo.' + @tablename + ' dcde on dcde.id_town = a.id_townand dcde.id_voter'@tablename can not be part of the string, otherwise SQL Server has no idea that there is a variable in the string to be replaced with your value.Tara Kizer |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-23 : 15:20:21
|
u know what just kick my ass...i made another mistake.-------------------------gongxia649------------------------- |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-23 : 15:43:28
|
quote: Originally posted by gongxia649 u know what just kick my ass...
I may have to take you up on that offer. Tara Kizer |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-23 : 18:11:42
|
quote: Originally posted by tkizer I don't understand your use of quotename.
For some reason, the SQL parser didn't like dynamically built strings where table names begin with digits, such as in his first example. TableName were 00519, and he got an error until putting double quotes around 00519. Then I suggested an ANSI approach to put brackets around, just as QUOTENAME does.Peter LarssonHelsingborg, Sweden |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2006-08-23 : 21:05:38
|
This thread is awesome.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-08-23 : 22:04:01
|
gongxia649 -- you could consider a better database design that won't require dynamic sql for basic SQL statements.- Jeff |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-24 : 02:48:03
|
quote: Originally posted by Merkin This thread is awesome.
The original poster is interesting.Peter LarssonHelsingborg, Sweden |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-08-24 : 06:48:17
|
at least his SP names have improved.1st - SP_Trio_Popul_Stg_Tbls1 ** naughty!!!! don't start it with SP_2nd - usp_Trio_updateDCDE ** at least this one won't go to the master database and look first!For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-25 : 11:42:23
|
quote: Originally posted by jsmith8858 gongxia649 -- you could consider a better database design that won't require dynamic sql for basic SQL statements.- Jeff
how? can you explain?gongxia649 |
|
|
gongxia649
So Suave
344 Posts |
Posted - 2006-08-25 : 11:42:58
|
quote: Originally posted by gongxia649
quote: Originally posted by jsmith8858 gongxia649 -- you could consider a better database design that won't require dynamic sql for basic SQL statements.- Jeff
how? can you explain? how do you know my db is not good designed?gongxia649
gongxia649 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-25 : 12:04:30
|
quote: Originally posted by gongxia649how? can you explain?
why are you having to pass the name of a table to your procedure? can you think of a way that does not require this?-ec |
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-08-25 : 12:33:12
|
quote: how? can you explain?
Because you're using dynamic SQL when you probably don't need to?Ken |
|
|
Previous Page&nsp;
Next Page
|