SQL存储过程+视图
来源:互联网 发布:ubuntu没有wifi列表 编辑:程序博客网 时间:2024/05/17 16:55
---存储过程举例
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'***'))
DROP PROCEDURE ***GO
create procedure ***(@tablename as nvarchar(256),@ret int output)
as
declare @sSql as nvarchar(4000)
set @sSql = N'select top 1 * from ' + @tablename
exec(@sSql)
if @@rowcount = 0
begin
set @ret=2
return
end
select convert(int,0) as id,convert(money,0) as ufts,convert(money,0) as corufts into #arr where 1=0
set @sSql=N'insert into #arr select arr.**,convert(money,arr.crm_timestamp) as ufts ,arrs.corufts
from (select a.***,t.ufts as corufts from
'+@tablename+' t left join *** a with (updlock) on a.***=t.autoid group by a.***,t.ufts ) arrs
left join *** arr with (updlock)
on arr.***=arrs.*** '
exec(@sSql)
if exists(select top 1 id from #arr where (ufts<>corufts or id is null) and corufts is not null )
begin
set @ret=1 --
return
end
set @sSql=N'select top 1 a.partapply_d_id from
'+@tablename+' t inner join *** a with (updlock) on a.***=t.***
where (isnull(t.***,0) + isnull(a.***,0)>isnull(a.***,0)) '
exec(@sSql)
if @@rowcount > 0
begin
set @ret=3 --数据校验
return
end
update *** set ***=*** from *** inner join #arr on ***.*** = #arr.id
set @sSql=N'update a set a.***=isnull(a.***,0)+isnull(t.***,0), a.***=isnull(a.***,0)+isnull(t.***,0)
from *** a join '+@tablename+' t on a.***=t.autoid'
exec(@sSql)
set @ret=0
return
go
---视图举例
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[***'))
DROP ***
GO
CREATE view *** as
select pa.*** as ***, rd.ID as ID
from *** rds WITH(NOLOCK)
inner join *** pad on rds.***=pad.***
inner join *** pa on rds.***=pa.***
inner join *** rd on rd.***=N'***'
go
0 0
- SQL存储过程+视图
- sql 视图与存储过程
- SQL存储过程和视图
- SQL 存储过程动态创建视图
- SQL 利用存储过程刷新所有视图
- sql之视图,存储过程以及事务
- week3_day5_T-SQL&事务、视图、索引、存储过程
- SQL Server视图、存储过程、触发器
- SQL 查询存储过程、视图内容
- MySQL-SQL之视图、存储过程
- 获取sql所有存储过程脚本,获取sql表名,视图名,存储过程名等
- 查看SQL SERVER 加密存储过程,函数,触发器,视图
- 查看SQL SERVER 加密存储过程,函数,触发器,视图
- 查看SQL SERVER 加密存储过程,函数,触发器,视图
- SQL Server2000视图、存储过程、触发器学习笔记
- 查看SQL SERVER 加密存储过程,函数,触发器,视图
- ASP调用SQL Server视图和存储过程
- SQL 存储过程和视图有什么关系?
- CentOS6.5配置DNS
- 数据结构之不相交集类
- leach和leach-c协议仿真
- struts2 路径找不到有以下几个办法
- Maximum number, GCD condition (CodeChef)
- SQL存储过程+视图
- day_3 用3个sevrlet文件完成用户登录模式小项目
- 梦想,不是浮躁而是沉淀和积累!
- android SeekBar
- Spring 学习笔记--Spring 整合JDBC
- 2014-03-30工作日志:error:sprintf其实不具有连接字符串的功能,每次调用它时,都是从头开始保存的
- 日语五十音快速记忆
- Week 1 Assignment - Wordnet - Princeton - Algorithms Part II
- dlut1217-dp水题