insert into in SP won't run - 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

insert into in SP won't run

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2006-08-21 : 14:35:34

-- drop proc usp_a
create proc usp_a @tableName varchar(30) as

begin
declare @sql varchar(20)
set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo."@tableName"'
exec @sql
end

exec usp_a 00519

after running SP, QAnalyzer shows the error.
" Could not find stored procedure 'insert into address '. "

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-21 : 14:43:03

Does this variable appear to have the correct length?

declare @sql varchar(20)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 14:43:15

1) declare @sql varchar(200)

2) set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo.' + @tableName

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 14:43:52

Run PRINT @SQL after the SET statement. Verify that the string is in the correct format.

Tara Kizer

gongxia649
So Suave

344 Posts

Posted - 2006-08-21 : 14:47:15

-- drop proc usp_a
create proc usp_a @tableName varchar(300) as

begin
declare @sql varchar(200)

set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo."@tableName"'

exec @sql
PRINT @SQL

end

exec usp_a 00519

Server: Msg 911, Level 16, State 1, Line 8
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 14:49:05

quote:
Originally posted by gongxia649

-- drop proc usp_a
create proc usp_a @tableName varchar(300) as

begin
declare @sql varchar(200)

set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo."@tableName"'

exec @sql
PRINT @SQL

end

exec usp_a 00519

Server: Msg 911, Level 16, State 1, Line 8
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.

So what does the PRINT statement show you?

You need to use the + sign to concatenate your variable to the rest of the string. See Peso's post.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 14:49:05

You must concatenate the string properly. Add @TableName last with + operator, as described in my previous posting.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 14:49:44

Where's the "Shoot the Yak" icon?

Peter Larsson
Helsingborg, Sweden

gongxia649
So Suave

344 Posts

Posted - 2006-08-21 : 14:55:28

-- drop proc usp_a
create proc usp_a @tableName varchar(300) as

begin
declare @sql varchar(200)
--declare @tableName varchar(300)
--set @tableName = '00519'
set @sql = 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo.' + @tableName

PRINT @SQL
exec @sql
end

exec usp_a "00519"

insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration.dbo.00519
Server: Msg 911, Level 16, State 1, Line 10
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select (id_voter) from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-21 : 14:59:46

quote:
Originally posted by Peso

Where's the "Shoot the Yak" icon?

Peter Larsson
Helsingborg, Sweden

He appears unwilling to put any effort at all into solving even simple problems like this.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 15:00:38

drop the parathesis around select id_voter!

set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo.' + @tableName

Peter Larsson
Helsingborg, Sweden

gongxia649
So Suave

344 Posts

Posted - 2006-08-21 : 15:04:54

create proc usp_a @tableName varchar(300) as

declare @sql varchar(200)
--declare @tableName varchar(300)
--set @tableName = '00519'
set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo.' + @tableName

exec @sql
--PRINT @SQL

exec usp_a "00519"

Server: Msg 911, Level 16, State 1, Line 10
Could not locate entry in sysdatabases for database 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration'. No entry found with that name. Make sure that the name is entered correctly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 15:07:31

gongxia, please post what the PRINT statement is showing. It will show exactly what the problem is.

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 15:08:11

What does the PRINT @SQL tell you?

Post only the result of PRINT here...

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 15:25:31

So did you notice that there are double quotes around your table name? Do you think that's the problem?

EDIT: He erased his reply.

Tara Kizer

gongxia649
So Suave

344 Posts

Posted - 2006-08-21 : 15:28:17

-- drop proc usp_a
create proc usp_a @tableName varchar(300) as

declare @sql varchar(200)
--declare @tableName varchar(300)
--set @tableName = "00519"
set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo.' + '"'+ @tableName +'"'
--set @sql = 'insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo."00519"'

PRINT @SQL
exec @sql

exec usp_a "00519"

the print shows:
insert into address (id_voter) select id_voter from SS_Import_Voter_Registration.dbo."00519" which is right. run it and it inserts records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 15:32:04

You need exec (@sql).

Tara Kizer

gongxia649
So Suave

344 Posts

Posted - 2006-08-21 : 15:33:20

oh thanks it worked... why do i need brakets?

gongxia649

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 15:35:18

You need parenthesis because otherwise it thinks you are trying to run a stored procedure. See EXEC in BOL.

Tara Kizer

msantosa
Starting Member

16 Posts

Posted - 2006-08-21 : 15:37:40

ALTER proc usp_a
@tableName varchar(30)

AS

BEGIN
Declare @Sql varchar(200)
set @Sql = 'insert into address (id_voter) SELECT(id_voter) from
SS_Import_voter_Registration.dbo.00'+@tablename
print @sql
exec @sql
END

exec usp_a '00519' -->give single quote on the parameter

Make sure you're on the same database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-21 : 15:38:59

quote:
Originally posted by msantosa

ALTER proc usp_a
@tableName varchar(30)

AS

BEGIN
Declare @Sql varchar(200)
set @Sql = 'insert into address (id_voter) SELECT(id_voter) from
SS_Import_voter_Registration.dbo.00'+@tablename
print @sql
exec @sql
END

exec usp_a '00519' -->give single quote on the parameter

Make sure you're on the same database.

He still needs exec (@sql).

Tara Kizer

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources