SQLServer預存程序

標籤：

--無參數預存程序建立和調用
--建立預存程序usp_testusergoif exists(select * from sysobjects where name=‘usp_test‘)drop proc usp_testgocreate proc usp_testasselect * from testgo--執行預存程序exec usp_testgo

--有參預存程序建立和調用--建立預存程序user testif exists (select * from sysobjects where name=‘usp_test‘)drop proc usp_testgocreate proc usp_test@name varchar(32),@age int = nullas if @age is nullbegin select * from test where name = @nameendelse begin select * from test where name = @name and age = @ageendgo--調用預存程序exec usp_test @name=‘username‘go    

　　

--有參數、輸出預存程序和調用--統計某年齡的人數--建立預存程序use testif exists (select * from sysobjects where name=‘usp_test‘)drop proc usp_testgocreate proc usp_test@age varchar(32),@count int outputas set @count = (select count(1) from test where age = @age)go--執行預存程序declare @age varchar(32)declare @count int set @age = ‘26‘exec usp_test @age ,@count outputprint @countgo 

　　

SQLServer預存程序