SQL Server conditional Where in select

I have a select that I want to have a conditional where statement

Passing in a variable 'P' or 'B'

I have a table with 4 columns
id    dText     Bill     Pay
1     Hours     1          0
2     Hrs-Reg  0          1

etc

When @var = 'P'
return  data where Pay = 1

when @var = 'B'
return data where Bill = 1

I have this which works...just wondering if I can streamline it
ALTER PROCEDURE usp_HoursTrackingDetailHoursGet2
			(
				@type VARCHAR(1)
			)
AS
BEGIN
	SET NOCOUNT ON;

		IF @type = 'B'
		BEGIN
			SELECT	hsid ,
					Definition 
			FROM	dbo.HoursTracking_SID_Definitions
			WHERE	Bill = 1
			ORDER BY def DESC, sort ASC
		END
		ELSE
		BEGIN
			SELECT	hsid ,
					Definition 
			FROM	dbo.HoursTracking_SID_Definitions
			WHERE	Pay = 1			
			ORDER BY def DESC, sort ASC
		END
END

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste the below into your SSMS, verify it works how you want, then modify to suit your needs.

CREATE TABLE #foo (id int, dText char(10), bill bit, pay bit)

INSERT INTO #foo (id, dText, bill, pay)
VALUES (1, 'Hours', 1, 0), (2, 'Hrs-Reg', 0, 1)

Declare @var char(1) = 'B'

SELECT id, dText, Bill, Pay
FROM #foo
WHERE (@var = 'P' AND bill=1) OR (@var = 'B' AND pay = 1)

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Perfect!

Remind me to buy you a sody-pop sometime
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.