laravel - 查询的事件结果不包含任何字段 laravel

Question

我尝试使用参数执行存储过程，然后出现错误。

SQLSTATE[IMSSP]:The active result for the query contains no fields. (SQL: exec [dbo].[CHECK_ACCESS_ALL_EMP] '10249', '','','','','','')

但我尝试在错误时执行选择结果

exec [dbo].[CHECK_ACCESS_ALL_EMP] '10249', '','','','','','' it work fine in database and the result after executed store procedure is:

SELECT EMPCODE, ALLNAME, SEX, DIVISION, DEPARTMENT AS DEPT, POSTDESC, EMAIL,CONVERT(VARCHAR, DOB, 106) AS DOB, TRANNO, LEVELCODE FROM RPT_EMPLOYEE 
WHERE (TERMINATESTATUS='' OR (TERMINATESTATUS<>'' AND DATETERMINATE>=GETDATE()))

Laravel 函数

public function emplyeeList()
    {
        $employee = HREmpMast::orderBy('EMPCODE')->paginate(10);
        $emp_code = Auth::user()->EMPCODE;

        if($emp_code != null){
           $script = DB::select("exec [dbo].[CHECK_ACCESS_ALL_EMP] '".$emp_code."', '','','','','',''");
        }

        return $script;
    }

存储过程

USE [SOFITELSR_HRMS]
    GO
    /****** Object:  StoredProcedure [dbo].[CHECK_ACCESS_ALL_EMP]    Script Date: 10/24/2017 3:41:46 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[CHECK_ACCESS_ALL_EMP](@EMPCODE VARCHAR(10),
        @FILTER_EMPCODE VARCHAR(10),
        @FILTER_EMPNAME VARCHAR(200),
        @FILTER_CATEGOYR VARCHAR(10),
        @FILTER_DIVISION VARCHAR(10),
        @FILTER_DEPT VARCHAR(10),
        @FILTER_POSITION VARCHAR(10)    
        )AS
    BEGIN
        DECLARE @ACCESS_ALL_EMP INT=0,@LINEID INT,@LINEID_DELEGATE INT,@DELCODE VARCHAR(10),
                @VIEW_BY_DEPT VARCHAR(500),@VIEW_BY_DEPT_DELEGATE VARCHAR(500),@MYSCRIPT VARCHAR(1000)
        SET @DELCODE=''
        SET @VIEW_BY_DEPT=''    
        SET @MYSCRIPT='SELECT EMPCODE, ALLNAME, SEX, DIVISION, DEPARTMENT AS DEPT, POSTDESC, EMAIL,CONVERT(VARCHAR, DOB, 106) AS DOB, TRANNO, LEVELCODE FROM RPT_EMPLOYEE 
        WHERE (TERMINATESTATUS='''' OR (TERMINATESTATUS<>'''' AND DATETERMINATE>=GETDATE()))'   
        SELECT @DELCODE=EMPCODE FROM HR_LEAVEDELEGATE WHERE DELCODE=@EMPCODE--Get delegate information
        SELECT @LINEID_DELEGATE=TRANNO FROM HR_EMPMAST WHERE EMPCODE=@DELCODE --Get line transaction no of delegate person
        SELECT @LINEID=TRANNO FROM HR_EMPMAST WHERE EMPCODE=@EMPCODE--Get line transaction no
        IF EXISTS(SELECT GDCODE FROM SYS_SETTING WHERE GDCODE=@EMPCODE) OR --General Manager
            EXISTS(SELECT EMPCODE FROM HR_EMPMAST WHERE EMPCODE=@EMPCODE AND BOARD=1) OR --Operation Manager
            EXISTS(SELECT HDCODE FROM SYS_SETTING WHERE HDCODE=@EMPCODE) OR --Head of HR
            EXISTS(SELECT HRCODE FROM SYS_SETTING WHERE HRCODE=@EMPCODE) OR --HR Assistant  
            EXISTS(SELECT GDCODE FROM SYS_SETTING WHERE GDCODE=@DELCODE) OR --General Manager by delegate
            EXISTS(SELECT EMPCODE FROM HR_EMPMAST WHERE EMPCODE=@DELCODE AND BOARD=1) OR --Operation Manager by delegate
            EXISTS(SELECT HDCODE FROM SYS_SETTING WHERE HDCODE=@DELCODE) OR --Head of HR by delegate
            EXISTS(SELECT HRCODE FROM SYS_SETTING WHERE HRCODE=@DELCODE) --HR Assistant by delegate
        BEGIN
            SET @ACCESS_ALL_EMP=1--THIS EMPLOYEE, ALLOW TO ACCESS ALL OF EMPLOYEES      
            PRINT 'VIEW ALL'    
        END
        ELSE 
        BEGIN               
            PRINT 'VIEW BY ROLES'
            --EMPLOYEE CONDITION        
            SET @MYSCRIPT+= ' AND (HODCODE=' + CAST(@LINEID AS VARCHAR(10))
            IF @LINEID_DELEGATE>0
                SET @MYSCRIPT+= ' OR HODCODE=' + CAST(@LINEID_DELEGATE AS VARCHAR(10))      
            IF EXISTS(SELECT CODE FROM HR_DEPARTMENT WHERE  DM1=@EMPCODE OR DM2=@EMPCODE OR DM3=@EMPCODE)--IF HEAD OF DEPARTMENTS
            BEGIN
                EXEC GET_VIEW_BY_DEPT @EMPCODE,@VIEW_BY_DEPT OUTPUT
                SET @MYSCRIPT+= ' OR DEPT IN (' + @VIEW_BY_DEPT + ')'
            END     
            IF EXISTS(SELECT CODE FROM HR_DEPARTMENT WHERE (@DELCODE<>'' AND @DELCODE IS NOT NULL) AND (DM1=@DELCODE OR DM2=@DELCODE OR DM3=@DELCODE))--IF HEAD OF DEPARTMENTS DELEGATE USER
            BEGIN
                EXEC GET_VIEW_BY_DEPT @EMPCODE,@VIEW_BY_DEPT_DELEGATE OUTPUT
                SET @MYSCRIPT+= ' OR DEPT IN (' + @VIEW_BY_DEPT_DELEGATE + ')'
            END
            SET @MYSCRIPT+=')'
        END
        -----USER FILES
        IF @FILTER_EMPCODE<>'' AND @FILTER_EMPCODE IS NOT NULL--employee code
            SET @MYSCRIPT+= ' AND EMPCODE LIKE ''%' +  @FILTER_EMPCODE + '%'''          
        IF @FILTER_EMPNAME<>'' AND @FILTER_EMPNAME IS NOT NULL--employee name
            SET @MYSCRIPT+= ' AND ALLNAME LIKE ''%' +  @FILTER_EMPCODE + '%'''  
        PRINT @MYSCRIPT
    END

Answer 1

您正在调用 SQL 函数，而不是 SELECT。而不是:

$script = DB::select("exec [dbo].[CHECK_ACCESS_ALL_EMP] '".$emp_code."', '','','','','',''");

尝试:

$script = DB::raw("exec [dbo].[CHECK_ACCESS_ALL_EMP] '".$emp_code."', '','','','','',''");