case conditional  
Author Message
vIQleS





PostPosted: Thu Jul 22 02:47:21 CDT 2004 Top

SQL Server Developer >> case conditional

ok, i'm at an impase regarding t-sql procedural progamming. I'm trying to
determine whether code certain logic in an stored procedure or a dll.

SP is nice idea maybe cause I can modify it more easily than a dll.

However, I would like to use 'case' instead of 'if ... then' Unfortunately,
I would prefer to use 'case' rather than 'if ... then' and I have been
unable to get a case conditional syntax to work. The 'if ... then'
conditional in an SP for procedural programming in t-sql is simple as
documented on this forum as elsewhere.

My question to you is: " is it possible to use case select type logic
outside of SQL statements for t-sql type procedural programming and if so,
what is the proper syntax?"

I leave discussion of the merits, unless you have a strong opinion to
express, to other posts.

SQL Server16  
 
 
Steve





PostPosted: Thu Jul 22 02:47:21 CDT 2004 Top

SQL Server Developer >> case conditional CASE ... END is an expression in T-SQL, and there is no CASE statement
to control program flow. You will have to find another way to express
what you want to do using one of the control structures in the language,
like IF .. ELSE, GOTO statements, recursion, WHILE, and so on.

Steve Kass
Drew University



>ok, i'm at an impase regarding t-sql procedural progamming. I'm trying to
>determine whether code certain logic in an stored procedure or a dll.
>
>SP is nice idea maybe cause I can modify it more easily than a dll.
>
>However, I would like to use 'case' instead of 'if ... then' Unfortunately,
>I would prefer to use 'case' rather than 'if ... then' and I have been
>unable to get a case conditional syntax to work. The 'if ... then'
>conditional in an SP for procedural programming in t-sql is simple as
>documented on this forum as elsewhere.
>
>My question to you is: " is it possible to use case select type logic
>outside of SQL statements for t-sql type procedural programming and if so,
>what is the proper syntax?"
>
>I leave discussion of the merits, unless you have a strong opinion to
>express, to other posts.
>
>
>
>
 
 
Cowboy





PostPosted: Thu Jul 22 10:41:48 CDT 2004 Top

SQL Server Developer >> case conditional You mean something like

SELECT CASE MyField1 WHEN < 20 THEN 0
ELSE MyField1 END As MyField1Output
FROM SomeTable

If you are manipulating large result sets, it is generally better to set up
the filter, if possible, in T-SQL. If the sets are small, you will likely
find similar perf in each, so pick the one you find easier to maintain. If
the filtering logic is business rules related, you may want to pull it out
into the business layer, regardless. In other words, if business rules
affect how the filter is accomplished, not simply business requires it to be
filtered in a certain manner.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************


> ok, i'm at an impase regarding t-sql procedural progamming. I'm trying to
> determine whether code certain logic in an stored procedure or a dll.
>
> SP is nice idea maybe cause I can modify it more easily than a dll.
>
> However, I would like to use 'case' instead of 'if ... then'
Unfortunately,
> I would prefer to use 'case' rather than 'if ... then' and I have been
> unable to get a case conditional syntax to work. The 'if ... then'
> conditional in an SP for procedural programming in t-sql is simple as
> documented on this forum as elsewhere.
>
> My question to you is: " is it possible to use case select type logic
> outside of SQL statements for t-sql type procedural programming and if so,
> what is the proper syntax?"
>
> I leave discussion of the merits, unless you have a strong opinion to
> express, to other posts.
>
>


 
 
Unit





PostPosted: Thu Jul 22 23:01:57 CDT 2004 Top

SQL Server Developer >> case conditional Cowboy, I understand my use of the term 't-sql' was incorrect. I meant, as I
believe Steve in the previous post understood, to ask about the use of CASE
... WHEN ... ELSE ... END outside of any SQL statement in order to control
the processing of a procedure. The use of IF ... THEN ... ELSE is well
documented, as in:


BEGIN
' some code

BEGIN' some code ENDELSE BEGIN ... END' and so on ...However, I could not
find documentation of the use of CASE ... WHEN ... ELSE ... END in a similar
manner to control SP execution. As Steve confirmed, MS Sql Server does not
support such useage for CASE. I do understand that CASE is used frequently
inside SQL statements.

The above useage of IF ... THEN ... ELSE or as Steve mentioned, "IF .. ELSE,
GOTO statements, recursion, WHILE, and so on", are applicable to control SP
program flow.


--------------------------------------



> You mean something like
>
> SELECT CASE MyField1 WHEN < 20 THEN 0
> ELSE MyField1 END As MyField1Output
> FROM SomeTable
>
> If you are manipulating large result sets, it is generally better to set
up
> the filter, if possible, in T-SQL. If the sets are small, you will likely
> find similar perf in each, so pick the one you find easier to maintain. If
> the filtering logic is business rules related, you may want to pull it out
> into the business layer, regardless. In other words, if business rules
> affect how the filter is accomplished, not simply business requires it to
be
> filtered in a certain manner.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ************************************************
> Think Outside the Box!
> ************************************************


> > ok, i'm at an impase regarding t-sql procedural progamming. I'm trying
to
> > determine whether code certain logic in an stored procedure or a dll.
> >
> > SP is nice idea maybe cause I can modify it more easily than a dll.
> >
> > However, I would like to use 'case' instead of 'if ... then'
> Unfortunately,
> > I would prefer to use 'case' rather than 'if ... then' and I have been
> > unable to get a case conditional syntax to work. The 'if ... then'
> > conditional in an SP for procedural programming in t-sql is simple as
> > documented on this forum as elsewhere.
> >
> > My question to you is: " is it possible to use case select type logic
> > outside of SQL statements for t-sql type procedural programming and if
so,
> > what is the proper syntax?"
> >
> > I leave discussion of the merits, unless you have a strong opinion to
> > express, to other posts.
> >
> >
>
>


 
 
Unit





PostPosted: Thu Jul 22 23:05:46 CDT 2004 Top

SQL Server Developer >> case conditional But, I'll try to use the CASE logic inside the SQL statement to see if it
meets my needs. Thanks




> You mean something like
>
> SELECT CASE MyField1 WHEN < 20 THEN 0
> ELSE MyField1 END As MyField1Output
> FROM SomeTable
>
> If you are manipulating large result sets, it is generally better to set
up
> the filter, if possible, in T-SQL. If the sets are small, you will likely
> find similar perf in each, so pick the one you find easier to maintain. If
> the filtering logic is business rules related, you may want to pull it out
> into the business layer, regardless. In other words, if business rules
> affect how the filter is accomplished, not simply business requires it to
be
> filtered in a certain manner.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ************************************************
> Think Outside the Box!
> ************************************************


> > ok, i'm at an impase regarding t-sql procedural progamming. I'm trying
to
> > determine whether code certain logic in an stored procedure or a dll.
> >
> > SP is nice idea maybe cause I can modify it more easily than a dll.
> >
> > However, I would like to use 'case' instead of 'if ... then'
> Unfortunately,
> > I would prefer to use 'case' rather than 'if ... then' and I have been
> > unable to get a case conditional syntax to work. The 'if ... then'
> > conditional in an SP for procedural programming in t-sql is simple as
> > documented on this forum as elsewhere.
> >
> > My question to you is: " is it possible to use case select type logic
> > outside of SQL statements for t-sql type procedural programming and if
so,
> > what is the proper syntax?"
> >
> > I leave discussion of the merits, unless you have a strong opinion to
> > express, to other posts.
> >
> >
>
>