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
johan777Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dishan FernandoSoftware Engineer / DBACommented:
try this..
create procedure validate_daterange
          @validate_tbl varchar(50),
          @result int output
  as
  begin
    EXEC('select @result = count(*) from '+@validate_tbl+'')
  end
go
0
rajeshjacob3Commented:

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
0
namasi_navaretnamCommented:
You cannot pass table or cursur as input parameter.

You can create and populate a temp table in one proc (Proc1) and access the the same temp table in other procs if called with the same transaction. To may the proc compile just create the temptable outside the proc

create table #temptable
(
col1 int,
col2 int
)

go

Create Proc1 as
BEGIN
Insert #temptable
select col1, col2 from MyTable
End
go

Create Proc2
as
BEGIN
  select * from #temptable
END

go

HTH

Namasi

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johan777Author Commented:
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
0
Scott PletcherSenior DBACommented:
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.
0
namasi_navaretnamCommented:
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

0
johan777Author Commented:
Thanx Namasi
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.