?
Solved

Stored Procedure with parameters in ASP.Net

Posted on 2014-04-27
1
Medium Priority
?
419 Views
Last Modified: 2014-04-27
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?
0
Comment
Question by:dodgerfan
1 Comment
 
LVL 8

Accepted Solution

by:
Ganapathi earned 2000 total points
ID: 40026495
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question