談談sqlserver自定義函數(shù)與存儲過程的區(qū)別

1、selectdeleteSqlServer1男男男1create procedure sp_query_bankMoneyasselect * from bankMoneygoexec sp_query_bankMoney*T-Sql2Create proc insert_bank param1 char(10),param2 varchar(20),param3 varchar(20),param4 int,param5 int outputwith encryption -加密asinsert into bankMoney (id,userID,sex,Money)Values(param

2、1,param2,param3, param4)select param5=sum(Money) from bankMoney where userID=Zhangsango在SQL Server查詢分析器中執(zhí)該存儲過程的法是：declare total_price intexec insert_bank 004,Zhangsan,男,100,total_price outputprint 總余額為+convert(varchar,total_price)go3:3 USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = au_in

3、fo_all AND type = P)DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor taON a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idGOau_info_all存儲過程可以通過以下法執(zhí)：

4、EXECUTE au_info_all- OrEXEC au_info_all如果該過程是批處理中的第條語句，則可使：au_info_all4CREATE PROCEDURE au_infolastname varchar(40),firstname varchar(20)ASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor taON a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN

5、 publishers pON t.pub_id = p.pub_idWHERE au_fname = firstnameAND au_lname = lastnameGOau_info存儲過程可以通過以下法執(zhí)：EXECUTE au_info Dull, Ann- OrEXECUTE au_info lastname = Dull, firstname = Ann- OrEXECUTE au_info firstname = Ann, lastname = Dull- OrEXEC au_info Dull, Ann- OrEXEC au_info lastname = Dull, first

6、name = Ann- OrEXEC au_info firstname = Ann, lastname = Dull如果該過程是批處理中的第條語句，則可使：au_info Dull, Ann- Orau_info lastname = Dull, firstname = Ann- Orau_info firstname = Ann, lastname = Dull5CREATE PROCEDURE au_info2lastname varchar(30) = D%,firstname varchar(18) = %ASSELECT au_lname, au_fname, title, pub

7、_nameFROM authors a INNER JOIN titleauthor taON a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idWHERE au_fname LIKE firstnameAND au_lname LIKE lastnameGOau_info2存儲過程可以多種組合執(zhí)。下只列出了部分組合：EXECUTE au_info2- OrEXECUTE au_info2 Wh%- OrEXECUTE au

8、_info2 firstname = A%- OrEXECUTE au_info2 CKarsOEn- OrEXECUTE au_info2 Hunter, Sheryl- OrEXECUTE au_info2 H%, S%= proc26.下是ifelse的存儲過程：if exists (select 1 from sysobjects where name = Student and type =u )drop table Studentgoif exists (select 1 from sysobjects where name = spUpdateStudent and type =

9、p )drop proc spUpdateStudentgocreate table Student(fName nvarchar (10),fAgesmallint ,fDiqu varchar (50),fTel int)goinsert into Student values (X.X.Y , 28, Tesing , 888888)gocreate proc spUpdateStudent(fCase int ,fName nvarchar (10),fAge smallint ,fDiqu varchar (50),fTelint)asupdate Studentset fAge =

10、 fAge,- 傳 1,2,3 都要更新 fAge 不需要 casefDiqu = (case when fCase = 2 or fCase = 3 then fDiqu else fDiqu end ),fTel = (case when fCase = 3 then fTelelse fTel end )where fName = fNameselect * from Studentgo-只改 Ageexec spUpdateStudentfCase = 1,fName = NX.X.Y ,fAge = 80,fDiqu = NUpdate,fTel= 1010101-改 Age 和 Diquexec spUpdateSt