SQL Server进阶（11）存储过程

变量

局部变量  

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('12' 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 = '2'
            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

存储过程和函数的区别

存储过程是第一次编译之后就会被存储的下来的预编译对象，之后无论何时调用它都会去执行已经编译好的代码。

而函数每次执行都需要编译一次。总结下来有下面几个区别：

基本不同：

　　函数必须有一个返回值而存储过程则不是必须的（存储过程可以返回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