存储过程中输入表列名为变量的问题

[沉默的羔羊]

sql的存储变量中,碰到下面一个问题,希望可以由ASP.NET客户输入需要查询的资料，来输出，具体语句如下，

USE [DATA]
GO
/****** Object:  StoredProcedure [dbo].[P]    Script Date: 11/21/2011 19:36:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<From CSDN>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[P]
	--(@dalei [nvarchar](50),@riqi [int],@nian [int],@bizhong [NVARCHAR](50))
	(@dalei [nvarchar](50),@riqi [int],@nian [int])
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--USE [DATA]

--/****** Object:  StoredProcedure [dbo].[P]    Script Date: 11/20/2011 21:01:25 ******/
SET ANSI_NULLS ON
--GO
SET QUOTED_IDENTIFIER ON
--GO

if not object_id('Tempdb..#T') is null
    drop table #T
    
Create table #T([bupinlistid] int,[bupinmingcheng] nvarchar(50) ,[bupinfanhao] nvarchar(50) ,[danjia] decimal(18,6),[danjiarmb] decimal(18,6),[danjiausd] decimal(18,6),[shuliang] int,[date] Datetime)
Insert into #T(bupinlistid,bupinmingcheng,bupinfanhao,danjia,danjiarmb,danjiausd,shuliang ,date) select b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,c.danjiarb,c.danjiarmb,c.danjiausd,a.shuliang ,a.date from bupin as a right join bupinlist as b on a.bupinlistid=b.bupinlistid   and  month(a.date)= @riqi and YEAR(a.date) = @nian left join bupinbase as c on a.bupinlistid = c.bupinlistid  where b.dalei = @dalei order by b.bupinlistid



--SQL2005 
declare @t nvarchar(MAX)
Select  @t=isnull(@t+',','')+quotename(CONVERT(varchar(10),Date,120)) from #T group by date --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [bupinlistid] as 序号,[bupinmingcheng] as 部品名称,[bupinfanhao] as 部品番号,convert(decimal(18,2),isnull([danjia],0)) as [日币单价],[danjiarmb] as 人民币单价,[danjiausd] as 美元单价,'+@t+',isnull([zongshu],0) as 总数 from (select *,[zongshu]=sum([shuliang]) over (partition by [bupinlistid]) from #T  )  a pivot (sum([shuliang]) for [date] in('+@t+')  )b ')



END

如上语句是可以满足输出的，
但现在需要将输入表中的[danjia]用变量来替代，该怎么来修改这个语句呢？

[中国风]

@danjia--如果是列名時要動態
如下

Insert into #T(bupinlistid,bupinmingcheng,bupinfanhao,danjia,danjiarmb,danjiausd,shuliang ,date)
EXEC('select b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,'+@danjia+',c.danjiarmb,c.danjiausd,a.shuliang ,a.date
from bupin as a
right join bupinlist as b on a.bupinlistid=b.bupinlistid   and  month(a.date)= @riqi and YEAR(a.date) = @nian l
eft join bupinbase as c on a.bupinlistid = c.bupinlistid  where b.dalei = @dalei
order by b.bupinlistid')

[Superion]

为了保证代码的完整性，最好不要乱改SQL语句，否则过一段时间之后（例如半年后），随着代码量变的越来越庞大，你就会忘记你过去曾经做过什么修改，特别是那种很隐蔽，很不显眼的修改。

建议把需要用到变量的代码都放到C#中解决。

[沉默的羔羊]

前面弄了一个晚上，就是一直在报错，说列名无效。

楼上的提议让我想到干脆把select语句全部都作为变量通过ASP.NET C#前端去输入。
直接就是 EXEC(@SQLYJ)

结果就可以了。。。。


最近一直都在和动态SQL打交道，发现还真是很强大，有没有什么书推荐的么？

[--小F--]

动态SQL。

[中国风]

GO
-- =============================================
-- Author: <From CSDN>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[P]
--(@dalei [nvarchar](50),@riqi [int],@nian [int],@bizhong [NVARCHAR](50))
(@dalei [nvarchar](50),@riqi [int],@nian [int],@danjia decimal(18,6))

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--USE [DATA]

--/****** Object: StoredProcedure [dbo].[P] Script Date: 11/20/2011 21:01:25 ******/
SET ANSI_NULLS ON
--GO
SET QUOTED_IDENTIFIER ON
--GO

if not object_id('Tempdb..#T') is null
drop table #T

Create table #T([bupinlistid] int,[bupinmingcheng] nvarchar(50) ,[bupinfanhao] nvarchar(50) ,[danjia] decimal(18,6),[danjiarmb] decimal(18,6),[danjiausd] decimal(18,6),[shuliang] int,[date] Datetime)
Insert into #T
(bupinlistid,bupinmingcheng,bupinfanhao,danjia,danjiarmb,danjiausd,shuliang ,date)
select
b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,@danjia,c.danjiarmb,c.danjiausd,a.shuliang ,a.date
from bupin as a
right join bupinlist as b on a.bupinlistid=b.bupinlistid and month(a.date)= @riqi and YEAR(a.date) = @nian
left join bupinbase as c on a.bupinlistid = c.bupinlistid
where b.dalei = @dalei order by b.bupinlistid



--SQL2005
declare @t nvarchar(MAX)
Select @t=isnull(@t+',','')+quotename(CONVERT(varchar(10),Date,120)) from #T group by date --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [bupinlistid] as 序号,[bupinmingcheng] as 部品名称,[bupinfanhao] as 部品番号,convert(decimal(18,2),isnull([danjia],0)) as [日币单价],[danjiarmb] as 人民币单价,[danjiausd] as 美元单价,'+@t+',isnull([zongshu],0) as 总数 from (select *,[zongshu]=sum([shuliang]) over (partition by [bupinlistid]) from #T ) a pivot (sum([shuliang]) for [date] in('+@t+') )b ')



END

---看紅色字體

[-晴天]

改一下这一句:
Insert into #T(bupinlistid,bupinmingcheng,bupinfanhao,danjia,danjiarmb,danjiausd,shuliang ,date) select b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,c.danjiarb,c.danjiarmb,c.danjiausd,a.shuliang ,a.date from bupin as a right join bupinlist as b on a.bupinlistid=b.bupinlistid and month(a.date)= @riqi and YEAR(a.date) = @nian left join bupinbase as c on a.bupinlistid = c.bupinlistid where b.dalei = @dalei order by b.bupinlistid

为:

exec('Insert into #T(bupinlistid,bupinmingcheng,bupinfanhao,danjia,danjiarmb,danjiausd,shuliang ,date) select b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,c.'+@danjia+'rb,c.'+@danjia+'rmb,c.'+@danjia+'usd,a.shuliang ,a.date from bupin as a right join bupinlist as b on a.bupinlistid=b.bupinlistid   and  month(a.date)= @riqi and YEAR(a.date) = @nian left join bupinbase as c on a.bupinlistid = c.bupinlistid  where b.dalei = @dalei order by b.bupinlistid')

并且添加一个传入变量 @danjia

[沉默的羔羊]

[Quote=引用 3 楼 qianjin036a 的回复:]
改一下这一句:
Insert into #T(bupinlistid,bupinmingcheng,bupinfanhao,danjia,danjiarmb,danjiausd,shuliang ,date) select b.bupinlistid,b.bupinmingcheng,b.bupinfanhao,c.danjiarb,c.danjiarmb,c.danjiausd,a.shuli……
[/Quote]

已修改成exec的方式运行，但是，输入变量后，会出错。
我的变量分别是
EXEC @return_value = [dbo].[P]
@dalei = N'htht',
@riqi = 9,
@nian = 2011,
@danjia = N'danjia'
报错内容：

消息 207，级别 16，状态 1，第 1 行
列名 'htht' 无效。
消息 102，级别 15，状态 1，第 1 行
',' 附近有语法错误。
消息 102，级别 15，状态 1，第 1 行
'a' 附近有语法错误。

一下子没方向了。。。

[Superion]

[Quote=引用 8 楼 harlow1999 的回复:]
前面弄了一个晚上，就是一直在报错，说列名无效。

楼上的提议让我想到干脆把select语句全部都作为变量通过ASP.NET C#前端去输入。
直接就是 EXEC(@SQLYJ)

结果就可以了。。。。


最近一直都在和动态SQL打交道，发现还真是很强大，有没有什么书推荐的么？
[/Quote]
初学是《SQL学习指南》，进阶是《SQL语言艺术》。