1

I have 3 parameters in my table called @StartDate, @EndDate, and @ServerName. I am wanting all 3 of these to have a default value set to ALL.

Users have the ability to choose a StartDate and an EndDate and it will return all stopped services during those dates. I am also wanting them to be able to search for a ServerName and it will return all records of that ServerName no matter when the service stopped.

My problem is searching for a server name. Right now I just have a temporary fix for the parameters which would be that the default of the @StartDate parameter is set to the beginning of the year which is a date that is before any records were logged and the @EndDate is set to the current date. So when the report is opened it shows all the records of stopped services and then the user can adjust the start and end dates as they'd like. But when I search for a ServerName I want it to just find all records of that ServerName but it still just returns ALL the records within the Start and End Dates.

Here is my query:

SELECT ServerName, ServiceName, Status, Date, Time
FROM ServicesStatus
WHERE (Date BETWEEN @StartDate AND @EndDate) OR (ServerName = @ServerName)
ORDER BY Date DESC, Time DESC, ServerName

So what I need is to get the @StartDate and @EndDate to default to all dates so when someone searches for a ServerName it will return all instances of that server in the SQL database.

I also need the same for the parameter @ServerName so when someone search for a time range, it will return with ALL the Server Names that have a stopped service during that time.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
derekg8881
  • 107
  • 7
  • Possible duplicate of [sql query if parameter is null select all](https://stackoverflow.com/questions/13474207/sql-query-if-parameter-is-null-select-all) i.e. `OR (ServerName = IsNull(@ServerName, ServerName))` – JNevill Jul 24 '18 at 18:22
  • Removed the Visual Studio tag as this is a SQL question that has nothing to do with the tool in which the SQL is being written. (Unless there is actually a thing as a Visual Studio Table). – JNevill Jul 24 '18 at 18:28
  • I feel like it does having something to do with Visual Studio since I created those parameters in Visual Studio and the report I created is in Visual Studio but okay... – derekg8881 Jul 24 '18 at 18:52
  • I've added it back. I believe, though, that this is an issue more about how to write the sql to deal with a potentially NULL parameter which is a pretty common SQL parameterization thing. My concern is that it will pull in the wrong "experts" to answer the question, but I could be off base there. – JNevill Jul 24 '18 at 18:55

1 Answers1

2

You can set your default parameter values as NULL with OR operator on where clause.

If the user didn't use any condition, then it will select all datas.

DECLARE @StartDate DATETIME = NULL
DECLARE @EndDate DATETIME  = NULL
DECLARE @ServerName VARCHAR(100) = NULL

SELECT        ServerName, ServiceName, Status, Date, Time
FROM          ServicesStatus
WHERE         
    (Date >= @StartDate OR @StartDate IS NULL) 
AND 
    (Date <= @EndDate OR @EndDate IS NULL)
OR
    (ServerName = @ServerName OR @ServerName IS NULL)
ORDER BY      Date DESC, Time DESC, ServerName

Here is a default parameter sampleFiddle

NOTE

Your default value can be other (like 'ALL') ​that don't have to be NULL. You can default your parameter.

D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • Didn't work. I get an error box saying "Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. Incorrect syntax near '='. Must declare the scalar variable "@StartDate". To continue without updating the fields list, click OK. – derekg8881 Jul 24 '18 at 18:49
  • @derekg8881 I edit my answer. Make sure the parameter datatype equal to your judgment columns. – D-Shih Jul 24 '18 at 18:53