SP is not reading the table name? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SP is not reading the table name?

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-23 : 08:52:19

-- 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

158 Posts

Posted - 2006-08-23 : 09:16:02

[code]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)[/code]

Regards
N

The 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.Name

That 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 @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
-------------------------

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. @tablebame

Tara 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. @tablebame

Tara 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_voter
00513

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

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_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

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 Larsson
Helsingborg, 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 Larsson
Helsingborg, 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.aspx

Learn SQL
http://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 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

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources