|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
March 4th, 2004, 04:31 PM
|
|||
|
|||
|
STORED PROCS with optional parameters
I was wondering if anyone could shed some light on this for me....?
I want a user to be able to perform a search on my database. However, I want to make the various search parameters in the stored proc optional. Take the following: CREATE PROCEDURE sp_Accounts_Search_FullSearch @user_age tinyint = NULL, @user_sex varchar (6) = NULL, /* Null as default values */ @user_country varchar (20) = NULL AS SELECT * FROM TBL_ACCOUNTS_USERS u WHERE u.user_age = @user_age AND u.user_sex = @user_sex AND u.user_country = @user_country GO So basically, if user_sex or whatnot is not specified when the stored proc is executed, then it takes a null value. - So I'm basically trying to get it to ignore any parameter that's set to null when performing the search and return the results, regardless..... But it doesn't seem to be working :S Any suggestions? Thanks in advance!
__________________
<%Questions of Science%>
|
|
#2
March 7th, 2004, 12:44 PM
|
||||
|
||||
|
You can use case statements to handle the null parameters, something like this:
Code:
CREATE PROCEDURE sp_Accounts_Search_FullSearch
@user_age tinyint = NULL,
@user_sex varchar (6) = NULL,
/* Null as default values */
@user_country varchar (20) = NULL
AS
SELECT * FROM TBL_ACCOUNTS_USERS u
WHERE u.user_age = CASE WHEN @user_age IS NULL
THEN u.user_age
ELSE @user_age
END
AND u.user_sex = CASE WHEN @user_sex IS NULL
THEN u.user_sex
ELSE @user_sex
END
AND u.user_country = CASE WHEN @user_country IS NULL
THEN u.user_country
ELSE @user_country
END
__________________
Up the Irons What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home. "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest Down with Sharon Osbourne Puzzle of the Month solved by sizeablegrin, etienne141 and L7Sqr, superior C/C++ programmers of the month
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > STORED PROCS with optional parameters |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
