store 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

SQL Server Development (2000)

store procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-25 : 09:00:11

SP writes "Hi

i passed a parameter(table name) in store procedure and i want use this table name in store proc. how will use..??

example :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[itemcorrect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[itemcorrect]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure itemcorrect(@sptable varchar(40))
as
begin

declare @oitemcode varchar(16)
declare @ditemcode varchar(16)
declare @ritemcode varchar(16)

declare item cursor for

select item_code,item_code1 from data2

/*declare item1 cursor for */

/*select item_code from item_mst*/

open item
fetch next from item into @ditemcode,@ritemcode
while @@fetch_status=0
begin
select item_code from @sptable where item_code=@ditemcode

if (@@rowcount)>0
update @sptable set item_code=@ritemcode where item_code=@ditemcode
fetch next from item into @ditemcode,@ritemcode

end
Close item
Deallocate item
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

do u have solution for above..???"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-25 : 09:29:10

Why are you using a cursor?
If you want to do it like this then I suggest you shouldn't be using t-sql - do it in the client instead.

To use a table variable you will have to use dynamic sql - look at the faqs.

select @sql = 'select * from ' + @table

exec (@sql)

put it into a temp table if you need to work on it.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources