prn 254
Recognized Expert Contributor
Hi folks,
This may sound odd, but I'm preparing a stored procedure that I want to handle a couple of different cases. However, unlike the standard sort of situation where what I want to differentiate is the result, i.e., the selections, what I want to cover is the selection conditions.
I have basically two queries that differ only in a single condition, but I'd like to put them into a single stored proc and parameterize them (much more maintainable). I've boiled this down to the bare essentials, ignoring all the selected fields and the joined tables. The only difference is whether I use [condition1] or [condition2] in the WHERE clause. What I'd like is something like: -
CREATE PROCEDURE get_fubar
-
(
-
@mystring VARCHAR(20),
-
@type CHAR(3)
-
)
-
AS
-
BEGIN
-
SELECT
-
A_LOT_OF_FIELDS
-
FROM
-
MY_TABLES
-
WHERE
-
A_LOT_OF_CONDITIONS AND
-
CASE
-
WHEN @type = 'foo' THEN [condition1]
-
ELSE [condition2]
-
END
-
END
-
GO
-
But I'm afraid I'd have to settle for either two separate procs or else something like: -
CREATE PROCEDURE get_fubar
-
(
-
@mystring VARCHAR(20),
-
@type CHAR(3)
-
)
-
AS
-
BEGIN
-
IF @type = 'foo'
-
BEGIN
-
SELECT
-
A_LOT_OF_FIELDS
-
FROM
-
MY_TABLES
-
WHERE
-
A_LOT_OF_CONDITIONS AND
-
[condition1]
-
END
-
ELSE
-
BEGIN
-
SELECT
-
SOME_FIELDS,
-
FOO,
-
BAR,
-
MORE_FIELDS
-
FROM
-
MY_TABLES
-
WHERE
-
A_LOT_OF_CONDITIONS AND
-
[condition2]
-
END
-
END
-
GO
-
which is not much more maintainable than two procs.
Is there some way to do this? Or would it screw up the server's execution plan so much that it wouldn't be worth it even if it could be done?
Thanks for any insights,
Paul
2 1642 ck9663 2,878
Recognized Expert Specialist
try something like this.... -
-
WHERE (@type = 'foo' and zipcode = '94105') or
-
(@type = 'not food' and statecode = 'NY')
-
-
the two condition can never be both true.
Good Luck!!!
--- CK
prn 254
Recognized Expert Contributor
D'oh!
Thanks, CK! I guess that makes too much sense for me to have thought of it. My logic teachers might be tempted to go back and retroactively flunk me. :)
Thanks,
Paul
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: FHuang |
last post by:
Ok, I'm having some trouble with conditionals, for some reason PHP is screwing them up.
First off, here is the code:
<?php
$username = "fred";
$userdata = "./$username.txt";
$fp = fopen("$userdata", "r", 1);
$origpass = fread($fp, 5);
if($passcount = 1)
{
|
by: Joshua Ginsberg |
last post by:
Is there any plan to include inline conditionals in Python? For example:
def isNegative(x):
return x < 0 ? True : False
Thanks!
-jag
--
|
by: Paul Dale |
last post by:
Hi All,
I know that several of you will probably want to reply "you should write
a parser", and I may. For that matter any tips on theory in that
direction would be appreciated.
However, if you would indulge me in my regex question I would also be
most grateful.
I'm writing an edi parser and to be in compliance with the specification
|
by: steven |
last post by:
Is it possible to combine conditionals to call out data?
I have a list of records that include a date. I can call out all dates
after the date after tomorrow:
IIf(Date()+1)<,...,...
And I can call dates before next week:
IIf(< Date()+7,...,...
But I'd like to isolate those that fall between the day after tomorrow
and next week. I was...
|
by: Leon Lambert |
last post by:
I would appreciate it if someone could help me understand NaN handling
with respect to conditionals in IL code. I am playing with a small IL
interpreter and having a little problem with it. Following is a small
piece of C# code that gets compiled by VS2005 to simulate my problem.
static void Main(string args)
{
Double db1 = 1.0;
Double db2...
| |
by: .Net Sports |
last post by:
I need to convert some C# ?Conditionals over to vb.net (most likely in
vb.net using If..Then statements), but the C#2VBNETconverter by
KamalPatel isn't converting them:
string PurchaseType = (Convert.ToString(drwData) ==
"True") ? "ID" : "PID";
XmlNodeList Selections = xdcData.GetElementsByTagName((X == 0) ?
"Highlight" : "Selection");
|
by: Frederick Gotham |
last post by:
If we have the following expression:
2 < 1
What is the type of the expression, "2 < 1", or any other expression in C++
which involves comparison or inversion? More examples:
4 == 2
!3
|
by: Hul Tytus |
last post by:
comp.lang.c
c programs & shell conditionals
How is a unix shell script made to respond to the value returned
by a program compiled from c code?
The shell script below is my current effort, which doesn't work.
The c code shows what was used to generate a.out, seen in the shell script.
The value returned was varied from 0 to 1 for testing.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |