Stored Procedure with parameters in ASP.Net

I have a stored procedure in SQL Server I am using to search records and return them for display in an asp.net page (c# code behind). I have 3 parameters I need to use. Owner, Year and Type. By default the page opens to all owners for the current year of both types. Using drop down lists, users can select which owner, what year and which type to return. There are 12 owners, with data from each year for 2009-2014. There are two types to select from. I'm trying to create the stored procedure to select 1 owner OR all owners for a particular year, with either type 1 or type 2. I think I need an if statement or case statement in the stored procedure, but I don't know where to look to find examples. Am I approaching this right, and is there a good example to work with?
dodgerfanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GanapathiFacets DeveloperCommented:
CREATE TABLE TMP(OWNER VARCHAR(10),YEAR_VAL DATETIME, OWNER_TYPE INT)
INSERT INTO TMP SELECT 'OWNR1',GETDATE(),8
INSERT INTO TMP SELECT 'OWNR2',GETDATE(),6
INSERT INTO TMP SELECT 'OWNR3',GETDATE(),4
INSERT INTO TMP SELECT 'OWNR4',GETDATE(),3

DECLARE @TYPE_PARAM INT /* Consider this as the Paramater */
, @OWNER_PARAM CHAR(5)
SELECT @TYPE_PARAM = 2
SELECT @OWNER_PARAM = 'OWNR3'
SELECT
	*
FROM
	TMP
WHERE
   (2 = CASE
   		   WHEN @TYPE_PARAM = 2
		      THEN 2
		   ELSE 1
		END)
OR (@TYPE_PARAM = 1 AND OWNER = @OWNER_PARAM)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.