Tables as Stored Proc Parms

Question

Post reply

Tables as Stored Proc Parms

Michael Cordova

Grasshopper

Points: 10
More actions
August 21, 2005 at 9:04 pm

#94605

Hi,
I am trying to pass a table to a stored proc and can't get it to work. Basically I want something like this to work:
   CREATE PROCEDURE dbo.ps_InsUpd
   @insertedTable table,
   @deletedTable table
   AS
   select * into #inserted from @insertedTable
   select * into #deleted from @deletedTable
The tables are the special tables inserted and deleted from the trigger for for insert, update, delete. Can you provide me the syntax to do this?
Thanks,
Michael

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

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

sqlpro Hall of Fame Points: 3859 More actions · Answer 1

Michael

you might want to try dynamic sql.something like

declare @p_sql varchar(1000)

set @p_sql ='select * into #inserted from '+ @insertedTable

exec(@p_sql )

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

No need for dynamic sql here.. why can't you do the job in the trigger?

Michael Cordova Grasshopper Points: 10 More actions · Answer 3

Remi,

Good question. The reason I want to move the tables to a Stored Proc is that I want one set of code that is invoked from multiple triggers for maintenance reasons.

JSBBS Valued Member Points: 65 More actions · Answer 4

Reading SQL Server Books Online (BOL) for "CREATE PROCEDURE" states that...

"All data types, except the table data type, can be used as a parameter for a stored procedure"

I had the same issue. In the end I wrote my own T-SQL code generator (C# winform) that scripted the trigger generation and the audit tables based on user-selection of db, tables (etc).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Does it have to be done at the same time of the update or can it be delayed a little? I'm thinking that you could flag the rows that need processing and having a jog firing every x minutes... or when needed to finish the job. Temp tables could also do the job, but I'd be worried of dead locks (or long waits because of locks).