johan777
asked on
Passing a table as parameter to a stored procedure
Hi all,
I want to pass a table to a stored procedure as parameter for validating in the stored procedure. How do I go about to accomplish this. Here follows an example of how I thought it should work, but it seems I'm doing something wrong.
My reasoning for doing the validation in a seperate stored proc rather than in the trigger is that the same validation will be duplicated in more than one trigger. Extracting the validation process makes for cleaning code and only one place to change the business rules, if so required.
Regards,
Johan Swart
Example:
-- Stored proc definition
create procedure validate_daterange
@validate_tbl table (fld_a datetime, fld_b datetime),
@result int output
as
begin
select @result = count(*)
from @validate_tbl
where fld_a <= fldb);
end;
go
-- Table definition
create table main_tbl (fld_from datetime, fld_to datetime)
go
-- Trigger definition
create trigger main_tbl_tr1
instead of insert
as
begin
declare @result int;
set @result = 0;
execute validate_daterange (inserted, @result)
if (result = 0)
begin
insert into main_tbl
select * from inserted;
end
end;
go
I want to pass a table to a stored procedure as parameter for validating in the stored procedure. How do I go about to accomplish this. Here follows an example of how I thought it should work, but it seems I'm doing something wrong.
My reasoning for doing the validation in a seperate stored proc rather than in the trigger is that the same validation will be duplicated in more than one trigger. Extracting the validation process makes for cleaning code and only one place to change the business rules, if so required.
Regards,
Johan Swart
Example:
-- Stored proc definition
create procedure validate_daterange
@validate_tbl table (fld_a datetime, fld_b datetime),
@result int output
as
begin
select @result = count(*)
from @validate_tbl
where fld_a <= fldb);
end;
go
-- Table definition
create table main_tbl (fld_from datetime, fld_to datetime)
go
-- Trigger definition
create trigger main_tbl_tr1
instead of insert
as
begin
declare @result int;
set @result = 0;
execute validate_daterange (inserted, @result)
if (result = 0)
begin
insert into main_tbl
select * from inserted;
end
end;
go
This is seems ok but you can not pass a table as parameter in a procedure.
you can achieve this by passing the table name as parameter to the proc and dynamically make the query.
create procedure proc
@table1 varchar(100),
@result int output
as
begin
EXEC('select @result = count(*) from '+@table1 +'')
end
go
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi everyone,
Thanx for the responses so far.
Please note that I sepcifically want to pass the inserted table (or a pointer to it if possible) from the trigger.
I try to stay away from creating another temp table with the same values as I do not know at runtime the size of the dataset that the user is trying to insert.
Do I understand correctly from the BOL that it is possible to send a table as parameter?
Regards,
Js
Thanx for the responses so far.
Please note that I sepcifically want to pass the inserted table (or a pointer to it if possible) from the trigger.
I try to stay away from creating another temp table with the same values as I do not know at runtime the size of the dataset that the user is trying to insert.
Do I understand correctly from the BOL that it is possible to send a table as parameter?
Regards,
Js
AFAIK you cannot pass a table or table variable to a SP, although BOL states/implies otherwise.
I understand the desire to use a subroutine/avoid duplicating code, but in this situation that will result in a *lot* of additional overhead. I suggest you stream-line the code as much as possible (always true for any trigger code) and then add that block of code in-line to any trigger that needs it. Yes, if the logic changes, you will have to change the code in multiple places, but hopefully that won't be often. You could add a particular comment string before the code to help find any uses of the code later, for example:
-- Date_Validation_001
...
Later, if the logic does change, you could scan "syscomments" (assuming SPs are not encrypted) to find all the occurences of Date_Validation_001 to determine all the places in which the code needs to be changed.
I understand the desire to use a subroutine/avoid duplicating code, but in this situation that will result in a *lot* of additional overhead. I suggest you stream-line the code as much as possible (always true for any trigger code) and then add that block of code in-line to any trigger that needs it. Yes, if the logic changes, you will have to change the code in multiple places, but hopefully that won't be often. You could add a particular comment string before the code to help find any uses of the code later, for example:
-- Date_Validation_001
...
Later, if the logic does change, you could scan "syscomments" (assuming SPs are not encrypted) to find all the occurences of Date_Validation_001 to determine all the places in which the code needs to be changed.
Just would like to ass to this. Cursor can be used as output parameter though.
Thanks to CJ_S for this.
USE pubs
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT au_lname
FROM authors
WHERE au_lname LIKE 'S%'
OPEN @OutCrsr
GO
/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR
/* Execute the procedure created earlier to fill
the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT
/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @CrsrVar
END
CLOSE @CrsrVar
DEALLOCATE @CrsrVar
GO
Thanks to CJ_S for this.
USE pubs
GO
/* Create a procedure with a cursor output parameter. */
CREATE PROCEDURE OpenCrsr @OutCrsr CURSOR VARYING OUTPUT AS
SET @OutCrsr = CURSOR FOR
SELECT au_lname
FROM authors
WHERE au_lname LIKE 'S%'
OPEN @OutCrsr
GO
/* Allocate a cursor variable. */
DECLARE @CrsrVar CURSOR
/* Execute the procedure created earlier to fill
the variable. */
EXEC OpenCrsr @OutCrsr = @CrsrVar OUTPUT
/* Use the variable to fetch the rows from the cursor. */
FETCH NEXT FROM @CrsrVar
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @CrsrVar
END
CLOSE @CrsrVar
DEALLOCATE @CrsrVar
GO
ASKER
Thanx Namasi
create procedure validate_daterange
@validate_tbl varchar(50),
@result int output
as
begin
EXEC('select @result = count(*) from '+@validate_tbl+'')
end
go