sql 錯誤, 在綫等！解决办法

USE [SAP]GO/****** Object:  StoredProcedure [dbo].[SP_EMT_APP_STORE_AUTH]    Script Date: 08/29/2012 12:17:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [dbo].[SP_EMT_APP_STORE_AUTH]@COMP_CODE CHAR(4),@BADGE CHAR(8),@OBJ_ID CHAR(10),@VAL VARCHAR(50),@HAS_AUTH bit OUTPUTASdeclare @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 @[email protected]     set @[email protected]+' VAL='+left(@VAL,@LEN)+'* OR 'endset @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 @[email protected]+' INNER JOIN MT_AUTH_GRP_DETAIL AS M ON G.GRP_ID =M.GRP_ID'set    @[email protected]+' WHERE BADGE = @BADGE AND COMP_CODE = @COMP_CODE AND OBJ_ID = @OBJ_ID'set    @[email protected]+' AND'+'('+ @STR+')'execute sp_executesql @SQL,[email protected] varchar output',@BADGE,@COMP_CODE,OBJ_ID,@flag outputIF(@flag=1)   SET   @HAS_AUTH = 1ELSE    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 OUTPUTAS    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 @[email protected] - 1          SET @[email protected] + ' 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 @[email protected]             + ' INNER JOIN MT_AUTH_GRP_DETAIL AS M ON G.GRP_ID =M.GRP_ID'    SET @[email protected]             + ' WHERE BADGE = @BADGE AND COMP_CODE = @COMP_CODE AND OBJ_ID = @OBJ_ID'    SET @[email protected] + ' AND' + '(' + @STR + ')'    EXECUTE Sp_executesql      @SQL,      [email protected] 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,      [email protected] 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,[email protected] 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 OUTPUTAS    DECLARE @LEN INT    DECLARE @SQL NVARCHAR(500)    DECLARE @STR NVARCHAR(200)    DECLARE @flag VARCHAR(5)    SET @LEN=Len(@VAL)    SET @STR=N' ([email protected] OR '    WHILE ( @LEN > 0 )      BEGIN          SET @[email protected] - 1          SET @[email protected] + N' VAL=LEFT(@VAL, @LEN) * OR 1<>1)'      END    SET @SQL=N' select @flag=''1'' from MT_GRP_LIST G INNER JOIN MT_GRP_OBJ AS O ON G.GRP_ID = O.GRP_ID                                     INNER JOIN MT_AUTH_GRP_DETAIL AS M ON G.GRP_ID =M.GRP_ID                                       WHERE BADGE = @BADGE AND COMP_CODE = @COMP_CODE AND OBJ_ID = @OBJ_ID                                      AND (@STR)'    EXECUTE Sp_executesql     @SQL,      [email protected] VARCHAR(50),@LEN INT,@flag varchar output,        @BADGE CHAR(8),@COMP_CODE CHAR(4),@OBJ_ID CHAR(10),        @STR NVARCHAR(200)',-----把你的变量都在这里定义，这是动态SQL 调用 Sp_executesql 存储过程的语法规则。如果是 exec(@sql) 就不需要了    @VAL,    @LEN,    @flag OUTPUT,    @BADGE,    @COMP_CODE,    @OBJ_ID          IF( @flag = 1 )      SET @HAS_AUTH = 1    ELSE      SET @HAS_AUTH=0 /*用 Sp_executesql 执行 动态SQL  变量类型定义为 NVARCHAR型，在是语法要求。 其他的逻辑 自己调试吧。EXEC() 这种 动态执行 SQL 每次都编译生成计划。而 Sp_executesql 这种第一次编译后的计划会被 缓存起来供以后使用。效率 要告 调试性也比 前一种好。执行动态SQL  优选它*/