Case statement in UDF
-
Hi,
I'm just trying to get my head around the use of case statements in SQL and wondered if someone could point me to the problem with this one. The error message coming back is. "Incorrect syntax near the word Case", succinct but not very helpful
Thanks for your time
K.
CREATE FUNCTION dbo.NewStockLevel
(
@var_st_lev int,
@var_stt_amnt int
)
RETURNS int
AS
BEGIN
DECLARE @var_NSL int
Set @var_NSL= @var_st_lev - @var_stt_amnt
CASE @var_NSL
WHEN @var_NSL < 0
THEN @var_st_lev
END
RETURN @var_NSL
END
-
You can only use case in a query. here you are stuck with the good old if statement :-).
Need help with that one?
-
Try this:
CREATE
FUNCTION dbo.NewStockLevel
(
@var_st_lev
int,
@var_stt_amnt
int
)
RETURNS
int
AS
BEGIN
DECLARE
@var_NSL int
Set
@var_NSL= @var_st_lev - @var_stt_amnt
set
@var_st_lev = CASE WHEN (@var_NSL < 0) THEN @var_st_lev END
RETURN
@var_NSL
END
-
Cool, didn't know that way to use case
. -
Hi, Yes that would be very helpful as this attempt failed with even more errors
Thanks
K.
CREATE FUNCTION dbo.NewStockLevel
(
@var_st_lev int,
@var_stt_amnt int
)
RETURNS int
AS
BEGIN
DECLARE @var_NSL int
if @var_st_lev - @var_stt_amnt < 0 Then
Set @var_NSL= @var_st_lev
else
Set @var_NSL = @var_st_lev - @var_stt_amnt
end if
RETURN @var_NSL
END
-
Thanks Lynn that compiles nicely
Just one question,
Should set @var_st_lev in
set @var_st_lev = CASE WHEN (@var_NSL < 0) THEN @var_st_lev END
be
set @var_NSL = CASE WHEN (@var_NSL < 0) THEN @var_st_lev END
as @var_NSL is the variable i intended to return i.e. RETURN @var_NSL ?
-
I would use Lynn's version since it's shorter, but that'll give you a chance to learn a little more tsql
.If TSQL we use begin and end to delimit code. Then and end if are vb code and not sql
if @var_st_lev - @var_stt_amnt < 0 Then
begin
Set @var_NSL= @var_st_lev
end
else
begin
Set @var_NSL = @var_st_lev - @var_stt_amnt
end
end if
-
Spotted me then
-
RGR'us,
I would rewrite your TSQL like this:
if @var_st_lev - @var_stt_amnt < 0 Then
Set @var_NSL= @var_st_lev
else
Set @var_NSL = @var_st_lev - @var_stt_amnt
Reason, each set is a single statement and the begin end pair isn't required. I would only use the begin end pair if there is more than 1 TSQL statement.
-
Karma,
You may be right on the set statement in the case expression, but that is the only change I'd make.
Thanks,
Lynn
-
That is just personal taste... I don't like to have to add them when I need to change the code... or forget to add 'em and insert a bug in the application.
-
True enough on personal taste. I find it easier partly due to how I also use tabs (converted to spaces) to indent code. I am partially anal when it comes to using white space. I know someone worse than me when it comes to formatting code, but his is quite readable, just a little much for me.
-
I'm a minimalist. The CASE function (it's not a flow-of-control statement) can be used anywhere it is legal to use an expression, so just use it in RETURN and forget DECLARE, IF, and SET.
CREATE FUNCTION dbo.NewStockLevel (@var_st_lev int, @var_stt_amnt int) RETURNS int
BEGIN
RETURN CASE WHEN @var_st_lev > @var_stt_amnt THEN @var_st_lev - @var_stt_amnt ELSE @var_st_lev END
END
This assumes that @var_st_lev is not negative, it may be more robust to use WHEN @var_st_lev - @var_stt_amnt > 0 THEN
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply