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帶入參數運行時提示:Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@BADGE".(1 row(s) affected)(1 row(s) affected)
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帶入參數運行時提示:Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@BADGE".(1 row(s) affected)(1 row(s) affected)
@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,
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
-----参数的先后顺序也须保持一致
有错吧 循环就没用了
@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(@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,
N'@VAL 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 优选它
*/
1. SET @STR=' 1<>1)'是我疏忽了,應該是 SET @STR=@STR+'1<>1)'
2.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
的確要這樣寫。