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
 insert into in SP won't run
 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/21/2006 :  14:35:34  Show Profile  Visit gongxia649's Homepage  Reply with Quote
-- 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)

USA
7020 Posts

Posted - 08/21/2006 :  14:43:03  Show Profile  Reply with Quote
Does this variable appear to have the correct length?
declare @sql varchar(20)


CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2006 :  14:43:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 08/21/2006 14:44:44
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/21/2006 :  14:43:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run PRINT @SQL after the SET statement. Verify that the string is in the correct format.

Tara Kizer
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/21/2006 :  14:47:15  Show Profile  Visit gongxia649's Homepage  Reply with Quote



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

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/21/2006 :  14:49:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2006 :  14:49:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You must concatenate the string properly. Add @TableName last with + operator, as described in my previous posting.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2006 :  14:49:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Where's the "Shoot the Yak" icon?


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 08/21/2006 14:49:56
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/21/2006 :  14:55:28  Show Profile  Visit gongxia649's Homepage  Reply with Quote
-- 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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/21/2006 :  14:59:46  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2006 :  15:00:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/21/2006 :  15:04:54  Show Profile  Visit gongxia649's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/21/2006 :  15:07:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
gongxia, please post what the PRINT statement is showing. It will show exactly what the problem is.

Tara Kizer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/21/2006 :  15:08:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What does the PRINT @SQL tell you?

Post only the result of PRINT here...

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/21/2006 :  15:25:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 08/21/2006 15:27:10
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/21/2006 :  15:28:17  Show Profile  Visit gongxia649's Homepage  Reply with Quote
-- 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.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/21/2006 :  15:32:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need exec (@sql).

Tara Kizer
Go to Top of Page

gongxia649
So Suave

Azores
344 Posts

Posted - 08/21/2006 :  15:33:20  Show Profile  Visit gongxia649's Homepage  Reply with Quote
oh thanks it worked... why do i need brakets?

gongxia649
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/21/2006 :  15:35:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need parenthesis because otherwise it thinks you are trying to run a stored procedure. See EXEC in BOL.

Tara Kizer
Go to Top of Page

msantosa
Starting Member

16 Posts

Posted - 08/21/2006 :  15:37:40  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/21/2006 :  15:38:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
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.09 seconds. Powered By: Snitz Forums 2000