--exec Temp_proc a--EXEC LOG1 'S','','d'ALTER PROC LOG1
(
@USER VARCHAR(64),
@OFFICE VARCHAR(64),
@TIME VARCHAR(64)
)AS--select * from LOG2 WHERE @USER=USER AND @OFFICE=OFFICE AND @TIME=TIME DECLARE @STRING VARCHAR(3000),
@SUBSTRING VARCHAR(3000),
@RUNSTRING NVARCHAR(3000),
@USER1 VARCHAR(64),
@OFFICE1 VARCHAR(64),
@TIME1 VARCHAR(64)SET @USER1=@USER
SET @USER1='AND @USER=USER '
SET @OFFICE1='AND @OFFICE=OFFICE '
SET @TIME1='AND @TIME=TIME 'IF @USER=''
SET @USER1=''
IF @OFFICE=''
SET @OFFICE1=''
IF @TIME=''
SET @TIME1=''SET @STRING=@USER1+@OFFICE1+@TIME1
SET @SUBSTRING=SUBSTRING(@STRING,4,1000)
SET @RUNSTRING=' select * from LOG2 WHERE ' +@SUBSTRING
PRINT(@RUNSTRING)
--EXEC ( @RUNSTRING)
exec sp_executesql @RUNSTRING执行的时候提醒我必须声明变量@USER,请问怎么才能够解决这样的问题
(
@USER VARCHAR(64),
@OFFICE VARCHAR(64),
@TIME VARCHAR(64)
)AS--select * from LOG2 WHERE @USER=USER AND @OFFICE=OFFICE AND @TIME=TIME DECLARE @STRING VARCHAR(3000),
@SUBSTRING VARCHAR(3000),
@RUNSTRING NVARCHAR(3000),
@USER1 VARCHAR(64),
@OFFICE1 VARCHAR(64),
@TIME1 VARCHAR(64)SET @USER1=@USER
SET @USER1='AND @USER=USER '
SET @OFFICE1='AND @OFFICE=OFFICE '
SET @TIME1='AND @TIME=TIME 'IF @USER=''
SET @USER1=''
IF @OFFICE=''
SET @OFFICE1=''
IF @TIME=''
SET @TIME1=''SET @STRING=@USER1+@OFFICE1+@TIME1
SET @SUBSTRING=SUBSTRING(@STRING,4,1000)
SET @RUNSTRING=' select * from LOG2 WHERE ' +@SUBSTRING
PRINT(@RUNSTRING)
--EXEC ( @RUNSTRING)
exec sp_executesql @RUNSTRING执行的时候提醒我必须声明变量@USER,请问怎么才能够解决这样的问题
create table log2(name varchar(10),rights varchar(10),time datetime)
insert log2 select 'user','office','2006-01-13'
--查询测试数据
select * from log2
--创建存储过程
create PROC LOG1
(
@USER VARCHAR(64),
@OFFICE VARCHAR(64),
@TIME VARCHAR(64)
)AS
--print @user--调试语句
--print @office--调试语句
--print @time--调试语句--select * from LOG2 WHERE @USER=USER AND @OFFICE=OFFICE AND @TIME=TIME DECLARE @STRING VARCHAR(3000),
@SUBSTRING VARCHAR(3000),
@RUNSTRING NVARCHAR(3000),
@USER1 VARCHAR(64),
@OFFICE1 VARCHAR(64),
@TIME1 VARCHAR(64)SET @USER1=@USER
SET @USER1='AND '+@USER+'=''USER'' '
SET @OFFICE1='AND '+@OFFICE+'=''OFFICE'' '
SET @TIME1='AND'+ @TIME+'=''TIME'' 'IF @USER=''
SET @USER1=''
IF @OFFICE=''
SET @OFFICE1=''
IF @TIME=''
SET @TIME1=''SET @STRING=@USER1+@OFFICE1+@TIME1
SET @SUBSTRING=SUBSTRING(@STRING,4,1000)
--print @substring--调试语句SET @RUNSTRING=' select * from LOG2 WHERE ' +@SUBSTRING
--PRINT(@RUNSTRING)--调试语句
--EXEC ( @RUNSTRING)
exec sp_executesql @RUNSTRING--执行存储过程EXEC LOG1 'name','rights',''