SQL Server/T-SQL Tutorial/Procedure Function/Output parameter

Parameter for passing value out of a procedure

3>
4>
5> CREATE TABLE authors(
6>    au_id          varchar(11),
7>    au_lname       varchar(40)       NOT NULL,
8>    au_fname       varchar(20)       NOT NULL,
9>    phone          char(12)          NOT NULL DEFAULT ("UNKNOWN"),
10>    address        varchar(40)           NULL,
11>    city           varchar(20)           NULL,
12>    state          char(2)               NULL,
13>    zip            char(5)               NULL,
14>    contract       bit               NOT NULL
15> )
16> GO
1> insert authors values("1",  "Joe",   "Abra",   "111 111-1111", "6 St.", "Berkeley",  "CA", "11111", 1)
2> insert authors values("2",  "Jack",  "Majo",   "222 222-2222", "3 St.", "Oakland" ,  "CA", "22222", 1)
3> insert authors values("3",  "Pink",  "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4",  "Blue",  "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5",  "Red",   "Anne",   "555 555-5555", "6 Av.", "Regina",    "SK", "55555", 1)
6> insert authors values("6",  "Black", "Michel", "666 666-6666", "3 Pl.", "Regina",    "SK", "66666", 1)
7> insert authors values("7",  "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8",  "Yellow","Heather","888 888-8888", "3 Pu",  "Vacaville", "CA", "88888", 0)
9> insert authors values("9",  "Gold",  "Dep",    "999 999-9999", "5 Av.", "Oakland",   "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean",   "000 000-0000", "4 Av.", "Oakland",   "CA", "00000", 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE PROC count_tables @authorcount int OUTPUT,
3> @titlecount int OUTPUT
4> AS
5> SELECT * FROM authors
6> SET @authorcount=@@ROWCOUNT
7> SET @titlecount=@@ROWCOUNT
8> RETURN(0)
9> GO
1>
2> DECLARE @a_count int, @t_count int
3> EXEC count_tables @a_count OUTPUT, @t_count OUTPUT
4> GO
au_id       au_lname                                 au_fname             phone        address                                  city                 state zip   contract
----------- ---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------
1           Joe                                      Abra                 111 111-1111 6 St.                                    Berkeley             CA    11111        1
2           Jack                                     Majo                 222 222-2222 3 St.                                    Oakland              CA    22222        1
3           Pink                                     Cherry               333 333-3333 5 Ln.                                    Vancouver            BC    33333        1
4           Blue                                     Albert               444 444-4444 7 Av.                                    Vancouver            BC    44444        1
5           Red                                      Anne                 555 555-5555 6 Av.                                    Regina               SK    55555        1
6           Black                                    Michel               666 666-6666 3 Pl.                                    Regina               SK    66666        1
7           White                                    Sylvia               777 777-7777 1 Pl.                                    Rockville            MD    77777        1
8           Yellow                                   Heather              888 888-8888 3 Pu                                     Vacaville            CA    88888        0
9           Gold                                     Dep                  999 999-9999 5 Av.                                    Oakland              CA    99999        0
10          Siler                                    Dean                 000 000-0000 4 Av.                                    Oakland              CA    00000        1
(10 rows affected)
1>
2> drop PROC count_tables;
3> GO
1> drop table authors;
2> GO
1>

Recursive procedure with output parameter

6>    CREATE PROC spTriangular
7>    @ValueIn int,
8>    @ValueOut int OUTPUT
9>    AS
10>    DECLARE @InWorking int
11>    DECLARE @OutWorking int
12>    IF @ValueIn != 1
13>    BEGIN
14>            SELECT @InWorking = @ValueIn - 1
15>            EXEC spTriangular @InWorking, @OutWorking OUTPUT
16>
17>            SELECT @ValueOut = @ValueIn + @OutWorking
18>    END
19>    ELSE
20>    BEGIN
21>            SELECT @ValueOut = 1
22>    END
23>    RETURN
24>    GO
1>
2>    DECLARE @WorkingOut int
3>    DECLARE @WorkingIn int
4>    SELECT @WorkingIn = 5
5>    EXEC spTriangular @WorkingIn, @WorkingOut OUTPUT
6>
7>    PRINT CAST(@WorkingIn AS varchar) + " Triangular is " + CAST(@WorkingOut AS varchar)
8>    GO
5 Triangular is 15
1>
2>    drop PROC spTriangular;
3>    GO

Stored procedure returning value using an Output parameter

7>
8> CREATE PROCEDURE spCalculateOutput
9>   @Value1      Float
10>  , @Value2      Float
11>  , @Operator    Char(10)
12>  , @Result      Float     Output
13> As
14>  IF @Operator = "+"
15>     SET @Result = @Value1 + @Value2
16>  ELSE IF @Operator = "-"
17>     SET @Result = @Value1 - @Value2
18>  ELSE IF @Operator = "*"
19>     SET @Result = @Value1 * @Value2
20>  ELSE IF @Operator = "/"
21>     SET @Result = @Value1 / @Value2
22>
23> -- Declare a variable for the result value
24> Declare @Out Float
25> -- Execute the procedure & assign the result
26> Execute spCalculate_Output 123, 456, "+"
27> -- Print the result value
28> Print @Out
29>
30>
31> drop procedure spCalculateOutput;
32> GO
Cannot add rows to sysdepends for the current object because it depends on the missing object "spCalculate_Output". The object will still be created.