January 8, 2007 at 12:28 pm
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.
January 8, 2007 at 1:15 pm
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
January 8, 2007 at 2:37 pm
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.
January 9, 2007 at 1:13 am
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/
January 9, 2007 at 4:41 am
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')
January 9, 2007 at 2:11 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply