SQL Study Notes: stored procedures and user-defined functions

SQL Study Notes: stored procedures and user-defined functions

I. Simple creation, modification, and deletion of Stored Procedures 1. Create a simple Stored Procedure Use adventureworks Go Create proc spemployee As Select * From HumanResources. run the code above to create a stored procedure. If you want to run this stored procedure, you can directly execute the exec spemployee Statement 2. alter proc [DBO]. [spemployee] As Select top 13 * From HumanResources. employee3. delete stored procedure drop proc DBO. spemployee 2: Input and Output Parameters of Stored Procedure 1. Stored Procedure with input parameters use adventureworks Go Create proc spemployee @ Lastname nvarchar (50) = NULL As If @ lastname is null Select top 13 * From HumanResources. Employee Else Select top 10 * From HumanResources. Employee You can use Exec spemployee '000000' Or directly exec spemployee Reload stored procedures... 2. Use adventureworks Go Alter proc spemployee @ Lastname nvarchar (50) = Null Output As If @ lastname is null Begin Print 'null' Return '20140901' End Else Begin Print @ lastname Return '20140901' End: Check the first statement to test the stored procedure. Declare @ myval nvarchar (50) Exec @ myval = spemployee @ myval output Print @ myval Output null 123 second statement to test the Stored Procedure declare @ myval nvarchar (50) Set @ myval = 'xland' Exec @ myval = spemployee @ myval output Print @ myval output xland 456 3: User-Defined Functions 1. User-defined functions that return scalar values First make a simple Date Processing Function Shorten the long date to the short date create function dbo. dayonly (@ date datetime) Returns varchar (12) As Begin Return convert (varchar (12), @ date, 101) End to test the above function, first make a script use accounting Declare @ counter int Set @ counter = 1 While @ counter <= 10 Begin Insert into orders values (1, dateadd (MI, @ counter, getdate (), 1) Set @ counter = @ counter + 1 End and then retrieve the newly inserted data record of this script use accounting Select * from orders where DBO. dayonly (date1) = DBO. dayonly (getdate () 2. Return the table's User-Defined Function First look at the example of use adventureworks Go Create Function DBO. fncontactsearch (@ lastname nvarchar (50 )) Returns table As Return (select * From person. Contact where lastname like @ lastname + '%') execute this example use adventureworks Select * From fncontactsearch ('ad') 3. comprehensive example: Return table with input parameter use xland Go Create Function DBO. fungetmytable (@ ID as INT) Returns @ allrows table ( Id int not null, Title nvarchar (max) null ) As Begin Insert into @ allrows select ID, title from mytable where id = @ ID Return End Go to execute this example select * From fungetmytable (1)