if not exists (select * from sys.types where is_user_defined=1 and name='UDT_Area_2')
begin
CREATE TYPE [dbo].[UDT_Area_2] FROM [decimal](16,4) NULL
end
--删除数据库表的列
if exists( select * from syscolumns where('MC_Unit') and name='ApportionArea')
begin
ALTER TABLE MC_Unit DROP COLUMN ApportionArea
end
--创建数据库表的列
if not exists( select * from syscolumns where('MC_Unit') and name='ApportionArea')
begin
alter table MC_Unit add ApportionArea AS ([InteriorArea]-[BuildUpArea]) PERSISTED
end
--修改数据库的类型
if exists( select * from syscolumns where('MC_Unit') and name='UsableArea')
begin
ALTER TABLE MC_Unit ALTER column UsableArea UDT_Area_2 null
end
--修改数据视图
go
alter VIEW view_RT_BillReceipt
AS
---查询语句
go
--创建数据库视图
if exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where TABLE_NAME='View_RTUnit_RentalFeeWarning')
begin
drop view View_RTUnit_RentalFeeWarning--删除数据库视图
end
go
create VIEW View_RTUnit_RentalFeeWarning
AS
--查询数据库视图
go
--创建存储过程
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'MC_TotalPaymentStatements' AND type = 'P')
DROP PROCEDURE MC_TotalPaymentStatements --删除存储过程
GO
create procedure MC_TotalPaymentStatements
@QueryCriteria1 varchar(600),--定义传进来的函数
@PropertyID1 varchar(60)
as
declare @QueryCriteria varchar(600)=isnull(@QueryCriteria1,'')--定义存储过程的函数并给函数赋值
declare @PropertyID varchar(60)=isnull(@PropertyID1,'')
if(@PropertyID!='')
begin
exec ('select PayMethodName,DocumentDate1 ,sum(AllocAmt)as AllocAmt from View_Mc_PayDetail where '+@QueryCriteria+' and PropertyID='+@PropertyID+' group by PayMethodName ,DocumentDate1 ')
end
else
begin
exec ('select PayMethodName,DocumentDate1 ,sum(AllocAmt)as AllocAmt from View_Mc_PayDetail where '+@QueryCriteria+' group by PayMethodName ,DocumentDate1 ')
end
go
--自定义函数
--合并服务内容函数
if exists(select name from sysobjects where name='MergeRequestDesc' and xtype='FN')--判断函数是否存在
begin
drop function MergeRequestDesc--删除函数
end
go
Create function MergeRequestDesc(@string NVARCHAR(MAX))--合并服务内容函数
RETURNS NVARCHAR(MAX) --返回值
AS
BEGIN
declare @temp NVARCHAR(MAX) -- 定义变量
set @temp='' --设置值
select @temp=case when len(@temp)=0 then @temp+RequestDesc else @temp+','+RequestDesc end from View_CS_RequestDetails where RequestID=@string and recordstatus='Active'
return @temp
end
go