全部数据如下:USE [RsDemo]
GO
/****** Object: StoredProcedure [dbo].[P_INFO_VIEW] Script Date: 01/04/2012 14:36:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <杜鹏>
-- Create date: <2011-12-20>
-- =============================================
ALTER PROCEDURE [dbo].[P_INFO_VIEW]
@STARTYEAR VARCHAR(50) ,@STARTMONTH VARCHAR(50),
@ENDYEAR VARCHAR(50),@ENDMONTH VARCHAR(50),@ZYKJ VARCHAR(50),@CYKJ VARCHAR(50)
AS
BEGIN
CREATE TABLE #table1(SDID VARCHAR(50),PM INT,YEAR INT,Month INT,GYZCZ INT ,QNGYZCZ INT, ZL FLOAT,ZF FLOAT)
CREATE TABLE #table2(SDID VARCHAR(50),PM INT,YEAR INT,Month INT,GYZCZ INT ,QNGYZCZ INT,ZL FLOAT,ZF FLOAT)DECLARE @GYZCIsAdd VARCHAR(50)
select @GYZCIsAdd=IsAdd from T_Quota WHERE QID='GYZCZ'
IF @GYZCIsAdd='y'
BEGIN
declare @sql varchar(max)
set @sql=' INSERT INTO #table1 SELECT SDID,ROW_NUMBER() OVER (ORDER BY GYZCZ) AS PM ,YEAR,MONTH, GYZCZ,
(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID) AS QNGYZCZ,
(GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZL,
((GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID))/(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZF FROM T_Data AS T WHERE YEAR>='+@STARTYEAR+' AND YEAR <='+@ENDYEAR+' AND MONTH>='+@STARTMONTH+' AND MONTH<='+@ENDMONTH+' AND PDID='+@ZYKJ+' AND SDID IN ('''+@CYKJ+''') ORDER BY MONTH,SDID'
EXEC(@sql)
DECLARE @YJ2 VARCHAR(2000)
SET @YJ2='SELECT * FROM #TABLE1'
EXEC (@YJ2)
DROP TABLE #TABLE1
END
IF @GYZCIsAdd <>'y'
BEGIN
DECLARE @YJ3 VARCHAR(2000)
SET @YJ3='INSERT INTO #table2 SELECT SDID,ROW_NUMBER() OVER (ORDER BY GYZCZ) AS PM ,YEAR,MONTH, GYZCZ,
(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID) AS QNGYZCZ,
(GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZL,
((GYZCZ-(SELECT GYZCZ TOP1 from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID))/(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZF FROM T_Data AS T WHERE YEAR>='+@STARTYEAR+' AND YEAR <='+@ENDYEAR+' AND MONTH>='+@STARTMONTH+' AND MONTH<='+@ENDMONTH+' AND PDID='+@ZYKJ+' AND SDID IN ('''+@CYKJ+''') ORDER BY MONTH,SDID'
EXEC(@YJ3)
DECLARE @YJ4 VARCHAR(50)
SET @YJ4='SELECT * FROM #TABLE2'
EXEC (@YJ4)
DROP TABLE #TABLE2
END
END
关键是这里
set @sql=' INSERT INTO #table1 SELECT SDID,ROW_NUMBER() OVER (ORDER BY GYZCZ) AS PM ,YEAR,MONTH, GYZCZ,
(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID) AS QNGYZCZ,
(GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZL,
((GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID))/(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZF FROM T_Data AS T WHERE YEAR>='+@STARTYEAR+' AND YEAR <='+@ENDYEAR+' AND MONTH>='+@STARTMONTH+' AND MONTH<='+@ENDMONTH+' AND PDID='+@ZYKJ+' AND SDID IN ('''+@CYKJ+''') ORDER BY MONTH,SDID'我的@CYKJ的值是1'',''2
可是就是什么都查不出来呢?
GO
/****** Object: StoredProcedure [dbo].[P_INFO_VIEW] Script Date: 01/04/2012 14:36:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <杜鹏>
-- Create date: <2011-12-20>
-- =============================================
ALTER PROCEDURE [dbo].[P_INFO_VIEW]
@STARTYEAR VARCHAR(50) ,@STARTMONTH VARCHAR(50),
@ENDYEAR VARCHAR(50),@ENDMONTH VARCHAR(50),@ZYKJ VARCHAR(50),@CYKJ VARCHAR(50)
AS
BEGIN
CREATE TABLE #table1(SDID VARCHAR(50),PM INT,YEAR INT,Month INT,GYZCZ INT ,QNGYZCZ INT, ZL FLOAT,ZF FLOAT)
CREATE TABLE #table2(SDID VARCHAR(50),PM INT,YEAR INT,Month INT,GYZCZ INT ,QNGYZCZ INT,ZL FLOAT,ZF FLOAT)DECLARE @GYZCIsAdd VARCHAR(50)
select @GYZCIsAdd=IsAdd from T_Quota WHERE QID='GYZCZ'
IF @GYZCIsAdd='y'
BEGIN
declare @sql varchar(max)
set @sql=' INSERT INTO #table1 SELECT SDID,ROW_NUMBER() OVER (ORDER BY GYZCZ) AS PM ,YEAR,MONTH, GYZCZ,
(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID) AS QNGYZCZ,
(GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZL,
((GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID))/(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZF FROM T_Data AS T WHERE YEAR>='+@STARTYEAR+' AND YEAR <='+@ENDYEAR+' AND MONTH>='+@STARTMONTH+' AND MONTH<='+@ENDMONTH+' AND PDID='+@ZYKJ+' AND SDID IN ('''+@CYKJ+''') ORDER BY MONTH,SDID'
EXEC(@sql)
DECLARE @YJ2 VARCHAR(2000)
SET @YJ2='SELECT * FROM #TABLE1'
EXEC (@YJ2)
DROP TABLE #TABLE1
END
IF @GYZCIsAdd <>'y'
BEGIN
DECLARE @YJ3 VARCHAR(2000)
SET @YJ3='INSERT INTO #table2 SELECT SDID,ROW_NUMBER() OVER (ORDER BY GYZCZ) AS PM ,YEAR,MONTH, GYZCZ,
(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID) AS QNGYZCZ,
(GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZL,
((GYZCZ-(SELECT GYZCZ TOP1 from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID))/(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZF FROM T_Data AS T WHERE YEAR>='+@STARTYEAR+' AND YEAR <='+@ENDYEAR+' AND MONTH>='+@STARTMONTH+' AND MONTH<='+@ENDMONTH+' AND PDID='+@ZYKJ+' AND SDID IN ('''+@CYKJ+''') ORDER BY MONTH,SDID'
EXEC(@YJ3)
DECLARE @YJ4 VARCHAR(50)
SET @YJ4='SELECT * FROM #TABLE2'
EXEC (@YJ4)
DROP TABLE #TABLE2
END
END
关键是这里
set @sql=' INSERT INTO #table1 SELECT SDID,ROW_NUMBER() OVER (ORDER BY GYZCZ) AS PM ,YEAR,MONTH, GYZCZ,
(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID) AS QNGYZCZ,
(GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZL,
((GYZCZ-(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID))/(SELECT GYZCZ from [T_Data] where YEAR=T.Year-1 AND MONTH=T.Month AND SDID=T.SDID)) AS ZF FROM T_Data AS T WHERE YEAR>='+@STARTYEAR+' AND YEAR <='+@ENDYEAR+' AND MONTH>='+@STARTMONTH+' AND MONTH<='+@ENDMONTH+' AND PDID='+@ZYKJ+' AND SDID IN ('''+@CYKJ+''') ORDER BY MONTH,SDID'我的@CYKJ的值是1'',''2
可是就是什么都查不出来呢?
解决方案 »
- 各位大虾救命啊!!!
- 数据库、中间层、客户端的三层结构是什么意思?
- js实现textbox隐藏,但刷新过后textbox 又显示问题~~ 50分 求高手~
- HyperLink如何接收参数,谢
- [请教]我从URL地址获取到一个100多M的文件下载有问题吗
- ASP.NET MVC3 微软官方都下载不了.
- 请教高手!怎么在后台代码中获取一个服务器控件将生成的html代码?比如页面上的一个DropDownList?
- 求一些用户登陆验证的文章!随手问一下兄弟问都是怎么 做的?
- 请教如何每次只显示一条数据
- 再加上100分,累计369分,★★★难题见真功★★要多少分给多少分,这贴在CSDN一天了也没人答上是咋么回事
- 求平衡组,正则表达式
- 关于截取字符串,标签被截断的问题
USE [RsDemo]
GODECLARE @return_value intEXEC @return_value = [dbo].[P_INFO_VIEW]
@STARTYEAR = N'2011',
@STARTMONTH = N'1',
@ENDYEAR = N'2011',
@ENDMONTH = N'5',
@ZYKJ = N'1',
@CYKJ = N'1'''',''''2'SELECT 'Return Value' = @return_value
执行参数
--加个默认值好使不
DECLARE @GYZCIsAdd VARCHAR(50)=''
看在sql里执行,报的什么错误,再根据错误,做相应修改.
SDID IN ('''+@CYKJ+''')
看你传进去的@CYKJ是什么字符,还有跟('''+@CYKJ+''')