Sql Server sp 例子

 

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Daniel Qin>
-- Create date: <2008-11-26>
-- Description:    <Example of SQL Server Store Procedure>
-- =============================================
create PROCEDURE Test 
    -- Add the parameters for the stored procedure here
    @param1 nvarchar(100),
    @param2 int
    -- @parma3 int output -- 定义返回参数，类似C#的 out语法
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    -- 自定义变量:
    declare @nParam1 nvarchar(100);
    declare @nParam2 int;
    
    -- 给变量赋值：
    set @nParam1 = 'Hello World!';
    set @nParam2 = 1;
    
    -- 用select赋值
    select @nParam1 = 'From Select' ;
    -- 从表中获取一个值
    --select @nParam1 = Urls from Users;
    
    -- 逻辑判读, begin end 等同于 {}
    if @param2 = 1 
        set @nParam1 = 'Parma2 = 1';
    else
    begin
        set @nParam1 = 'Parma2 <> 1';
    end;
    
    -- 库函数例子 每种数据库都有很多自己的库函数，这个要慢慢查稳定.
    if LTRIM(RTRIM(@param1)) = ''
        set @nParam2 = 0;
    
    -- select getdate();
    
    /* 临时表 ,常常用来做中转，比如某些业务逻辑情况下，我们用单纯或者复杂的select语句
     也无法完成数据处理，则可以先处理部分数据，存储到临时表，之后再统一处理。
     临时表基本上和普通表没有区别，只是定义时多一个#，另外需要注意临时表的生存周期。*/
    
    -- 定义之前，先删除
    IF OBJECT_ID (N'#TestTempTable',N'U') IS NOT NULL
        DROP TABLE #TestTempTable ;
     
    -- 创建
    create table #TestTempTable(
    
        name1 nvarchar(100) null,
        name2 nvarchar(200) null
    );
    
    -- 游标操作，关键之：declare,open,fetch,while,close,deallocate
    -- 定义变量
    declare @name1 nvarchar(100);
    declare @name2 nvarchar(100);
    
    -- 定义游标
    declare Test_cursor cursor for select FirstName,LastName from Users; 
    
    open Test_cursor ;
    fetch next from Test_cursor into  @name1,@name2 ;
    
    -- @@FETCH_STATUS 这个变量是sql server内部变量
    WHILE  @@FETCH_STATUS = 0
    begin
        insert into #TestTempTable values (@name1,@name2);
        
        -- fetch again
        fetch next from Test_cursor 
        into  @name1,@name2 ;
    end;

    close Test_cursor;
    deallocate Test_cursor;
     
    -- 看结果
    select @nParam1 as nParam1,@nParam2 as nParam2;
    select * from #TestTempTable;
    
    
END
GO
 

 

Create PROCEDURE SPExample
	-- Add the parameters for the stored procedure here
	@PortalId nvarchar(1),
	@StartFromLevel nvarchar(1),
	@MaxLevel nvarchar(1),
	@ShowVisible nvarchar(1),
	@ShowAdminOnly nvarchar(1),
	@NoAdmin nvarchar(1),
	@ExcludedTabIds nvarchar(1000),
	@RoleIds nvarchar(1000),
	@IsSuperUser nvarchar(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @sql nvarchar(4000);
    set @sql = N'
	select * from Tabs 
	where IsDeleted = ''0'' 
	and (PortalID = '+@PortalId+') 
	and Level between '+@StartFromLevel+' and '+@MaxLevel+' ';
	
	if @ShowVisible = '0' 
		set @sql = @sql + N' and IsVisible = 1 ';
	
	if @ShowAdminOnly = '1'
		set @sql = @sql + N' and TabPath like ''//Admin%'' ';
	
	if @NoAdmin = '1' 
		set @sql = @sql + N' and TabPath not like ''//Admin%'' ';
	
	if LTRIM(RTRIM(@ExcludedTabIds)) <> '' 
		set @sql = @sql + N' and TabID not in ( '+@ExcludedTabIds+' ) ';
		
	if  LTRIM(RTRIM(@RoleIds)) <> '' 
		set @sql = @sql + N' and TabID in (
		select distinct a.TabID from TabPermission a
		left join Roles b on (a.RoleID = b.RoleID)
		left join Permission c on (a.PermissionID = c.PermissionID )
		where c.PermissionKey=''VIEW''
		and b.RoleID in ('+@RoleIds+')
		)';
	

	if @IsSuperUser = '1'
		set @sql = @sql + N' union select * from Tabs where PortalID is null';
	
	
	set @sql = @sql + N' order by PortalID desc,TabOrder asc';
	
	-- for debug
	--select @sql;
	
	exec sp_executesql @sql;
END
GO

over