参数查询
ALTER PROCEDURE [dbo].[PReportSceneOrderDetail]
(
@AgentId varchar(20),
@PayType tinyint,
@StatusOperator char(1),
@Status tinyint,
@TimeType tinyint,
@BeginTime datetime,
@EndTime datetime,
@SceneNameOperator char(1),
@SceneName varchar(60),
@TicketName varchar(60)
)
AS
BEGIN
DECLARE
@sql nvarchar(4000)
SET @sql='SELECT Orders.OrderId, Scene.SceneName,
SceneTicket.TicketName, OrderContact.Mobile, Users.UserName,Users.UserId,
SceneTicket.PayType,Orders.Status,Orders.CreateTime, OrderScene.PlanGoDate, OrderCheckin.CheckinDate,
OrderScene.TicketNum, ISNULL(OrderCheckin.CheckinNum,0) AS CheckinNum,
OrderScene.SalePrice,OrderScene.CostPrice,OrderScene.AgentMaxCommission,
OrderScene.AgentCommissionRate,OrderContact.ContactName,
UserOperator.OperatorLoginId,UserOperator.OperatorName
FROM Orders
INNER JOIN OrderScene ON (OrderScene.OrderId=Orders.OrderId)
LEFT JOIN OrderContact ON (OrderContact.OrderId=Orders.OrderId)
LEFT JOIN Users ON (Users.UserId=OrderScene.AgentId)
INNER JOIN SceneTicket ON (SceneTicket.TicketId=OrderScene.TicketId)
INNER JOIN Scene ON (Scene.SceneId = SceneTicket.SceneId)
LEFT JOIN OrderCheckin ON Orders.OrderId = OrderCheckin.OrderId
LEFT JOIN UserOperator ON (Orders.Saler=UserOperator.OperatorId)
WHERE Orders.OrderType = 0'
IF @AgentId<>'' AND @AgentId IS NOT NULL
BEGIN
SET @sql=@sql+' AND (OrderScene.AgentId LIKE @AgentId OR Users.UserName LIKE @AgentId)'
END
IF @PayType IS NOT NULL
BEGIN
SET @sql=@sql+' AND (SceneTicket.PayType=@PayType)'
END
IF @Status IS NOT NULL
BEGIN
IF @StatusOperator='0'
SET @sql=@sql+' AND (Orders.Status=@Status)'
ELSE IF @StatusOperator='1'
SET @sql=@sql+' AND (Orders.Status<>@Status)'
ELSE IF @StatusOperator='2'
SET @sql=@sql+' AND (Orders.Status<@Status)'
ELSE IF @StatusOperator='3'
SET @sql=@sql+' AND (Orders.Status<=@Status)'
ELSE IF @StatusOperator='4'
SET @sql=@sql+' AND (Orders.Status>@Status)'
ELSE
SET @sql=@sql+' AND (Orders.Status>=@Status)'
END
IF @TimeType=0
BEGIN
SET @sql=@sql+' AND (OrderScene.CreateTime>=@BeginTime AND OrderScene.CreateTime<DATEADD(d,1,@EndTime))'
END
ELSE IF @TimeType=1
BEGIN
SET @sql=@sql+' AND (OrderScene.PlanGoDate>=@BeginTime AND OrderScene.PlanGoDate<DATEADD(d,1,@EndTime))'
END
ELSE
BEGIN
SET @sql=@sql+' AND (OrderCheckin.CheckinDate>=@BeginTime AND OrderCheckin.CheckinDate<DATEADD(d,1,@EndTime))'
END
IF @SceneNameOperator='1'
BEGIN
SET @sql=@sql+' AND (Scene.SceneName LIKE @SceneName)'
END
ELSE IF @SceneNameOperator='2'
BEGIN
SET @sql=@sql+' AND (Scene.SceneName NOT LIKE @SceneName)'
END
IF @TicketName<>'' AND @TicketName IS NOT NULL
BEGIN
SET @sql=@sql+' AND (SceneTicket.TicketName LIKE @TicketName)'
END
SET @AgentId=N'%'+@AgentId+N'%';
SET @SceneName=N'%'+@SceneName+N'%';
SET @TicketName=N'%'+@TicketName+N'%';
EXEC sp_executesql @sql,N'@AgentId varchar(20), @PayType tinyint, @Status tinyint, @BeginTime datetime, @EndTime datetime, @SceneName varchar(60), @TicketName varchar(60)',
@AgentId,@PayType,@Status,@BeginTime,@EndTime,@SceneName,@TicketName
END
--调试方式
/*
DECLARE @AgentId varchar(20),
@PayType tinyint,
@StatusOperator char(1),
@Status tinyint,
@TimeType tinyint,
@BeginTime datetime,
@EndTime datetime,
@SceneNameOperator char(1),
@SceneName varchar(60),
@TicketName varchar(60)
SET @TimeType=0
SET @BeginTime='2010-01-01'
SET @EndTime='2011-01-01'
SET @SceneNameOperator=''
SET @SceneName='白'
BEGIN
DECLARE
@sql nvarchar(4000)
SET @sql=''
SET @AgentId=N'%'+@AgentId+N'%';
SET @SceneName=N'%'+@SceneName+N'%';
SET @TicketName=N'%'+@TicketName+N'%';
EXEC sp_executesql @sql,N'@AgentId varchar(20), @PayType tinyint, @Status tinyint, @BeginTime datetime, @EndTime datetime, @SceneName varchar(60), @TicketName varchar(60)',
@AgentId,@PayType,@Status,@BeginTime,@EndTime,@SceneName,@TicketName
END
*/
==============================
监时表
ALTER PROCEDURE [dbo].[PReportSceneOrderYear1]
(
@TheYear varchar(20)
)
AS
BEGIN
DECLARE @TopNum INT
DECLARE @MinCheckinNumTop DECIMAL
DECLARE @MinCustomerAmountTop DECIMAL
DECLARE @MinProviderAmountTop DECIMAL
DECLARE @MinAgentAmountTop DECIMAL
DECLARE @MinGrossAmountTop DECIMAL
SET @TopNum=10
SELECT
SUM(OrderCheckin.CheckinNum) CheckinNum,
SUM(OrderScene.SalePrice*OrderCheckin.CheckinNum) AS CustomerAmount,
SUM(OrderScene.CostPrice*OrderCheckin.CheckinNum) ProviderAmount,
SUM(OrderScene.AgentMaxCommission*OrderScene.AgentCommissionRate*OrderCheckin.CheckinNum)
AS AgentAmount,
SUM((OrderScene.SalePrice*OrderCheckin.CheckinNum)
- (OrderScene.CostPrice*OrderCheckin.CheckinNum)
- (OrderScene.AgentMaxCommission*OrderScene.AgentCommissionRate*OrderCheckin.CheckinNum))
AS GrossAmount,
MIN(Scene.SceneName) AS SceneName,
Scene.SceneId
INTO #temp
FROM OrderScene
INNER JOIN OrderCheckin ON (OrderCheckin.OrderId = OrderScene.OrderId)
INNER JOIN SceneTicket ON(OrderScene.TicketId=SceneTicket.TicketId)
INNER JOIN Scene ON(SceneTicket.SceneId=Scene.SceneId)
WHERE YEAR(OrderScene.PlanGoDate) = @TheYear
GROUP BY Scene.SceneId
SELECT @MinCheckinNumTop=MIN(CheckinNum) FROM
(SELECT TOP (@TopNum) CheckinNum FROM #temp ORDER BY CheckinNum DESC) AS temp1
SELECT @MinCustomerAmountTop=MIN(CustomerAmount) FROM
(SELECT TOP (@TopNum) CustomerAmount FROM #temp ORDER BY CustomerAmount DESC) AS temp1
SELECT @MinProviderAmountTop=MIN(ProviderAmount) FROM
(SELECT TOP (@TopNum) ProviderAmount FROM #temp ORDER BY ProviderAmount DESC) AS temp1
SELECT @MinAgentAmountTop=MIN(AgentAmount) FROM
(SELECT TOP (@TopNum) AgentAmount FROM #temp ORDER BY AgentAmount DESC) AS temp1
SELECT @MinGrossAmountTop=MIN(GrossAmount) FROM
(SELECT TOP (@TopNum) GrossAmount FROM #temp ORDER BY GrossAmount DESC) AS temp1
SELECT *
,CASE WHEN CheckinNum>=@MinCheckinNumTop THEN SceneName ELSE '其他' END
AS SceneNameByCheckinNum
,CASE WHEN CustomerAmount>=@MinCustomerAmountTop THEN SceneName ELSE '其他' END
AS SceneNameByCustomerAmount
,CASE WHEN ProviderAmount>=@MinProviderAmountTop THEN SceneName ELSE '其他' END
AS SceneNameByProviderAmount
,CASE WHEN AgentAmount>=@MinAgentAmountTop THEN SceneName ELSE '其他' END
AS SceneNameByAgentAmount
,CASE WHEN GrossAmount>=@MinGrossAmountTop THEN SceneName ELSE '其他' END
AS SceneNameByGrossAmount
FROM #temp
DROP TABLE #temp
END
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
序列号
ALTER PROCEDURE [dbo].[PBaseSerialId]
(
@SerialType varchar(20),
@Increment tinyint
)
AS
BEGIN
SET LOCK_TIMEOUT 2000;SET TRAN ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
UPDATE BaseSerialId SET SerialId=CAST(SerialId AS INT)+@Increment WHERE SerialType = @SerialType; IF @@ERROR!=0 ROLLBACK TRAN;
SELECT SerialId FROM BaseSerialId WHERE SerialType = @SerialType; IF @@ERROR!=0 ROLLBACK TRAN;
COMMIT TRAN;
END
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
/*********************************************
读取数据库中所有表的结构信息
*********************************************/
ALTER Procedure [dbo].[GetAllTableInfo] As
begin
DECLARE @strName nvarchar(128), @Tid int,@strOwner varchar(50) --,@au_id varchar(11)
set nocount on
--获取所有的用户表信息
DECLARE All_Tables CURSOR
FOR select convert(nvarchar(128),Name),ID from sysobjects where OBJECTPROPERTY(id, N'IsUserTable')=1 and OBJECTPROPERTY(id, N'IsMSShipped')=0 order by Name
Create Table #ColInfo
(
TName nvarchar(50), --表名称
ColName nvarchar(50), --列名称
ColID smallint, --列ID
REMARK nvarchar(1000), --注释
Type varchar(50), --数据类型
DefaultValue nvarchar(255), --默认值
IsIden nchar(1), --是否标识列
IsAllowNull nchar(1), --是否允许为空
IsPrimaryKey nchar(1) --是否为主键
)
--主键信息
Create Table #PkInfo
(
TABLE_QUALIFIER nvarchar(50),
TABLE_OWNER varchar(50),
TABLE_NAME nvarchar(50),
COLUMN_NAME nvarchar(50),
KEY_SEQ int,
PK_NAME nvarchar(50)
)
--set @strOwner=user_name()
set @strOwner='dbo'
OPEN All_Tables
FETCH NEXT FROM All_Tables into @strName,@Tid
while @@FETCH_STATUS = 0
begin
--插入表及其注释信息
Insert Into #ColInfo
SELECT @StrName,'',0,isnull(convert(nvarchar(1000),Value),''),'','','','',''
FROM (select @strName TName) A left join
::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, NULL, NULL) --表的注释信息
ON 1=1
--插入列信息
insert into #ColInfo
select @strName,C.name CName,c.colid,isnull(convert(nvarchar(1000),R.value),''),
(case when (C.xtype>=34 and C.xtype<=58) or C.xtype in (61,98,99,104,127,189) then T.name
when c.xtype in (59,60,106,108,122) then T.name+'('+convert(varchar,C.xprec)+','+convert(varchar,C.xscale)+')'
else T.name+'('+convert(varchar,c.prec)+'/'+convert(varchar,C.length)+')' end),
isnull(Def.text,''),
(case c.status when 0x80 then '√' else '' end),
case isnullable when 1 then '是' else '否' end,
''
from (systypes T
inner join
( syscolumns C
left join SysComments Def
on C.cdefault=Def.id and 1=Def.colid
) on T.xtype=C.xtype and T.xusertype=C.xusertype
)
left join
::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, N'Column', NULL) R --列的注释信息
on convert(varchar,R.objname)=convert(varchar,C.Name)
where C.id=object_id(@strName)
order by c.colid
insert into #PkInfo EXEC [dbo].[sp_pkeys] @strName,@strOwner, DEFAULT
fetch next from All_Tables into @strName,@Tid
end
--更新主键信息
update #ColInfo
set IsPrimaryKey='*'
from #PkInfo
where #PkInfo.TABLE_NAME=#ColInfo.TName and #PkInfo.COLUMN_NAME=#ColInfo.ColName
Close All_Tables
DEALLOCATE All_Tables
select '☆***☆' ObjectName,'' REMARK,'' Col1,'' Col2,'' Col3,'' Col4,'' Col5,TName TableName,0 flag,colid
from #ColInfo
where ColName=''
union
select '表名：'+TName,REMARK,'','','','','',TName,1 flag,colid
from #ColInfo
where ColName=''
union
select '列名','注释','数据类型','默认值','标识','允许空','主键',TName,2,colid
from #ColInfo
where ColName=''
union
select ColName,REMARK,Type,DefaultValue,IsIden,IsAllowNull,IsPrimaryKey,TName,3,colid
from #ColInfo
where ColName<>''
order by 8,9,10
drop table #PkInfo
drop table #ColInfo
end
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
/*********************************************
读取数据库中指定表的结构信息
*********************************************/
ALTER Procedure [dbo].[GetTableInfo_A] @tableName nvarchar(128) As
begin
DECLARE @strName nvarchar(128), @Tid int,@strOwner varchar(50) --,@au_id varchar(11)
set nocount on
DECLARE All_Tables CURSOR
FOR select convert(nvarchar(128),Name),ID from sysobjects where OBJECTPROPERTY(id, N'IsUserTable')=1 and OBJECTPROPERTY(id, N'IsMSShipped')=0 and object_name(id)=@tableName order by Name
Create Table #ColInfo
(
TName nvarchar(50), --表名称
ColName nvarchar(50), --列名称
ColID smallint, --列ID
REMARK nvarchar(1000), --注释
Type varchar(50), --数据类型
DefaultValue nvarchar(255), --默认值
IsIden nchar(1), --是否标识列
IsAllowNull nchar(2), --是否允许为空
IsPrimaryKey nchar(1) --是否为主键
)
--主键信息
Create Table #PkInfo
(
TABLE_QUALIFIER nvarchar(50),
TABLE_OWNER varchar(50),
TABLE_NAME nvarchar(50),
COLUMN_NAME nvarchar(50),
KEY_SEQ int,
PK_NAME nvarchar(50)
)
set @strOwner=user_name()
OPEN All_Tables
FETCH NEXT FROM All_Tables into @strName,@Tid
while @@FETCH_STATUS = 0
begin
--插入表及其注释信息
Insert Into #ColInfo
SELECT @StrName,'',0,isnull(convert(nvarchar(1000),Value),''),'','','','',''
FROM (select @strName TName) A left join
::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, NULL, NULL) --表的注释信息
ON 1=1
--插入列信息
insert into #ColInfo
select @strName,C.name CName,c.colid,isnull(convert(nvarchar(1000),R.value),''),
(case when (C.xtype>=34 and C.xtype<=58) or C.xtype in (61,98,99,104,127,189) then T.name
when c.xtype in (59,60,106,108,122) then T.name+'('+convert(varchar,C.xprec)+','+convert(varchar,C.xscale)+')'
else T.name+'('+convert(varchar,c.prec) -- +'/'+convert(varchar,C.length)
+')' end),
isnull(Def.text,''),
(case c.status when 0x80 then '√' else '' end),
case isnullable when 1 then '空' else '非空' end,
''
from (systypes T
inner join
( syscolumns C
left join SysComments Def
on C.cdefault=Def.id and 1=Def.colid
) on T.xtype=C.xtype and T.xusertype=C.xusertype
)
left join
::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, N'Column', NULL) R --列的注释信息
on convert(varchar,R.objname)=convert(varchar,C.Name)
where C.id=object_id(@strName)
order by c.colid
insert into #PkInfo EXEC [dbo].[sp_pkeys] @strName,@strOwner, DEFAULT
fetch next from All_Tables into @strName,@Tid
end
--更新主键信息
update #ColInfo
set IsPrimaryKey='*'
from #PkInfo
where #PkInfo.TABLE_NAME=#ColInfo.TName and #PkInfo.COLUMN_NAME=#ColInfo.ColName
Close All_Tables
DEALLOCATE All_Tables
/*
select '☆***☆' ObjectName,'' REMARK,'' Col1,'' Col2,'' Col3,'' Col4,'' Col5,TName TableName,0 flag,colid
from #ColInfo
where ColName=''
union
*/
select TName,'','',REMARK,colid --,'','','','','',TName,1 flag,colid
from #ColInfo
where ColName=''
union
/*
select '列名','注释','数据类型','默认值','标识','允许空','主键',TName,2,colid
from #ColInfo
where ColName=''
union
*/
select ColName,Type,IsAllowNull,REMARK,colid --,DefaultValue,IsIden,IsPrimaryKey,TName,3
from #ColInfo
where ColName<>''
order by colid
drop table #PkInfo
drop table #ColInfo
end
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
curd,
SET NOCOUNT ON(R)
SET NOCOUNT OFF(cud)