Personally I would consider doing like so.

Replace all occurrances of @DateHolder with

DATEADD(ss,-1,DATEADD(d,DATEDIFF(d,0,GETDATE()+1),0))

in your query code and get rid of the variable declaration.

Replace @tdate with

GETDATE()

and get rid of variable declaration.

Not really sure you need SET DATEFORMAT so say good-bye to it.

Then try this.

CREATE PROCEDURE GetFrontDeskData;1

	@PracticeID bigint,

	@LocationID bigint,

	@Mode nvarchar(25)

AS



SET NOCOUNT ON -- Does save some time and data.



/* Note here, I prefer to group my SPs which is what the ;n represents 1 is always first and is called by default when you call EXEC SPNAME without the ;n. You could give each their own name and if you plan to use on other screens may be easier to keep up with. */

if @Mode = 'FD_DAILYREGISTER'

	exec GetFrontDeskData;2 @PracticeID, @LocationID

else if @Mode = 'FD_APPTREGISTER'

	exec GetFrontDeskData;3 @PracticeID, @LocationID

else if @Mode = 'FD_OPENCHARTS'

	exec GetFrontDeskData;4 @PracticeID, @LocationID

else if @Mode = 'FD_OPENTICKETS'

	exec GetFrontDeskData;5 @PracticeID, @LocationID

else if @Mode = 'FD_OPENCHARGETICKETS'

	exec GetFrontDeskData;6 @PracticeID  /* Note I didn't carefully check all, but don't create and pass unneeded variables. Saves some minor memory overhead. */

GO



/* Note: Always format your code for best readability, saves time in the future when you need to review. */

/* This is the procdure that handles FD_DAILYREGISTER. */

CREATE PROCEDURE GetFrontDeskData;1

	@PracticeID bigint,

	@LocationID bigint

AS



SET NOCOUNT ON



SELECT

	/* Note your columns here. */

FROM 

	appt

INNER JOIN 

	patient 

	INNER JOIN /* It is easier to read and understand realtionships if you do like so when related. May also have performance impact on occasion. */

		alert 

	ON 

		patient.alertid = alert.alertid

ON 

	appt.patientid = patient.patientid

INNER JOIN 

	status 

ON 

	appt.statusid = status.statusid

INNER JOIN 

	room 

ON 

	appt.roomid = room.roomid

INNER JOIN 

	problem 

ON 

	appt.problemid = problem.problemid

INNER JOIN 

	location 

ON 

	appt.locationid = location.locationid

INNER JOIN 

	provider 

ON 

	appt.providerid = provider.providerid

INNER JOIN 

	pcdr 

ON 

	appt.pcdrid = pcdr.pcdrid

INNER JOIN 

	stype 

ON 

	appt.stypeid = stype.typeid

INNER JOIN 

	userx 

ON 

	appt.userid = userx.userid

INNER JOIN 

	payment AS p1 

ON 

	appt.paymentid1 = p1.paymentid

INNER JOIN 

	payment AS p2 

ON 

	appt.paymentid2 = p2.paymentid

INNER JOIN 

	payment AS p3 

ON 

	appt.paymentid3 = p3.paymentid

INNER JOIN 

	payment AS p4 

ON 

	appt.paymentid4 = p4.paymentid

INNER JOIN 

	payment AS p5 

ON 

	appt.paymentid5 = p5.paymentid

WHERE -- The where cluase related to FD_DAILYREGISTER

	appt.type = 'A' and 

	appt.practiceid = @PracticeID and 

	appt.locationid = @LocationID and 

	(

		status.statusid = -1 or 

		status.daily_register = 1

	) and 

	appt.open_ticket = 'O' and 

	appt.apptdate <= DATEADD(ss,-1,DATEADD(d,DATEDIFF(d,0,GETDATE()+1),0)) and 

	(

		appt.deleted <> 'Y' or 

		appt.deleted IS NULL

	)

GO



/* This is the procdure that handles FD_APPTREGISTER. */

CREATE PROCEDURE GetFrontDeskData;3

	@PracticeID bigint,

	@LocationID bigint

AS



SET NOCOUNT ON



/*Same query as before only where clause changes. In this instance based on @mode = FD_APPTREGISTER items.*/

GO



/* And so on....*/





/* This is the procdure that handles FD_OPENCHARGETICKETS. */

CREATE PROCEDURE GetFrontDeskData;6

	@PracticeID bigint

	/* Note @LocationID not needed in query so I don't even create parameter position for. */

AS



SET NOCOUNT ON



/*Same query as before only where clause changes. In this instance based on @mode = FD_OPENCHARGETICKETS items.*/

WHERE

	appt.type IN ('A','O') and /* <<<<<<< Note here I use IN to combine OR statements, IN is more optimized than individual ORs when related as was. */

	substring(stype.code,1,1) <> '*' and 

	appt.practiceid = @PracticeID and 

	status.open_tickets = 1 and 

	appt.open_ticket = 'O' and 

	appt.apptdate <= DATEADD(ss,-1,DATEADD(d,DATEDIFF(d,0,GETDATE()+1),0)) and 

	(

		appt.deleted <> 'Y' or 

		appt.deleted IS NULL

	)

GO

I know you stated you needed all fields. If that was so you could combine work and individual modes do not need all fields do consider dropping unneeded columns and handle on the client side dealing without them on a combined screen.

You should see a performance gain with seperate SPs (even grouped they are still considered to the engine as seperate) because each will have a more optimal query plan that can be properly utilized when calling.