SQL问题，至wwwwgou老弟，求助
DECLARE @SQL2 VARCHAR(MAX),@emp_id nvarchar(8),@on_date nvarchar(6),@end_date nvarchar(8)
set @emp_id='0001'
set @on_date='201305'
set @end_date='201308'
declare @fieldlist varchar(8000)
SET @fieldlist=
STUFF(
(
select ',' + QUOTENAME(wage_subject_name) from wage_subject
where wage_subject_id in (select distinct(a.wage_subject_id) from wage_set_val a,wage_set b
where b.voucher_id=a.voucher_id and a.emp_id=@emp_id and a.voucher_id in (select voucher_id from wage_set
where data_month>=@on_date and data_month<=@end_date and title in ('部室基础工资','领导效益工资')) ) --and a.voucher_id='4'
group by wage_subject_id,wage_subject_name order by wage_subject_id FOR XML PATH('')
),1,1,'')
--SELECT @fieldlist
--确保你的@sql格式没问题,然后只能动态拼SQL
SET @SQL2 = '
SELECT *
FROM
(
SELECT
b.title 标题,
f.wage_set_name 发放类别 ,
b.data_month 月份,
-- d.dept_id ,
-- a.emp_id ,
-- d.note_info ,
e.wage_subject_name ,
a.val
FROM wage_set_val a ,
wage_set b ,
ebs_v c ,
wage_set_emp d,
wage_subject E,
wage_set_type f
WHERE a.voucher_id = b.voucher_id
and e.wage_subject_id=a.wage_subject_id
AND b.voucher_id = c.voucher_id
AND c.voucher_id = d.voucher_id
and b.wage_set_type=f.wage_set_type
AND a.emp_id = d.emp_id
AND b.data_month BETWEEN '+@on_date+' AND '+@end_date+'
and a.emp_id='+@emp_id+'
and b.title in (''部室基础工资'',''领导效益工资'')
AND d.dept_id IN (
SELECT dept_id
FROM emp_dept
WHERE company_id =''a'' )
) a
PIVOT
(
MAX(val) FOR wage_subject_name IN('+ @fieldlist +')
) b
order by 月份
'
print @sql2
EXEC (@sql2)
-------老兄啊，语句已经完成了，不过加了个变量'部室基础工资','领导效益工资'，请问这个要怎么写，我想做成存储过程，直接传四个变量过去。@title有可能有多个值
@emp_id
@on_date
@end_date
@title
[解决办法]
CREATE PROC up_yourprocname
@emp_id varchar(100),
@on_date CHAR(6),
@end_date CHAR(6),
@title NVARCHAR(500)
AS
SET NOCOUNT ON
DECLARE @SQL2 VARCHAR(MAX)
declare @fieldlist varchar(8000)
SET @fieldlist=
STUFF(
(
select ',' + QUOTENAME(wage_subject_name) from wage_subject
where wage_subject_id in (select distinct(a.wage_subject_id) from wage_set_val a,wage_set b
where b.voucher_id=a.voucher_id and a.emp_id=@emp_id and a.voucher_id in (select voucher_id from wage_set
where data_month>=@on_date and data_month<=@end_date AND CHARINDEX(','+title+',', ','+@title+',') > 0) ) --and a.voucher_id='4'
group by wage_subject_id,wage_subject_name order by wage_subject_id FOR XML PATH('')
),1,1,'')
--SELECT @fieldlist
--确保你的@sql格式没问题,然后只能动态拼SQL
SET @SQL2 = '
SELECT *
FROM
(
SELECT
b.title 标题,
f.wage_set_name 发放类别 ,
b.data_month 月份,
-- d.dept_id ,
-- a.emp_id ,
-- d.note_info ,
e.wage_subject_name ,
a.val
FROM wage_set_val a ,
wage_set b ,
ebs_v c ,
wage_set_emp d,
wage_subject E,
wage_set_type f
WHERE a.voucher_id = b.voucher_id
and e.wage_subject_id=a.wage_subject_id
AND b.voucher_id = c.voucher_id
AND c.voucher_id = d.voucher_id
and b.wage_set_type=f.wage_set_type
AND a.emp_id = d.emp_id
AND b.data_month BETWEEN '''+@on_date+''' AND '''+@end_date+'''
and a.emp_id='''+@emp_id+'''
and b.title in (''部室基础工资'',''领导效益工资'')
AND d.dept_id IN (
SELECT dept_id
FROM emp_dept
WHERE company_id =''a'' )
) a
PIVOT
(
MAX(val) FOR wage_subject_name IN('+ @fieldlist +')
) b
order by 月份
'
print @sql2
EXEC (@sql2)
GO
--data_month字段是字符类型就没有问题？否则给出字段类型和格式
--emp_id字段从传参看来应该是INT类型吧？
--测试
EXEC up_yourprocname
@emp_id = '0001',
@on_date = '201305',
@end_date = '201308',
@title = '部室基础工资,领导效益工资'
