SQL Server IF中的条件语句...否则 [英] Condition Statement in SQL Server IF...Else
问题描述
Hey Guys,
我正在尝试在SQL Server 2008 R2中创建一个存储过程
这需要3个参数(AnalyserID,Date_From,Date_To)
实际上,用户可能不会发送三个参数
所以,当执行存储过程时,它可能有1个参数值或2或3个或根本没有任何参数。
我将存储过程设计为Follows
CREATE PROCEDURE [dbo]。[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@ p_AnalyserID INT ,
@ p_Date_From 日期时间,
@ p_Date_To 日期时间
AS
DECLARE @ query varchar ( 2000 );
SET @ query = ' SELECT * FROM T_ANALYSER_RESULTS WHERE T_ANALYSER_RESULTS.AnalysersID =' + @ p_AnalyserID
IF @ p_Date_From 不 null 那么
@query = @ query + ' 并且T_ANALYSER_RESULTS.ResultDate>' + @ p_Date_From
END IF 跨度>;
IF @ p_Date_To 不 null 那么
< span class =code-sdkkeyword> @ query = @ query + ' T_ANALYSER_RESULTS.ResultDate< ' + @ p_Date_To
END IF 跨度>;
EXEC ( @ query )
但是我收到语法错误
消息156,级别15,状态1,过程SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date,第8行
关键字'THEN'附近的语法不正确。
消息156,等级15,状态1,过程SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date,行11
关键字'THEN附近的语法不正确'。
任何想法PlZ ????
紧急!!
我在程序中做了一些更改..
创建 PROCEDURE [dbo]。[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@ p_AnalyserID INT ,
@ p_Date_From Dateti我,
@ p_Date_To 日期时间
AS
DECLARE @ query varchar ( 2000 );
SET @ query = ' SELECT * FROM T_ANALYSER_RESULTS WHERE T_ANALYSER_RESULTS.AnalysersID =' + 转换( varchar ( 30 ), @ p_AnalyserID )
IF @ p_Date_From 不 null
开始
set @ query = @ query + ' 和T_ANALYSER_RESULTS.ResultDate>' + 转换( varchar ( 50 ), @ p_Date_From )
END
IF @ p_Date_To 不 null
开始
set @ query = @ query + ' 和T_ANALYSER_RESULTS.ResultDate< ' + convert ( varchar ( 50 ), @ p_Date_To )
END
打印( @ query )
EXEC ( @ query )
注意:如果参数数据不可用,你应该通过NULL至少(输入到程序)
类似
select *
来自T_ANALYSER_RESULTS
其中AnalysersID = IsNull(@p_analyserID,AnalysersID)
和ResultDate> = IsNull(@ p_Date_from,ResultDate)
和ResultDate< = IsNull(@ p_Date_to,ResultDate)
在SP中创建和执行查询就像打败SP一样第一个位置。
这是正确答案
ALTER PROCEDURE [dbo]。[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@ p_AnalyserID INT ,
@ p_Date_From datetime = null ,
@ p_Date_To datetime = null
AS
DECLARE @ query varchar ( 2000 );
SET @ query = ' SELECT * FROM T_ANALYSER_RESULTS WHERE 1 = 1'
IF @ p_AnalyserID 不 null BEGIN
set @query = @ query + ' AND T_ANALYSER_RESULTS.AnalysersID =' + 转换( varchar ( 30 ), @ p_AnalyserID )
END
IF @ p_Date_From 不 null BEGIN
set @ query = @ query + ' 和T_ANALYSER_RESULTS.ResultDate> ''' + CONVERT ( varchar ( 50 ), @ p_Date_From )+ ' '''
END ;
IF @ p_Date_To 是 不 null BEGIN
set @ query = @ query + ' AND T_ANALYSER_RESULTS.ResultDate< ''' + CONVERT ( varchar ( 50 ), @ p_Date_To )+ ' '''
END ;
PRINT @ query
EXEC ( @ query )
Hey Guys,
I am trying to make a Stored Procedure in SQL Server 2008 R2
That Takes 3 Parameters (AnalyserID,Date_From,Date_To)
Actually, The User Might not send the three parameters
so, When the stored procedure is executed it might have 1 parameter value or 2 or three or not taken any parameters at all.
I designed the stored procedure as Follows
CREATE PROCEDURE [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@p_AnalyserID INT,
@p_Date_From Datetime,
@p_Date_To Datetime
AS
DECLARE @query varchar(2000);
SET @query = 'SELECT * FROM T_ANALYSER_RESULTS WHERE T_ANALYSER_RESULTS.AnalysersID = ' + @p_AnalyserID
IF @p_Date_From is not null THEN
@query = @query + ' And T_ANALYSER_RESULTS.ResultDate >' + @p_Date_From
END IF;
IF @p_Date_To is not null THEN
@query = @query + ' T_ANALYSER_RESULTS.ResultDate < ' + @p_Date_To
END IF;
EXEC(@query)
But I am getting Syntax Error
Msg 156, Level 15, State 1, Procedure SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date, Line 8
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date, Line 11
Incorrect syntax near the keyword 'THEN'.
Any Ideas PlZ????
Its Urgent!!
I have made some changes in the procedure..
Create PROCEDURE [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date] @p_AnalyserID INT, @p_Date_From Datetime, @p_Date_To Datetime AS DECLARE @query varchar(2000); SET @query = 'SELECT * FROM T_ANALYSER_RESULTS WHERE T_ANALYSER_RESULTS.AnalysersID = ' + Convert(varchar(30),@p_AnalyserID) IF @p_Date_From is not null Begin set @query = @query + ' And T_ANALYSER_RESULTS.ResultDate >' + convert(varchar(50),@p_Date_From) END IF @p_Date_To is not null Begin set @query = @query + ' And T_ANALYSER_RESULTS.ResultDate < ' + convert(varchar(50),@p_Date_To) END Print(@query) EXEC(@query)
Note: if the parameter data not available , u should pass "NULL" at least(input to procedure)
something like
select * from T_ANALYSER_RESULTS Where AnalysersID = IsNull(@p_analyserID, AnalysersID) And ResultDate >= IsNull(@p_Date_from, ResultDate) And ResultDate <= IsNull(@p_Date_to, ResultDate)
Creating and executing a query in a SP like you have is defeating the point of having an SP in the first place somewhat.
Thanks to you all Guys
This is the right Answer
ALTER PROCEDURE [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date] @p_AnalyserID INT, @p_Date_From datetime = null, @p_Date_To datetime = null AS DECLARE @query varchar(2000); SET @query = 'SELECT * FROM T_ANALYSER_RESULTS WHERE 1=1 ' IF @p_AnalyserID is not null BEGIN set @query = @query + ' AND T_ANALYSER_RESULTS.AnalysersID = ' + Convert(varchar(30),@p_AnalyserID) END IF @p_Date_From is not null BEGIN set @query = @query + ' And T_ANALYSER_RESULTS.ResultDate > ''' + CONVERT(varchar(50),@p_Date_From) + '''' END; IF @p_Date_To is not null BEGIN set @query = @query + ' AND T_ANALYSER_RESULTS.ResultDate < ''' + CONVERT(varchar(50),@p_Date_To) + '''' END; PRINT @query EXEC(@query)
这篇关于SQL Server IF中的条件语句...否则的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!