?
Solved

Passing a table as parameter to a stored procedure

Posted on 2003-11-06
7
Medium Priority
?
441 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:johan777
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9699875
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
 

Expert Comment

by:rajeshjacob3
ID: 9700322

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
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 750 total points
ID: 9700551
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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:johan777
ID: 9700973
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9701412
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9714644
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
 

Author Comment

by:johan777
ID: 9720598
Thanx Namasi
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question