tablename in stored procedure - 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

SQL Server 2000 Forums

Transact-SQL (2000)

tablename in stored procedure

Author

Topic

Swigbert
Starting Member

1 Post

Posted - 2002-04-23 : 17:14:04

I'm trying to pass a tablename to a sp and have the sp populate
the table. Error msg is needing to declare the variable I've assigned
the tablename to.

create procedure PopGolf
@dbGreat varchar(30),
@lineno int
as
declare @tablename varchar(128)
declare @stopval int
declare @recordname varchar(30)
declare @count int

set @stopval = @lineno
set @tablename = (select name from sysobjects where name = @dbGreat)
set @count = (select count(*) from MastersGolf)
set @count = @count + 1
set @count = 1
while (@count < @stopval)
begin
set @recordname = 'GolfGreat'+cast(@count as varchar)
insert @tablename values (@count, @recordname, GETDATE())
set @count = @count + 1
end
go

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-04-23 : 17:53:37

Here is how I would do it:

create procedure PopGolf
@dbGreat varchar(30),
@lineno int
as
declare @tablename varchar(128)
declare @stopval int
declare @recordname varchar(30)
declare @count int

set @stopval = @lineno
select @tablename=name from sysobjects where name = @dbGreat
select @count=count(*) from MastersGolf
set @count = @count + 1
set @count = 1
while (@count < @stopval)
begin
set @recordname = 'GolfGreat'+cast(@count as varchar)

EXEC('insert ' + @tablename + 'values (@count, @recordname, GETDATE())')
set @count = @count + 1
end
go

*************************
Just trying to get things done

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources