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.