Solved

Passing a table as parameter to a stored procedure

Posted on 2003-11-06
7
435 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
7 Comments
 
LVL 8

Expert Comment

by:dishanf
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 250 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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:ScottPletcher
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now