Prev: Question on triggers for our audit tables
Next: Trigger to insert column value
From: mscertified on 17 Jul 2008 13:04
In a sproc I need to issue a select, it returns five columns and always only
one row. I need to pass those five values back to the calling proc in output
parameters. Any of the five values could be Null. How do I do this? My 1360
page book (Wrox) does not show me. All the examples have ony one output
parameter. Thanks.
From: Plamen Ratchev on 17 Jul 2008 13:20
You do it exactly the same way you pass one output parameter. Here is
example with 3 output parameters:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
col1 CHAR(1),
col2 CHAR(1),
col3 CHAR(1));

INSERT INTO Foo VALUES(1, 'a', 'b', NULL);

GO

CREATE PROCEDURE GetFoo
@keycol INT,
@col1 CHAR(1) OUTPUT,
@col2 CHAR(2) OUTPUT,
@col3 CHAR(3) OUTPUT
AS
SELECT @col1 = col1, @col2 = col2, @col3 = col3
FROM Foo
WHERE keycol = @keycol;

GO

DECLARE @c1 CHAR(1), @c2 CHAR(1), @c3 CHAR(1);

EXEC GetFoo 1, @c1 OUTPUT, @c2 OUTPUT, @c3 OUTPUT;

SELECT @c1, @c2, @c3;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Eric Isaacs on 17 Jul 2008 13:30
You just add the 5 parameters with OUTPUT after them.

If you call the sproc from within SQL, just specify OUTPUT after each
of the parameter names that is being passed to the procedure with the
5 output parameters.

If you have a specific example of what you're looking for, a more
detailed answer can be provided.


-Eric Isaacs
 | 
Pages: 1
Prev: Question on triggers for our audit tables
Next: Trigger to insert column value