NursingCorp
asked on
Assistance with stored procedure setting a blank variable to retrieve all
I have a stored procedure with variables being sent to it such as dates, facility, employees, which are used in the final SELECT statement (See snippet). Sometimes an Employee or Facility is not given, because we want to see all employees at a given Facility.
I have tried setting the value of the variable @Employee = '%' but it will not return all. How can I get it to show all if the value of this variable is left blank?
Thanks
I have tried setting the value of the variable @Employee = '%' but it will not return all. How can I get it to show all if the value of this variable is left blank?
Thanks
ALTER PROC [dbo].[spNC_BookingWizard]
@StartDate varchar(10) = '1/1/1900',
@EndDate varchar(10) = '1/1/2010',
@Employee varchar(200) = '',
@Facility varchar(200) = '',
@Status varchar(200) = '',
@test bit = 0
AS
SET NOCOUNT ON
DECLARE @dtStart datetime
DECLARE @dtEnd datetime
SET @dtStart = CONVERT(datetime, @StartDate)
SET @dtEnd = DATEADD(dd, 1, DATEADD(dd, 0, CONVERT(datetime, @EndDate)))
...........
SELECT * FROM #tmpBook
WHERE StartDate_date >= @dtStart AND StartDate_date <= @dtEnd
AND Facility = @Facility
AND EmpName = @Employee
AND BookingStatus = @Status
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Copied and pasted...worked perfect. Thanks
for you, this syntax should work:
Open in new window