变量
局部变量
1.声明局部变量
@where NVARCHAR(4000),
@whereFORhruserinfo NVARCHAR(4000) = '',
@order NVARCHAR(200) = '',
@pagesize INT,--每页有几条数据
@page INT,--当前页数
@totalRecorder INT OUTPUT
2.为变量赋值
SET @变量名 =值 --set用于普通的赋值
SELECT @变量名 = 值 --用于从表中查询数据并赋值，，可以一次给多个变量赋值 SET @name=‘张三’
SET @id = 1
SELECT @name = sName FROM student WHERE sId=@id
3.输出变量的值
SELECT 以表格的方式输出，可以同时输出多个变量；而PRINT 则是以文本的方式输出，一次只能输出一个变量的值。
SELECT @name,@id
PRINT @name
PRINT @id
print @name,@id --错误！！
全局变量
用全局变量
select @@LANGUAGE as '当前使用语言'
select @@SERVERNAME as '当前服务器名称'
select @@TRANCOUNT as '当前连接打开的事务数'
select @@MAX_CONNECTIONS as '可以同时连接的最大数目'
select @@VERSION as '当前服务器版本'
select @@ERROR as '最后一个T-SQL错误的错误号'
IF ELSE
条件选择语法
IF(条件表达式)
BEGIN --相当于C#里的{
语句1 ……
END --相当于C#里的}
ELSE
BEGIN
语句1
……
END
WHILE
循环语句语法
WHILE(条件表达式)
BEGIN --相当于C#里的{
语句
……
continue --退出本次循环
BREAK --退出整个循环
END --相当于C#里的}
计算1-100之间所有奇数的和
declare @index int = 1
declare @sum int = 0
while(@index <= 100)
begin
if(@index%2!=0)
begin
set @sum=@sum+@index
end
set @index=@index+1
end
存储过程
新建存储过程
Create PROCEDURE [dbo].[p_test]
@type int
AS
BEGIN
declare @count int
declare @result varchar(50)
if(@type =1)
begin
select @count = (select count(*) from dbo.UserGrowthDetail)
set @result='求第一个总数'
print @result
return @count
end
else if(@type =2)
begin
select @count = (select count(*) from dbo.UserGrowthValue)
set @result='求第二个总数'
print @result
return @count
end
END
执行存储过程
GO DECLARE @return_value int EXEC @return_value = [dbo].[p_test]
@type = 1 SELECT 'Return Value' = @return_value GO
存储过程通用分页
Create PROCEDURE [dbo].[WF_Pager]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 返回字段列表要足够大
@orderName varchar(255), -- 字段名用于排序
@OrderType bit = 0,--0 升序 1降序
@PageSize int = 10, -- 页尺寸
@PageIndex int, -- 页码
@strWhere varchar(1000) = '' ,-- 查询条件(注意:不要加where)
@RecordCount int output --总数
AS
BEGIN
DECLARE @sql nvarchar(4000)
declare @sortStr varchar(50)--排序方式
if @OrderType!=1 --升序
set @sortStr=' asc '
else --降序
set @sortStr=' desc '
if @strWhere !=''
set @strWhere=' where '+@strWhere
SELECT @sql =N'
DECLARE @temp int;
DECLARE @minRecord int;
DECLARE @PageSize int;
DECLARE @PageIndex int;
SET @temp = 0
SET @minRecord = 0
SET @PageSize ='+ cast(@PageSize as varchar(10)) +'
SET @PageIndex = '+ cast(@PageIndex as varchar(10)) +'
SELECT @RecordCount = COUNT(0) FROM '+@tblName+ @strWhere +'
-- 限制每页记录数，默认每页10项记录 --
IF(@PageSize < 1)
SET @PageSize = 10
-- 限制页号 BEGIN --
IF(@PageIndex < 1)
SET @PageIndex = 1
DECLARE @MaxPageIndex INT -- 最大页数。
SET @MaxPageIndex = @RecordCount / @PageSize --(完整数量的页的数量)
IF(@RecordCount % @PageSize > 0)
BEGIN
SET @MaxPageIndex = @MaxPageIndex + 1
END
IF (@MaxPageIndex = 0)
BEGIN
SET @MaxPageIndex=1
END
IF(@PageIndex > @MaxPageIndex)
SET @PageIndex = @MaxPageIndex
SET @minRecord = (@PageIndex - 1) * @PageSize;
SET @temp = @PageIndex * @PageSize;
SELECT * FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY '+@orderName+@sortStr+') AS Item,'+@strGetFields+'
FROM '+@tblName+ @strWhere +'
) AS T
WHERE T.Item >= @minRecord + 1
AND T.Item <= @temp '
--执行
EXEC SP_EXECUTESQL @sql, N'@RecordCount varchar(10) output',@RecordCount output
SELECT @RecordCount
END
存储过程综合示例
USE [SCST]
GO
/****** Object: StoredProcedure [dbo].[p_test] Script Date: 2018/8/23 19:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO ALTER PROCEDURE [dbo].[p_test]
@strWhere varchar(1000),
@PageSize int = 10, -- 页尺寸
@PageIndex int = 0, -- 页码
@RecordCount int output --输出参数 总数
AS
BEGIN
DECLARE @Result nvarchar(50)
IF(@strWhere=1)
BEGIN
--CET公共表表达式
with tbStudent as
(
select * from Student where Ssex = '男'
)
SELECT @Result = (select count(*) from tbStudent) --输出参数赋值
SELECT @RecordCount=1
END
ELSE
BEGIN SELECT dense_rank() OVER (ORDER BY s.Sage DESC) AS Rank,ROW_NUMBER() OVER(ORDER BY s.S# ASC) as RowNumber,s.S#,s.Sname,s.Ssex,ISNULL(s.Ssex,'空值') as Ssex,
Sage=(case Sage
when 17 then '小'
when 18 then '正好'
when 19 then '大'
end
)
from Student s SELECT CAST('' AS int) SELECT CONVERT(VARCHAR(19),GETDATE()) --exists --while
declare @index int = 1
declare @sum int = 0
while(@index <= 100)
begin
if(@index%2!=0)
begin
set @sum=@sum+@index
end
set @index=@index+1
end
print @sum SET @Result = ''
SELECT @RecordCount=2
END
print @Result
return @Result
END
USE [Points2019]
GO /****** Object: StoredProcedure [dbo].[p_Page] Script Date: 2019/8/6 13:28:52 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[p_Page]
-- Add the parameters for the stored procedure here
@SQL NVARCHAR(4000),
@Order NVARCHAR(200),
@CurPage INT,
@PageRows INT,
@TotalRecorder INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
DECLARE @ExceSQL NVARCHAR(4000) --获得记录数
SET @ExceSQL = 'select @TotalRecorder=count(*) from ' +
substring(@SQL, charindex('YanFaFrom', @SQL) + 9, len(@SQL) - charindex('YanFaFrom', @SQL) - 8) EXECUTE sp_executesql @ExceSQL, N'@TotalRecorder int output', @TotalRecorder OUTPUT --设置开始行号
DECLARE @start_row_num AS INT
SET @start_row_num = (@CurPage - 1) * @PageRows + 1 --设置标识语句
DECLARE @RowNumber NVARCHAR(300)
IF (isnull(@Order, '') = '')
BEGIN
SET @Order = '(select 0)'
END
SET @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from ' SET @SQL = Replace(@SQL, 'YanFaFrom', @RowNumber) --设置查询语句
SET @ExceSQL = 'WITH tmp AS (' + @SQL + ')
select * from tmp where RowNumber between ' + Convert(NVARCHAR, @start_row_num)
+ ' And ' + Convert(NVARCHAR, @start_row_num + @PageRows - 1) + ' ORDER BY RowNumber asc' EXECUTE (@ExceSQL)
PRINT @ExceSQL
END GO
[dbo].[p_Page]
存储过程和函数的区别
存储过程是第一次编译之后就会被存储的下来的预编译对象，之后无论何时调用它都会去执行已经编译好的代码。
而函数每次执行都需要编译一次。总结下来有下面几个区别：
基本不同：
函数必须有一个返回值而存储过程则不是必须的（存储过程可以返回0个到n个值）；
函数只能有输入参数而存储过程可以同时又输入和输出参数；
函数至少有一个参数而存储过程可能需要n个参数；
函数可以被存储过程调用而存储过程不可以被函数调用；
高级不同：
存储过程允许SELECT还有DML（INSERT/UPDATE/DELETE）语句而函数只能使用SELECT语句；
存储过程不可以使用在WHERE/HAVING/SELECT语句中而函数可以；
返回表变量的函数可以和其他表进行JOIN操作；
内联函数可以看做一个带参数的VIEW去和其他表进行JOIN操作；
存储过程可以使用try-catch块进行异常处理二函数不可以；
存储过程中可以使用事务而函数不可以；
总结：
函数有且只有一个输入参数和一个返回值，而存储过程没有这个限制；
返回表变量的函数可以当做VIEW或者临时表用在WHERE/HAVING/SELECT/JOIN语句中而存储过程不可以；
存储过程中可以使用try-catch块和事务，而函数中不可以
实战
1.可以将耗时的查询先查出来放到临时表中，给需要创建索引的字段创建索引。然后用该临时表和主表做join查询。
SET @s = 'select * into # from v_hruserinfo WHERE 1=1 ' + @whereFORhruserinfo+';'
select * into #Table from v_Table
CREATE UNIQUE INDEX TMPUNIQUEHRU ON #Table(XXField)
CREATE INDEX TMPHRU ON #Table (XXField)
2.使用公用表表达式可以使存储过程更加优雅
WITH CTE AS
(
)
资料
https://www.cnblogs.com/skybreak/p/3642593.html