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

Материал из SQL эксперт
Перейти к: навигация, поиск

Parameter for passing value out of a procedure

   <source lang="sql">

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></source>


Recursive procedure with output parameter

   <source lang="sql">

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</source>


Stored procedure returning value using an Output parameter

   <source lang="sql">

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.</source>