SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP is not reading the table name?		  New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

gongxia649
So Suave

Azores
344 Posts
Posted - 08/23/2006 :  08:52:19  Show Profile  Visit gongxia649's Homepage  Reply with Quote
-- drop proc SP_Trio_Popul_Stg_Tbls1
CREATE PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS
-- alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS


declare @sql varchar (20)

set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last)
SELECT nm_name FROM dbo.' + quotename(@tableName)

print @sql
exec (@sql)

----------------------------------------------------------------------
exec SP_Trio_Popul_Stg_Tbls1 '00485'
----------------------------------------------------------------------
INSERT INTO dbo.Name
Server: Msg 170, Level 15, State 1, Line 1
Line 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

Kenya
158 Posts
Posted - 08/23/2006 :  09:16:02  Show Profile  Reply with Quote 
alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS


declare @sql varchar (200)

set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last)
SELECT nm_name FROM dbo.' + quotename(@tableName)

print @sql
exec (@sql)


Regards
N

The revolution won't be televised!
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts
Posted - 08/23/2006 :  10:26:26  Show Profile  Reply with Quote
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.Name

That is why you get the error. Use Norwich's code and see if you get a little further.



-ec
Edited by - eyechart on 08/23/2006 10:26:45
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts
Posted - 08/23/2006 :  10:48:15  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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
-------------------------
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts
Posted - 08/23/2006 :  11:09:37  Show Profile  Reply with Quote
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts
Posted - 08/23/2006 :  14:33:01  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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 @sql

exec (@sql)



-- exec usp_Trio_updateDCDE '00519'


Server: Msg 137, Level 15, State 2, Procedure usp_Trio_updateDCDE, Line 13
Must declare the variable '@tablebame'.


can you help me on this one?

-------------------------
gongxia649
-------------------------
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 08/23/2006 :  14:51:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you even looking at the error? Your variable isn't spelled correctly. @tablebame

Tara Kizer
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts
Posted - 08/23/2006 :  15:03:28  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Are you even looking at the error? Your variable isn't spelled correctly. @tablebame

Tara Kizer



let the ass-kicking commence..



-ec
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts
Posted - 08/23/2006 :  15:14:57  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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_voter
00513

----------------------------------------------------------------------------------------

why @tableName is not turning into 00513

-------------------------
gongxia649
-------------------------
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 08/23/2006 :  15:17:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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_unit
from hava_dev.dbo.TempAddressParsingTable a
inner join hava_DCDE.dbo.' + @tablename + ' dcde
on dcde.id_town = a.id_town
and 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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts
Posted - 08/23/2006 :  15:20:21  Show Profile  Visit gongxia649's Homepage  Reply with Quote
u know what just kick my ass...i made another mistake.

-------------------------
gongxia649
-------------------------
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 08/23/2006 :  15:43:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by gongxia649

u know what just kick my ass...


I may have to take you up on that offer.

Tara Kizer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/23/2006 :  18:11:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts
Posted - 08/23/2006 :  21:05:38  Show Profile  Visit Merkin's Homepage  Reply with Quote
This thread is awesome.


Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 08/23/2006 :  22:04:01  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
gongxia649 -- you could consider a better database design that won't require dynamic sql for basic SQL statements.

- Jeff
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/24/2006 :  02:48:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by Merkin

This thread is awesome.
The original poster is interesting.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2111 Posts
Posted - 08/24/2006 :  06:48:17  Show Profile  Reply with Quote
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.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts
Posted - 08/25/2006 :  11:42:23  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts
Posted - 08/25/2006 :  11:42:58  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts
Posted - 08/25/2006 :  12:04:30  Show Profile  Reply with Quote
quote:
Originally posted by gongxia649

how? 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
Go to Top of Page

KenW
Constraint Violating Yak Guru

USA
391 Posts
Posted - 08/25/2006 :  12:33:12  Show Profile  Reply with Quote
quote:

how? can you explain?


Because you're using dynamic SQL when you probably don't need to?

Ken
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000