如何针对以下情况编写MS SQL语句？
我有一个允许用户输入日期（fromDate＆toDate）和ID（fromID＆toID）范围的表格。对于所有字段，这些字段都可以为空白，或者仅输入from或to字段，或者输入from和to字段。选择基于输入的值进行选择。以下是在where子句中检查输入值的条件。
no value entered => skip all conditions
value entered in fromDate only => Date = frDate
value entered in toDate only => Date <= toDate
value entered in both fromDate & toDate => Date between fromDate and toDate
Condition is applied to ID field as well.
任何建议都将受到高度赞赏。提前致谢。
您可以使用动态查询解决您的问题。您的问题尚不清楚。在这里，我给您一个解决方案，它将帮助您解决问题。试试这个：
1.在存储过程中创建动态查询
CREATE PROCEDURE sp_YourSPName
/* Input Parameters */
@FromDate DATETIME ,
@ToDate DATETIME
AS
SET NOCOUNT ON
/* Variable Declaration */
DECLARE @SQLQuery AS NVARCHAR(4000)
DECLARE @ParamDefinition AS NVARCHAR(2000)
/* Build the Transact-SQL String with the input parameters */
SET @SQLQuery = 'Select * From YourTableName where (1=1) '
/* check for the condition and build the WHERE clause accordingly */
IF (@FromDate IS NOT NULL)
AND (@ToDate IS NOT NULL)
SET @SQLQuery = @SQLQuery +
' And (YourDate BETWEEN @FromDate AND @ToDate)'
IF (@FromDate IS NULL)
AND (@ToDate IS NOT NULL)
SET @SQLQuery = @SQLQuery + ' And (YourDate <= @ToDate)'
IF (@FromDate IS NOT NULL)
AND (@ToDate IS NULL)
SET @SQLQuery = @SQLQuery + ' And (YourDate = @FromDate)'
/* Specify Parameter Format for all input parameters included
in the stmt */
SET @ParamDefinition = '@StartDate DateTime,
@EndDate DateTime'
/* Execute the Transact-SQL String with all parameter value's Using sp_executesql Command */
EXECUTE sp_Executesql @SQLQuery,
@ParamDefinition,
@FromDate,
@ToDate
IF @@ERROR <> 0
GOTO ErrorHandler
SET NOCOUNT OFF
RETURN(0)
ErrorHandler :
RETURN(@@ERROR)
GO
2.执行存储过程：
EXEC sp_YourSPName '01 Oct 2018', '01 Oct 2018'
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句