set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Search_TongJiTimeName] @name varchar(50), --查询名称
@date DateTime, --开始时间
@dateend DateTime --结束时间
AS
BEGIN
SET NOCOUNT ON
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,
@S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))
FROM VIEW_XuQiuGongYingTongji
where Monday>=@date and Monday<=@dateend and ProductName Like '%'+@name+'%'--这里加时间限制
GROUP BY [Monday]
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,
CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,
Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,
'+@S1+',Total
FROM (
SELECT [ProductName],[SupplyName],
CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,
SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') and ProductName Like ''%'+@name+'%'' VIEW_XuQiuGongYingTongji
UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber, Balance)) B
) A
PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B
ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3 END
'
EXEC(@SQL)
SET NOCOUNT OFF
END
GO加名称查询时出错,错误为:and附近有语法错误。谁能解决立即给分。查询分析器测试通过,但是程序调用时出错。
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Search_TongJiTimeName] @name varchar(50), --查询名称
@date DateTime, --开始时间
@dateend DateTime --结束时间
AS
BEGIN
SET NOCOUNT ON
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,
@S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))
FROM VIEW_XuQiuGongYingTongji
where Monday>=@date and Monday<=@dateend and ProductName Like '%'+@name+'%'--这里加时间限制
GROUP BY [Monday]
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,
CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,
Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,
'+@S1+',Total
FROM (
SELECT [ProductName],[SupplyName],
CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,
SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''') and ProductName Like ''%'+@name+'%'' VIEW_XuQiuGongYingTongji
UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber, Balance)) B
) A
PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B
ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3 END
'
EXEC(@SQL)
SET NOCOUNT OFF
END
GO加名称查询时出错,错误为:and附近有语法错误。谁能解决立即给分。查询分析器测试通过,但是程序调用时出错。
凭眼睛看还是没机器快
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[Search_TongJiTimeName] @name varchar(50), --查询名称
@date DateTime, --开始时间
@dateend DateTime --结束时间
AS
BEGIN
SET NOCOUNT ON
DECLARE @S VARCHAR(8000),@s1 VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,Monday,23)) ,
@S1=isnull(@S1+',','')+'isnull('+quotename(convert(VARCHAR,Monday,23))+',0)'+quotename(convert(VARCHAR,Monday,23))
FROM VIEW_XuQiuGongYingTongji
where Monday>=@date and Monday<=@dateend and ProductName Like '%'+@name+'%'--这里加时间限制
GROUP BY [Monday]
DECLARE @SQL VARCHAR(MAX)
SET @SQL='
SELECT CASE TYPE WHEN ''XuQiuNumber'' THEN ProductName ELSE '''' END ProductName,
CASE TYPE WHEN ''XuQiuNumber'' THEN SupplyName ELSE '''' END SupplyName,
Date = CASE TYPE WHEN ''XuQiuNumber'' THEN ''Order Demand'' WHEN ''AlreadyNumber'' THEN ''Schedule'' ELSE ''Balance'' END,
'+@S1+',Total
FROM (
SELECT [ProductName],[SupplyName],
CONVERT(VARCHAR,Monday,23) Monday,VALUE,TYPE,
SUM(VALUE)OVER(PARTITION BY ProductName,SupplyName,Type) Total
FROM (SELECT * FROM VIEW_XuQiuGongYingTongji WHERE Monday>='''+convert(CHAR,@date,23)+''' and Monday<='''+convert(CHAR,@dateend,23)+''' and ProductName Like ''%'+@name+'%'') VIEW_XuQiuGongYingTongji
UNPIVOT(VALUE FOR TYPE IN(XuQiuNumber,AlreadyNumber, Balance)) B
) A
PIVOT(MAX(VALUE) FOR Monday IN('+@S+')) B
ORDER BY B.ProductName,CASE TYPE WHEN ''XuQiuNumber'' THEN 1 WHEN ''AlreadyNumber'' THEN 2 ELSE 3 END
'
EXEC(@SQL)
SET NOCOUNT OFF
END
GO
datetime类型的字段在where里应该加上''引起来的,但''在存储过程里是没办法直接加的,我的解决办法是先将条件里的datatime型变量转换成字符串,然后再拼接查询的SQL语句时在and Monday<=+char(39)+datetime转换成的字符串+char(39)
char(39)是'的ASCII十进制值,是不是39我记不清了,你可以查一下ASCII表!