SQL 2005 - Stored procedure parameter problem

[satanorz]

Hi all!, I've a SQL Express 2005 database that have many tables with AFTER INSERT/UPDATE triggers that does the same on all the tables.

Then, i've created an StoredProcedure to be called within these triggers.. so less repeteable code and an advantage on future changes..

The stored proc:

CREATE PROCEDURE [dbo].[sp_ControlarModificacion]
-- Add the parameters for the stored procedure here
@Tabla table,
@Clave int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
update @Tabla set FechaModificacion = GETDATE(), UsuarioModificacion = USER where clave = @clave
END


And the call from the trigger will be some like..


exec dbo.sp_ControlarModificacion inserted, @Clave


But i've a problem, SQL Server throw me the error "Incorrect syntax near the keyword 'table "when i try to save the stored procedure.

Some ideas? Thanks!

[Alsvha]

I do not think you can parse a table as input to a SPROC in SQL Server 2005 (or earlier).

You'll have to parse the specific parameters in, instead of the table.

[satanorz]

The problem is that i need the parameter to be a table, because i need to make an update on it :S

[satanorz]

Solved! :cool: :D

I've found a solution for my problem googling for a while.

Instead of passing the table, i pass only the name of it.
Then, i create a nvarchar variable to build the query using the table name parameter..

Finally we can run the query using the storedproc sp_executesql

An example acording my problem..


CREATE PROCEDURE [dbo].[sp_ControlarModificacion]
-- Add the parameters for the stored procedure here
@Tabla varchar(50),
@Clave int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Consulta nvarchar(200)

SET @Consulta = 'update ' + @Tabla + ' set FechaModificacion = GETDATE(), UsuarioModificacion = USER where clave = ' + str(@Clave)
exec sp_executesql @Consulta
END


Hope it helps someone that have the same problem! :D

[cjard]

i would never do this.. the performance implications are horrendous