passing table name as parameter in a stores procedure

[y3by]

hi ppl ,
i'm trying to do a sp that does the same thing in 4 diferent tables...
the best was to create a sp, where the table name is a parameter...
what is the syntax to do that
i've done something like this

create sp_insertX (@table, @Cod, @descr)

AS

DECLARE @ErrorCode int
DECLARE @Result int


BEGIN TRANSACTION
IF @Cod IN (SELECT Code FROM @Table)
BEGIN
UPDATE @Table SET Descri = @Descr.
SET @ErrorCode = @@ERROR
IF (@ErrorCode <> 0 )
BEGIN
set @Result = ( -@ErrorCode )
ROLLBACK TRANSACTION
-- In case of ERROR RollBack and return the Error
Return @Result
END
set @Result=1
COMMIT TRANSACTION
RETURN @Result
END
ELSE
BEGIN
INSERT INTO @Table(Code,Descri)
VALUES (@Cod,@Descr)
SET @ErrorCode = @@ERROR
IF (@ErrorCode <> 0 )
BEGIN
set @Result = ( -@ErrorCode )
ROLLBACK TRANSACTION
-- In case of ERROR RollBack and return the Error
Return @Result
END
set @Result=2
COMMIT TRANSACTION
RETURN @Result
END


GO


thanks for your help and please correct my sp if i'm thinking in a bad way

 

[AWithers]

basically you need to do it as dynamic sql, hopefully the following will get you started

Andy

CREATE PROCEDURE [yourprocname]
@tablename

as

declare @sql

set @sql ='select * from '+@tablename'

exec(@sql)

 

[y3by]

hiii
thanks for the tip but i think i'm having a problem with quotation!
in &quot;set @sql='select * from '+@tablename'&quot; and another problem with the &quot;declare @sql&quot;
could you point te way to correct this!?
thanks again

 

[AWithers]

Sorry

try this instead, I forgot the datatype/size for @sql and have changed the quotation

Andy

CREATE PROCEDURE [yourprocname]
@tablename

as

declare @sql varchar(8000)

set @sql =&quot;select * from &quot;+@tablename

exec(@sql)