April 23, 2009 at 12:00 pm
--'Hi,
--
--I want to write a procedure that calls one of two select statements depending on a parameter. For example.......'
CREATE PROCEDURE x
@Section int
AS
BEGIN
case @Section
when 1 then
Select Date, Customer, SalesAmount from Sales
When 2 then
Select date, vendor, PurchaseAmount, Tax from Purchases
end
END
GO
--'It seems it should be easy, but I do not have alot of experience with this.
--
--Thanks,
--
--Mike'
April 23, 2009 at 12:06 pm
looks like you're pretty close.
CREATE PROCEDURE x
@Section int
AS
BEGIN
if @Section=1
begin
Select Date, Customer, SalesAmount from Sales
end
else
if @Section=2
begin
Select date, vendor, PurchaseAmount, Tax from Purchases
end
END
GO
April 23, 2009 at 3:05 pm
Important distinction to remember in SQL:
In SQL, IF is a control statement, used for branching logic. It controls whether certain code executes or not. You use it in multi-statement queries, functions, and stored procedures.
In SQL, CASE is an expression, no different from a constant, a column, a variable, a formula such as [amt]+1, or a function such as substring(@x,3,1). I generally think of it as a function like ISNULL(), just immensely more powerful. You use CASE within a SELECT, INSERT, or UPDATE statement.
In SQL, CASE returns a value. There may be IF logic going on under the covers but it doesn't pass beyond the borders of the CASE...END. CASE chooses between two or more expressions based on the tests you establish, but the code following each WHEN test must itself be an expression (column, function, variable, formula, constant, or even another, nested, CASE).
-- IF controls logic flow, choosing between two selects
IF @Year = 1999
BEGIN
SELECT 'Party like there is no tomorrow.'
END
ELSE
BEGIN
SELECT 'Get back to work.'
END
-- CASE is an expression, used with a single select to test the value of a column for each row
SELECT CASE WHEN [year] = 1999 THEN 'Party like there is no tomorrow.'
ELSE 'Get back to work.'
END
FROM sometable
Good luck 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply