Replacing table name with a variable in SP

Question

Post reply

Replacing table name with a variable in SP

pavan-178969

Mr or Mrs. 500

Points: 512
More actions
January 8, 2007 at 12:28 pm

#68129

I tried creating the SP in sql server 2000
-----------------------------------------------------
create PROCEDURE Test_sp @filename1 varchar(200)
as
set nocount on
insert into @filename1
select 'loginame'     = left(loginame, 30),
       'hostname'     = left(hostname,30),
       'datagbase'    = left(db_name(dbid),30),
       'spid'         = str(spid,4,0),
       'block'        = str(blocked,5,0),
       'phys_io'      = str(physical_io,8,0) from master..sysprocesses
GO
-----------------------------------------------------
I got the error
Must declare the variable '@filename1'.
Please help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ram4tech Hall of Fame Points: 3140 More actions · Answer 1

Declare @filename1 as an output parameter.

create PROCEDURE Test_sp @filename1 varchar(200) OUTPUT

as

set nocount on

select @filename1 = left(loginame, 30) + left(hostname,30) + left(db_name(dbid),30) + str(spid,4,0) + str(blocked,5,0) + str(physical_io,8,0) from master..sysprocesses

GO

pavan-178969 Mr or Mrs. 500 Points: 512 More actions · Answer 2

I do not want to concatenate the query.

My sole aim is th pass the table name at run time, please do let me know if there is anyway we can pass table name at runtime in the stored proc.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

This is pretty basic T SQL, could I suggest that if you have to ask this then the purchase of a couple of good books or a training course would be more beneficial ? I figure reading BOL would help.

In answer to your question you pass the name in and bind it into a dynamic string which you then execute - this isn't really a good way to write a proc or statement as the dynamic sql will cause a recompilation and you will have to grant table permissions to allow the statement to run.

http://www.oreilly.com/catalog/wintrnssql/chapter/ch01.html

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 4

Use Execute function which evaluates the string to an SQL query

build a string fot the insert query and pass it to Execute fubnction

ex:

replace ur insert query with the following ..

Execute('insert into'+ @filename1+' select ''loginame'' = left(loginame, 30), ''hostname'' = left(hostname,30), ''datagbase'' = left(db_name(dbid),30),''spid'' = str(spid,4,0), ''block'' = str(blocked,5,0), ''phys_io'' = str(physical_io,8,0) from master..sysprocesses')

Thanks ,

Shekhar

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

I agree with Colin. You'll get much better performance if you avoid using dynamic SQL. I know that it's required sometimes, but if it can be avoided it should. Try to create several procedures, one for each table into which you will need to insert data.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]