Create and execution of stored procedures: create procedure [INSERT_MEMBERS_1] (@ id_1 [tinyint], @ name_2 [char] (10), @ password_3 [varcha] (50), @ sex_4 [char] (6), @ birth_5 [datetime ], @ Salary_7 [Money]) AS INSERT INTO [MyGuest]. [DBO]. [MEMBERS] ([ID], [Name], [PASSWORD], [SEX], [Birth], [SALARY]) VALUES (@ ID_1, @ Name_2, @ Password_3, @ SEX_4, @ Birth_5, @ Salary_7) GoExecute Insert_members_1 1, 'Bitan', 'Aaaaaa', 'Male', '19200202', $ 3200.20 Result: Table Members will add a record.
Another example of the stored procedure: / * create procedure update_members_id (@id int, @ name varchar (20)) as update members set name = @ name where id = @ id * / execute update_members_id 3, 'susan'; select * from Members; Results: 3 Susan aaaa male 1979-01-09 00: 00: 00.000 0x00000000000001A9 5000.0000
Create and execution of stored procedures: create procedure [INSERT_MEMBERS_1] (@ id_1 [tinyint], @ name_2 [char] (10), @ password_3 [varcha] (50), @ sex_4 [char] (6), @ birth_5 [datetime ], @ Salary_7 [Money]) AS INSERT INTO [MyGuest]. [DBO]. [MEMBERS] ([ID], [Name], [PASSWORD], [SEX], [Birth], [SALARY]) VALUES (@ ID_1, @ Name_2, @ Password_3, @ SEX_4, @ Birth_5, @ Salary_7) GoExecute Insert_members_1 1, 'Bitan', 'Aaaaaa', 'Male', '19200202', $ 3200.20 Result: Table Members will add a record.
Another example of the stored procedure: / * create procedure update_members_id (@id int, @ name varchar (20)) as update members set name = @ name where id = @ id * / execute update_members_id 3, 'susan'; select * from members; result: 3 susan aaaaa male 1979-01-09 00: 00: 00.000 0x00000000000001A9 5000.0000 create procedure select_members_by_id @id int as select * from members where id = @ id; execute select_members_by_id 3 using sub queries in a stored procedure: create procedure Multi_operation_members @ID INTASSELECT * from MEMBERS WHERE ID <(Select Count (*) from membrate) Execute Multi_operation_members 30
Modify the parameter value during the stored procedure, use multiple query statements: create procedure multi_operation_members_2 @ID Intas select @ID = (Select Count (*) from membrate where id> @ID) SELECT @ID = @ID - 2select * from membrom members where ID = @IDEXECUTE MULTI_OPERATION_MEMBERS_2 3
Use the While loop during the stored procedure, use multiple query statements: create procedure while_circle_members @ID int asclare @in int // Define temporary variable select @IN = 0 // Give the temporary variable to assign the initial value while @IN <@ID ///// Set the cycle condition beginselect @IN = @IN 1 // Temporary variables to increase SELECT * from membrate delete from membrate where id = @in endexecute while_circle_members 10
Call another procedure stored in the procedure storage create procedure procedure_1 @str varchar (20) asselect 'execute procedure_1' as message, @str as name; gocreate procedure procedure_2 @str varchar (20) asselect 'begin procedure_2' as message, @str AS NameExecute Procedure_1 @strselect 'end procedure_2' as message, @str as namegoexecute procedure_2 'bitan' Result: Message Name ------------------------- ----------- Begin Procedure_2 Bitan (the number of rows affected) Message name ----------------------- --------------- EXECUTE Procedure_1 Bitan (the number of rows affected) Message name ------------------- --------------- End procedure_2 bitan (the number of rows) is 1 line) View the stored procedure that has been created in the system table: / * create proc my_proc_2asprint 'this is my_proc_2'Return * / SELECT CONVERT (CHAR (10), So.name) 'Name', Convert (CHAR (60), Sc.text) 'Text', Convert (Char (30), So.crdate 'CRDATE' from Sysobjects SO , syscomments schere sol.id = sc.id and soli.name = 'my_proc_2' Result: Name Text CRDATE ---------- --------------------------------------- ------------------------------------------------- - MY_PROC_2 CREATE PROC MY_PROC_2 AS PRINT 'THIS MY_PROC_2' RETURN 03 15 2005 9:53 AM (the number of rows of rows) uses input parameters and output parameters: create proc my_proc_4 (@i int, @j int, @z INT output @z = @i @JPrint 'my_proc_4:' RTRIM (Convert (char, @ i)) ' '
RTRIM (Convert (Char, @ J)) '=' RTRIM (Convert (Char, @ z)) Return Declare @SUM INTEXECUTE MY_PROC_4 1, 2, @ Sum OutputPrint 'Result:' RTRIM (CHAR, @SUM) EXECUTE my_proc_4 @ J = 4, @ i = -10, @ z = @ Sum Outputprint 'Result:' RTRIM (Convert) Result: My_Proc_4: 1 2 = 3Result: 3MY_PROC_4: -10 4 = -6Result: -6 Sets the default value for the input parameter: create proc my_proc_5 (@i int = 5, @J INT = 6, @z int output) assselect @z = @i @jprint 'my_proc_5 : ' RTRIM (Convert (CHAR, @ i)) ' RTRIM (Convert (CHAR, @J)) '=' RTRIM (Convert (char, @ z)) Return Declare @SUM INTEXEC MY_PROC_5 @ @ Z = @Sum OutputPrint 'Result:' RTRIM (Convert) EXEC MY_PROC_5 @i = -10, @z = @sum outputprint 'Result:' RTRIM (Convert) EXEC (CHAR, @ SUM) MY_PROC_5 @i = -5, @J = -10, @z = @Sum Outputprint 'Result:' RTRIM (Convert) EXEC MY_PROC_5 Default, Default, @z = @Sum Outputprint 'Result:' RTRIM (CHAR, @ SUM) EXEC MY_PROC_5 @J = -15, @i = default, @z = @Sum Outputprint 'Result:' RTRIM (Convert (char, @ SUM) Result: My_PROC_5: 5 6 = 11Result: 11my_Proc_5: -10 6 = -4Result: -4my_Proc_5: -5 -10 = -15Result: -15my_Proc_5: 5 6 = 11Result: 11my_Proc_5: 5 -15 = -10Result: -10 Use the default value for parameter check: alter proc my_proc_6 (@i int = 0) asDeclare @s charif @i =
