存储过程:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [SM_ProInputGather]
@PROVNAME VARCHAR(50),
@BEGINDATE DATETIME,
@ENDDATE DATETIME
AS
DECLARE @SQL VARCHAR(1000)
SELECT @SQL=
'SELECT C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,SUM(B.NUM) ,E.PROVNAME
FROM SM_INPUTPROD A ,SM_INPUTPRODDETAIL B ,SM_PRODUCT C ,SM_UNIT D ,SM_PROVIDER E
WHERE A.INPUTNO=B.INPUTNO AND B.PRODUCTID=C.PRODUCTID AND C.UNITID=D.UNITID
AND A.PROVID=E.PROVID '
IF( @PROVNAME != '' )
SELECT @SQL=@SQL+' AND E.PROVNAME LIKE ''%'+ @PROVNAME +'%'' '
IF( @BEGINDATE !='' AND @ENDDATE!='')
SELECT @SQL=@SQL+' AND ( A.INPUTDATE BETWEEN '''+@BEGINDATE+''' AND '''+@ENDDATE+''' ) '
SELECT @SQL=@SQL+'
GROUP BY C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,E.PROVNAME
ORDER BY C.PRODUCTNAME ,E.PROVNAME'
EXEC (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO调用EXEC SM_ProInputGather '宝胜','2006-9-14','2006-9-16'但是却报错:
服务器: 消息 241,级别 16,状态 1,过程 SM_ProInputGather,行 17
从字符串转换为 datetime 时发生语法错误。
请教哪里错了,谢谢
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [SM_ProInputGather]
@PROVNAME VARCHAR(50),
@BEGINDATE DATETIME,
@ENDDATE DATETIME
AS
DECLARE @SQL VARCHAR(1000)
SELECT @SQL=
'SELECT C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,SUM(B.NUM) ,E.PROVNAME
FROM SM_INPUTPROD A ,SM_INPUTPRODDETAIL B ,SM_PRODUCT C ,SM_UNIT D ,SM_PROVIDER E
WHERE A.INPUTNO=B.INPUTNO AND B.PRODUCTID=C.PRODUCTID AND C.UNITID=D.UNITID
AND A.PROVID=E.PROVID '
IF( @PROVNAME != '' )
SELECT @SQL=@SQL+' AND E.PROVNAME LIKE ''%'+ @PROVNAME +'%'' '
IF( @BEGINDATE !='' AND @ENDDATE!='')
SELECT @SQL=@SQL+' AND ( A.INPUTDATE BETWEEN '''+@BEGINDATE+''' AND '''+@ENDDATE+''' ) '
SELECT @SQL=@SQL+'
GROUP BY C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,E.PROVNAME
ORDER BY C.PRODUCTNAME ,E.PROVNAME'
EXEC (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO调用EXEC SM_ProInputGather '宝胜','2006-9-14','2006-9-16'但是却报错:
服务器: 消息 241,级别 16,状态 1,过程 SM_ProInputGather,行 17
从字符串转换为 datetime 时发生语法错误。
请教哪里错了,谢谢
解决方案 »
- sqlserver2000,如何对一批记录,自动生成行号呢?
- 我想找一些标准sql的资料,哪有呀?即标准sql都规定了什么?
- SQL默认值getdate()的格式
- 如何将数据库升级到2005?
- 求一个关于循环插入记录的存储过程~!!!
- 求一个查询语句!!!!高手进
- to haiwer:帮我写句sql
- 讨论:SQL Server的表能支持多少数据量,在多大的数据量下能够游刃有余,200万条记录怎么样?
- Finding SQL servers that does not broadcast on the LAN
- ☆☆★★★MySQL的难题★★★☆☆
- 在存储过程中能够使用用户自定义的函数吗,怎么使用呀?
- 如果表格name 列中 含有unicode数据,如何处理?
A.INPUTDATE
改成
CAST(A.INPUTDATE AS datetime)
@PROVNAME VARCHAR(50),
@BEGINDATE varchar(50),
@ENDDATE varchar(50)
AS
DECLARE @SQL VARCHAR(1000)
SELECT @SQL=
'SELECT C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,SUM(B.NUM) ,E.PROVNAME
FROM SM_INPUTPROD A ,SM_INPUTPRODDETAIL B ,SM_PRODUCT C ,SM_UNIT D ,SM_PROVIDER E
WHERE A.INPUTNO=B.INPUTNO AND B.PRODUCTID=C.PRODUCTID AND C.UNITID=D.UNITID
AND A.PROVID=E.PROVID '
IF( @PROVNAME != '' )
SELECT @SQL=@SQL+' AND E.PROVNAME LIKE ''%'+ @PROVNAME +'%'' '
IF( @BEGINDATE !='' AND @ENDDATE!='')
SELECT @SQL=@SQL+' AND ( A.INPUTDATE BETWEEN '''+@BEGINDATE+''' AND '''+@ENDDATE+''' ) '
SELECT @SQL=@SQL+'
GROUP BY C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,E.PROVNAME
ORDER BY C.PRODUCTNAME ,E.PROVNAME'
EXEC (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
改前面的就可以拉
但是我执行时:
EXEC SM_ProInputGather '宝胜','2006-9-14','2006-9-16'却没有结果,WHY???
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [SM_PROINPUTGATHER]
@PROVNAME VARCHAR(50),
@BEGINDATE DATETIME,
@ENDDATE DATETIME
ASDECLARE @SQL VARCHAR(1000)
SELECT @SQL=
'SELECT C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,SUM(B.NUM) ,E.PROVNAME
FROM SM_INPUTPROD A ,SM_INPUTPRODDETAIL B ,SM_PRODUCT C ,SM_UNIT D ,SM_PROVIDER E
WHERE A.INPUTNO=B.INPUTNO AND B.PRODUCTID=C.PRODUCTID AND C.UNITID=D.UNITID
AND A.PROVID=E.PROVID '
IF( @PROVNAME != '' )
SELECT @SQL=@SQL+' AND E.PROVNAME LIKE ''%'+ @PROVNAME +'%'' '
IF( CAST(@BEGINDATE AS NVARCHAR(20)) !='' AND CAST(@ENDDATE AS NVARCHAR(20))!='')
SELECT @SQL=@SQL+' AND ( A.INPUTDATE BETWEEN '''+CAST(@BEGINDATE AS NVARCHAR(20))+''' AND '''+CAST(@ENDDATE AS NVARCHAR(20))+''' ) '
SELECT @SQL=@SQL+'
GROUP BY C.PRODUCTNAME ,C.PRODUCTTYPE ,D.UNITNAME ,B.PRICE ,E.PROVNAME
ORDER BY C.PRODUCTNAME ,E.PROVNAME' EXEC (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO