sqlserver自定义报表报“数据库错‘+’附近有语法错误”,哪位大神帮忙定位一下错误呢,感谢

发布时间: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的,你可以自己把参数设俩数执行下

以上问题属网友观点,不代表本站立场,仅供参考!