发布时间:2019-07-31 15:31:59
DECLARE @item_field NVARCHAR(500)
DECLARE @item_field_sum NVARCHAR(MAX)
DECLARE @acct_year_slct NVARCHAR(4)
DECLARE @acct_month NVARCHAR(2)
DECLARE @dept_code NVARCHAR(20)
DECLARE @emp_duty NVARCHAR(20)
DECLARE @emp_title NVARCHAR(20)
DECLARE @copy_code_1 NVARCHAR(10)
SET @item_field = ''
SET @item_field_sum = ''
SET @acct_year_slct = @para0
SET @acct_month = @para1
SET @dept_code = @para2
SET @emp_duty = @para3
SET @emp_title = @para4
SET @copy_code_1 = '001'
/*游标变量定义*/
DECLARE @name NVARCHAR(80)
DECLARE @colu NVARCHAR(20)
/*自定义变量*/
DECLARE @sql VARCHAR(5000)
CREATE TABLE #tc_tab(id VARCHAR(100))
EXECUTE wageTableAllReadyTableColumn @p_code,@copy_code_1,@acct_year_slct
CREATE TABLE #tbl_data_final(emp_id NVARCHAR(30),emp_name NVARCHAR(80),dept_name NVARCHAR(80),emp_duty NVARCHAR(40),emp_title NVARCHAR(40))
INSERT INTO #tbl_data_final
SELECT '职工编码','职工姓名','部门','职称','职务'
DECLARE cur_slry_item CURSOR FOR
SELECT name,colu
FROM #tc_tab
ORDER BY code
OPEN cur_slry_item
FETCH NEXT FROM cur_slry_item INTO @name
,@colu
WHILE @@FETCH_STATUS = 0
BEGIN
SET @item_field += @colu+','
SET @item_field_sum += 'SUM(CONVERT(MONEY,'+@colu+')),'
SET @sql='ALTER TABLE #tbl_data_final ADD '+@colu+' NVARCHAR(20) ' /*动态加列*/
EXECUTE(@sql)
SET @sql='UPDATE #tbl_data_final SET '+@colu + ' =''' +@name+''' WHERE emp_id = ''职工编码'''
EXECUTE(@sql)
FETCH NEXT FROM cur_slry_item INTO @name
,@colu
END
CLOSE cur_slry_item
DEALLOCATE cur_slry_item
IF(@item_field = '')
BEGIN
SET @item_field = ''''''
END
ELSE
BEGIN
SET @item_field = SUBSTRING(@item_field,1,LEN(@item_field)-1)
END
IF(@item_field_sum = '')
BEGIN
SET @item_field_sum = ''''''
END
ELSE
BEGIN
SET @item_field_sum = SUBSTRING(@item_field_sum,1,LEN(@item_field_sum)-1)
END
SET @sql=' INSERT INTO #tbl_data_final
SELECT sp.emp_code
,sp.emp_name
,sd.dept_name
,CASE WHEN dd.duty_name IS NULL THEN ''未指定'' ELSE dd.duty_name END duty_name
,CASE WHEN dt.title_name IS NULL THEN ''未指定'' ELSE dt.title_name END title_name
,'+@item_field+'
FROM slry_pay sp
LEFT JOIN sys_emp se
ON sp.emp_code = se.emp_code
AND sp.p_code = se.p_code
LEFT JOIN sys_dept sd
ON sp.dept_code = sd.dept_code
AND sp.p_code = sd.p_code
LEFT JOIN dict_duty dd
ON se.duty_code = dd.duty_code
LEFT JOIN dict_title dt
ON se.title_code = dt.title_code
WHERE sp.acct_year = '''+@acct_year_slct +'''
AND sp.acct_month = '''+@acct_month +'''
AND sp.p_code = '''+@p_code+'''
AND sp.copy_code = '''+@copy_code_1+'''
AND (sp.dept_code LIKE '''+@dept_code+'''+''%'' OR '''+@dept_code+''' = '''')
AND (se.duty_code = '''+@emp_duty+''' OR '''+@emp_duty+''' = '''')
AND (se.title_code = '''+@emp_title+''' OR '''+@emp_title+''' = '''')
'
EXECUTE(@sql)
SET @sql=' INSERT INTO #tbl_data_final
SELECT ''合计'','''','''','''','''','+@item_field_sum+'
FROM #tbl_data_final WHERE emp_id <>''职工编码''
'
EXECUTE(@sql)
SELECT * FROM #tbl_data_final
DROP TABLE #tc_tab
DROP TABLE #tbl_data_final
(1)你的 (currentPage-1)*pageSize 这个地方出错了。 对参数的引用不能通过运算符之后再引用,系统会认为两个+之间是一个参数名字"select top " + pageSize + " orderNumber,spm,guige,pihao,shuliang from [orderStatusInfo] where z_sid not in(select top (" +currentPage+ "-1)*" +pageSize+" z_sid from [orderStatusInfo] order by z_sid) order by z_sid";(2)你这个sql语句本身就有问题,子查询中z_sid 使用别名是不能在后面order by的,你可以自己把参数设俩数执行下