Solved

Passing a table as parameter to a stored procedure

Posted on 2003-11-06
7
440 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 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