用sql拼接来完毕可变参数的机能 ,壹 、创造存款和储蓄进度

在sql server中国建工业总会公司存款和储蓄进度,假如急需参数是五个可变集合的拍卖

壹 、创设存款和储蓄进度

原存款和储蓄进程,@objectIds 为可变参数,比如 110,98,99

if Exists(select name from sysobjects where NAME = ‘sp1LoginUser’ and
type=’P’)
drop procedure sp1LoginUser
GO
CREATE PROCEDURE [dbo].[sp1LoginUser]
— Add the parameters for the stored procedure here
@username NVARCHAR(50)
AS
BEGIN
DECLARE @identityCount INT
SELECT * FROM dbo.LoginUser where IsDelete=0
AND UserName=@username
SELECT TOP 1 @identityCount= ID FROM dbo.LoginUser ORDER BY ID DESC
select @identityCount
END
GO

图片 1

二 、执行存款和储蓄进程

ALTER PROC
[dbo].[Proc_totalScore]

EXEC sp1LoginUser ‘admin’

@categoryKey int,

 

@objectIds VARCHAR(MAX)

三 、创立函数

AS

1、
ALTER FUNCTION [dbo].[fn1GetRoleNane](
@userid INT
)
returns varchar(500)
AS
begin
DECLARE @tmp VARCHAR(500)
SELECT @tmp=isnull(@tmp+’,’,”)+ltrim(r.roleName) FROM UserAndRole ar,
Role r WHERE r.RoleID=ar.roleID AND ar.userid=@userid
RETURN ISNULL(@tmp,”)
END

BEGIN

 

     SELECT c.Name,AVG(e.Score) Score
FROM dbo.Sys_com_comment_main m

2、

     INNER JOIN
dbo.sys_com_coment_extend e ON e.commentId=m.ID

select * from Split(@strwhere , ‘,’))
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[Split]
(
@c VARCHAR(MAX) ,
@split VARCHAR(50)
)

     INNER JOIN dbo.sys_com_category c
ON e.NameKey=c.NameKey

RETURNS @t TABLE ( col VARCHAR(50) )
AS
BEGIN
WHILE ( CHARINDEX(@split, @c) <> 0 )
BEGIN
INSERT @t( col )
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) – 1) )
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), ”)
END
INSERT @t( col ) VALUES ( @c )
RETURN
END
GO

     WHERE m.categoryKey=@categoryKey
AND m.dataStatus<>99 AND m.IsCheck=0 AND m.objectId
IN(@objectIds)

④ 、 sqlserver 日期字段类型转字符串
(Select Convert(Varchar(10),FeeTime,120) Username,
把日子类型字段转为钦定长度的字符串

     GROUP BY c.Name

伍 、创制分页存款和储蓄进度
Create PROCEDURE usp_PagingLarge
@TableNames VA揽胜CHA中华V(200),–表名,能够是多少个表,但无法用小名
@PrimaryKey VAENCORECHAEnclave(100),–主键,能够为空,但@Order为空时该值不能够为空
@Fields
VA途胜CHALX570(200),–要取出的字段,可以是多少个表的字段,能够为空,为空表示select*
@PageSize INT,–每页记录数
@CurrentPage INT,–当前页,表示第页
@Filter VA中华VCHAKuga(200)=”,–条件,能够为空,不用填where
@Group VALacrosseCHA奥德赛(200)=”,–分组依照,可以为空,不用填groupby
@Order
VA猎豹CS6CHAPAJERO(200)=”–排序,能够为空,为空暗中同意按主键升序排列,不用填orderby
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
IF @Fields=”
SET @Fields=’*’
IF @Filter=”
SET @Filter=’Where1=1′
ELSE
SET @Filter=’Where’+@Filter
IF @Group<>”
SET @Group=’GROUPBY’+@Group

END

IF @Order<>”
BEGIN
DECLARE @pos1 INT,@pos2 INT
SET @Order=REPLACE(REPLACE(@Order,’asc’,’ASC’),’desc’,’DESC’)
IF CHARINDEX(‘DESC’,@Order)>0
IF CHARINDEX(‘ASC’,@Order)>0
BEGIN
IF CHARINDEX(‘DESC’,@Order)<CHARINDEX(‘ASC’,@Order)
SET @Operator='<=’
ELSE
SET @Operator=’>=’
END
ELSE
SET @Operator='<=’
ELSE
SET @Operator=’>=’
SET
@SortColumn=REPLACE(REPLACE(REPLACE(@Order,’ASC’,”),’DESC’,”),”,”)
SET @pos1=CHARINDEX(‘,’,@SortColumn)
IF @pos1>0
SET @SortColumn=SUBSTRING(@SortColumn,1,@pos1-1)
SET @pos2=CHARINDEX(‘.’,@SortColumn)
IF @pos2>0
BEGIN
SET @SortTable=SUBSTRING(@SortColumn,1,@pos2-1)
IF @pos1>0
SET @SortName=SUBSTRING(@SortColumn,@pos2+1,@pos1-@pos2-1)
ELSE
SET @SortName=SUBSTRING(@SortColumn,@pos2+1,LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable=@TableNames
SET @SortName=@SortColumn
END
END
ELSE
BEGIN
SET @SortColumn=@PrimaryKey
SET @SortTable=@TableNames
SET @SortName=@SortColumn
SET @Order=@SortColumn
SET @Operator=’>=’
END

调用1:EXEC [Proc_totalScore]
99902,’110′

DECLARE @type varchar(50)
DECLARE @prec int
Select @type=t.name,@prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
Where o.name=@SortTable AND c.name=@SortName
IF CHARINDEX(‘char’,@type)>0
SET @type=@type+'(‘+CAST(@prec AS varchar)+’)’

图片 2

DECLARE @TopRows INT
SET @TopRows=@PageSize*@CurrentPage+1
print @TopRows
print @Operator
EXEC(‘
DECLARE @SortColumnBegin’+@type+’
SET ROWCOUNT’+@TopRows+’
Select
@SortColumnBegin=’+@SortColumn+’FROM’+@TableNames+”+@Filter+”+@Group+’or
DERBY’+@Order+’
SET ROWCOUNT’+@PageSize+’
Select’+@Fields+’FROM’+@TableNames+”+@Filter+’AND’+@SortColumn+”+@Operator+’@SortColumnBegin’+@Group+’or
DERBY’+@Order+’
‘)
END

 

GO

调用2:EXEC [Proc_totalScore]
99902,’110,97,87′

 

图片 3

int.TryParse(areaID.ToString(), out areaID);
六、–case when的用法
select (case cardstatus
when 0 then ‘卡发行’
when 1 then ‘卡延期’
when 2 then ‘挂失’
when 3 then ‘解挂’
when 4 then ‘补发’
when 5 then ‘退款’
when 6 then ‘销户’
else ‘其它’
end ) as ‘卡片类型’,count(*) as ‘数量’
from CCardOperation
group by cardstatus

 

–sum case when 用法
select
sum(case cardstatus when 0 then 1 else 0 end) as ‘卡发行’ ,
sum(case cardstatus when 1 then 1 else 0 end ) as ‘卡延期’,
sum(case cardstatus when 2 then 1 else 0 end) as ‘挂失’,
sum(case cardstatus when 3 then 1 else 0 end) as ‘解挂’,
sum(case cardstatus when 4 then 1 else 0 end) as ‘补发’,
sum(case cardstatus when 5 then 1 else 0 end) as ‘退款’,
sum(case cardstatus when 6 then 1 else 0 end) as ‘销户’
from CCardOperation

 

柒 、拼接字段

在sql server中国建工业总会集团存款和储蓄进度,假诺必要参数是二个可变集合怎么处理?

select(select count(1) from CCardOperation where cardstatus=0) as
‘卡发行’,
(select count(1) from CCardOperation where cardstatus=1) as ‘卡延期’,
(select count(1) from CCardOperation where cardstatus=2) as ‘卡挂失’

上网搜了一晃多数都以酱油贴,有网上朋友说用xml的章程,具体的代码也没瞧见

八 、创设一个游标

 

update [Vw_Sale_PersonCard] a set a.deptid=b.deptid where exists
(
(select b.deptid from [Sale_SaleRecord] b on a.yktid=b.yktid )

折中时而,用sql拼接来完毕可变参数的机能 

游标
Declare MyCusror Cursor Scroll

先举个sql拼接的事例

For select id, yktid from [Sale_SaleRecord];

图片 4

Open MyCusror
declare @GoodsCode int

DECLARE @sql NVARCHAR(MAX)

declare @GoodsName int
Fetch next From MyCusror
Into @GoodsCode,@GoodsName

SET @sql =’select 1 mmd’

While(@@Fetch_Status = 0)
Begin

EXECUTE(@sql)

Begin
update [Sale_SaleRecord] set deptid=(select deptid from
[Vw_Sale_PersonCard] as a where a.yktid=@GoodsName) where
id=@GoodsCode
End

 

Fetch next From MyCusror
Into @GoodsCode,@GoodsName

折中后的囤积进程

End
Close MyCusror
Deallocate MyCusror

图片 5

玖 、成立视图及按月查询

ALTER PROC
[dbo].[Proc_totalScore]

IF EXISTS(SELECT * FROM sysobjects WHERE
id=OBJECT_ID(‘v_ShouldPaymentRecordsByMonth’))
DROP VIEW v_ShouldPaymentRecordsByMonth
go
CREATE VIEW v_ShouldPaymentRecordsByMonth
AS
SELECT a.CustomerName,CONVERT(NVARCHAR(7),a.PaymentDate,120) AS Yue
,SUM(CASE ad.IsDiscount WHEN ‘0’ THEN ad.ShouldPayment ELSE
-ad.ShouldPayment END) Amount
FROM dbo.Account a
LEFT JOIN dbo.AccountDetail ad ON a.AccountNo=ad.AccountNo
GROUP BY a.CustomerName,CONVERT(NVARCHAR(7),a.PaymentDate,120)

@categoryKey VARCHAR(100),

 

@objectIds VARCHAR(MAX)

SELECT a.AccountNo,a.CustomerName,CONVERT(NVARCHAR(7),a.PaymentDate,120)
,p.Name,
SUM(CASE ad.IsDiscount WHEN ‘0’ THEN ad.ShouldPayment ELSE
-ad.ShouldPayment END) Amount
FROM dbo.Account a
LEFT JOIN dbo.AccountDetail ad ON a.AccountNo=ad.AccountNo
LEFT JOIN dbo.PaymentItem p ON p.PaymentItem=ad.PaymentItem
Group BY
a.AccountNo,a.CustomerName,CONVERT(NVARCHAR(7),a.PaymentDate,120)
,p.Name

AS

⑩ 、存款和储蓄进度动态行转列

BEGIN

————-存储进程动态行转列 ————-
IF EXISTS(SELECT * FROM sysobjects WHERE
id=OBJECT_ID(‘pro_ShouldPaymentRecordsDetail’))
DROP procedure pro_ShouldPaymentRecordsDetail
go
CREATE procedure pro_ShouldPaymentRecordsDetail
(
@tableName SYSNAME ,–行转列表
@groupColumn SYSNAME, –分组字段
–@otherDiplayColumn NVA奥德赛CHA普拉多(max), –输出其余列字段
@row2column SYSNAME, –行变列的字段
@row2columnValue SYSNAME, –行变列值的字段
@sql_where NVARCHAR(100)–WHERE UserName = ”王五”
)
AS
BEGIN
DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @otherDiplayColumnName NVARCHAR(MAX)

DECLARE @sql NVARCHAR(MAX)

–从行数据中拿走大概存在的列
SET @sql_str = N’
SELECT @sql_col_out = ISNULL(@sql_col_out + ”,”,””) +
QUOTENAME([‘+@row2column+’])
FROM [‘+@tableName+’]’+@sql_where+’ GROUP BY [‘+@row2column+’]’
PRINT @sql_str
EXEC sp_executesql @sql_str,N’@sql_col_out NVARCHAR(MAX)
OUTPUT’,@sql_col_out=@sql_col OUTPUT
PRINT @sql_col

SET @sql =’SELECT c.Name,AVG(e.Score)
Score FROM dbo.Sys_com_comment_main m

SET @sql_str = N’
SELECT * FROM (
SELECT [‘+@groupColumn+’],[‘+@row2column+’],[‘+@row2columnValue+’]
FROM [‘+@tableName+’]’+@sql_where+’) p PIVOT
(SUM([‘+@row2columnValue+’]) FOR [‘+@row2column+’] IN ( ‘+ @sql_col
+’) ) AS pvt
ORDER BY pvt.[‘+@groupColumn+’]’
PRINT @sql_str
exec (@sql_str)
END
go

         INNER JOIN
dbo.sys_com_coment_extend e ON e.commentId=m.ID

—-执行存款和储蓄过程
EXEC pro_ShouldPaymentRecordsDetail
‘v_ShouldPaymentRecordsDetail’,’AccountNo’,’Name’,’Amount’,’ where
AccountNo=”888868-2015091815341812” ‘

         INNER JOIN
dbo.sys_com_category c ON e.NameKey=c.NameKey

 十一、视图

         WHERE
m.categoryKey=’+@categoryKey+’ AND m.dataStatus<>99 AND
m.IsCheck=0 AND m.objectId IN(‘+@objectIds+’)

———- 查询客户该账单总分类账簿———-
IF EXISTS(SELECT * FROM sysobjects WHERE
id=OBJECT_ID(‘v_ShouldPaymentRecords’))
DROP VIEW v_ShouldPaymentRecords
go
CREATE VIEW v_ShouldPaymentRecords
AS
SELECT a.AccountNo, a.CustomerName,a.PaymentDate ,SUM(CASE ad.IsDiscount
WHEN ‘0’ THEN ad.ShouldPayment ELSE -ad.ShouldPayment END) Amount
FROM dbo.Account a
LEFT JOIN dbo.AccountDetail ad ON a.AccountNo=ad.AccountNo
GROUP BY a.AccountNo, a.CustomerName,a.PaymentDate

         GROUP BY c.Name’

go
————-查询账单明细——————————
IF EXISTS(SELECT * FROM sysobjects WHERE
id=OBJECT_ID(‘v_ShouldPaymentRecordsDetail’))
DROP VIEW v_ShouldPaymentRecordsDetail
go
CREATE VIEW v_ShouldPaymentRecordsDetail
AS
SELECT a.AccountNo,a.CustomerName,a.PaymentDate ,p.Name,
SUM(CASE ad.IsDiscount WHEN ‘0’ THEN ad.ShouldPayment ELSE
-ad.ShouldPayment END) Amount
FROM dbo.Account a
LEFT JOIN dbo.AccountDetail ad ON a.AccountNo=ad.AccountNo
LEFT JOIN dbo.PaymentItem p ON p.PaymentItem=ad.PaymentItem
Group BY a.AccountNo,a.CustomerName,a.PaymentDate ,p.Name

         EXECUTE(@sql)

END

末尾效果

图片 6

 

相关文章