USE [SAP]
GO
/****** Object:  StoredProcedure [dbo].[SP_EMT_APP_STORE_AUTH]    Script Date: 08/29/2012 12:17:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[SP_EMT_APP_STORE_AUTH]
@COMP_CODE CHAR(4),
@BADGE CHAR(8),
@OBJ_ID CHAR(10),
@VAL VARCHAR(50),
@HAS_AUTH bit OUTPUT
AS
declare @LEN int 
declare @SQL NVARCHAR(500)
declare @STR varchar(200)
declare @flag varchar(5)
set @LEN=len(@VAL)
set @STR=' (VAL='+ @VAL+' OR '
WHile (@LEN>0)
begin
    set @LEN=@LEN-1 
    set @STR=@STR+' VAL='+left(@VAL,@LEN)+'* OR '
end
set @STR=' 1<>1)'

set @SQL=' select @flag=''1'' from MT_GRP_LIST G INNER JOIN MT_GRP_OBJ AS O ON G.GRP_ID = O.GRP_ID'
set @SQL=@SQL+' INNER JOIN MT_AUTH_GRP_DETAIL AS M ON G.GRP_ID =M.GRP_ID'
set    @SQL=@SQL+' WHERE BADGE = @BADGE AND COMP_CODE = @COMP_CODE AND OBJ_ID = @OBJ_ID'
set    @SQL=@SQL+' AND'+'('+ @STR+')'
execute sp_executesql @SQL,N'@flag varchar output',@BADGE,@COMP_CODE,OBJ_ID,@flag output
IF(@flag=1)
   SET   @HAS_AUTH = 1
ELSE 
   SET @HAS_AUTH=0

ALTER PROCEDURE [dbo].[Sp_emt_app_store_auth] 
@COMP_CODE CHAR(4),
@BADGE     CHAR(8),
@OBJ_ID    CHAR(10),
@VAL       VARCHAR(50),
@HAS_AUTH  BIT OUTPUT
AS
    DECLARE @LEN INT
    DECLARE @SQL NVARCHAR(500)
    DECLARE @STR VARCHAR(200)
    DECLARE @flag VARCHAR(5)

    SET @LEN=Len(@VAL)
    SET @STR=' (VAL=' + @VAL + ' OR '

    WHILE ( @LEN > 0 )
      BEGIN
          SET @LEN=@LEN - 1
          SET @STR=@STR + ' VAL=' + LEFT(@VAL, @LEN) + '* OR '
      END

    SET @STR=' 1<>1)'
    SET @SQL=' select @flag=''1'' from MT_GRP_LIST G INNER JOIN MT_GRP_OBJ AS O ON G.GRP_ID = O.GRP_ID'
    SET @SQL=@SQL
             + ' INNER JOIN MT_AUTH_GRP_DETAIL AS M ON G.GRP_ID =M.GRP_ID'
    SET @SQL=@SQL
             + ' WHERE BADGE = @BADGE AND COMP_CODE = @COMP_CODE AND OBJ_ID = @OBJ_ID'
    SET @SQL=@SQL + ' AND' + '(' + @STR + ')'

    EXECUTE Sp_executesql
      @SQL,
      N'@flag varchar output,@BADGE CHAR(8),@COMP_CODE CHAR(4),@OBJ_ID CHAR(10),@flag VARCHAR(5)',-----把你的变量都在这里定义，这是动态SQL 调用 Sp_executesql 存储过程的语法规则。如果是 exec(@sql) 就不需要了
      @BADGE,
      @COMP_CODE,
      @OBJ_ID,
      @flag output

    IF( @flag = 1 )
      SET @HAS_AUTH = 1
    ELSE
      SET @HAS_AUTH=0

------解决方案--------------------
SQL code
    EXECUTE Sp_executesql
      @SQL,
      N'@BADGE CHAR(8),@COMP_CODE CHAR(4),@OBJ_ID CHAR(10),@flag varchar output',-----把你的变量都在这里定义，这是动态SQL 调用 Sp_executesql 存储过程的语法规则。如果是 exec(@sql) 就不需要了
      @BADGE,
      @COMP_CODE,
      @OBJ_ID,
      @flag output
-----参数的先后顺序也须保持一致

------解决方案--------------------

SQL code
execute sp_executesql @SQL,N'@flag varchar output, @COMP_CODE CHAR(4),@BADGE CHAR(8),@OBJ_ID CHAR(10),@VAL VARCHAR(50),',@BADGE,@COMP_CODE,@OBJ_ID,@flag OUTPUT

------解决方案--------------------
SQL code
  SET @STR=' 1<>1)'

------解决方案--------------------
try

SQL code
ALTER PROCEDURE [dbo].[Sp_emt_app_store_auth] 
@COMP_CODE CHAR(4),
@BADGE     CHAR(8),
@OBJ_ID    CHAR(10),
@VAL       VARCHAR(50),
@HAS_AUTH  BIT OUTPUT
AS
    DECLARE @LEN INT
    DECLARE @SQL NVARCHAR(500)
    DECLARE @STR NVARCHAR(200)
    DECLARE @flag VARCHAR(5)

    SET @LEN=Len(@VAL)
    SET @STR=N' (VAL=@VAL OR '

    WHILE ( @LEN > 0 )
      BEGIN
          SET @LEN=@LEN - 1
          SET @STR=@STR + N' VAL=LEFT(@VA