Sql Server sp 例子
Code-- ================================================
-- 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
