October 4, 2007 at 11:29 am
This is my script
And SQL tell me I'm doing an error
Msg 156, Level 15, State 1, Procedure fn_Get_Day_Late, Line 8
Incorrect syntax near the keyword 'CASE'.
Msg 137, Level 15, State 2, Procedure fn_Get_Day_Late_, Line 10
Must declare the variable '@out_value'.
Msg 137, Level 15, State 2, Procedure fn_Get_Day_Late, Line 10
Must declare the variable '@LOAD_ID_NU'.
Msg 137, Level 15, State 2, Procedure fn_Get_Day_Late, Line 12
Must declare the variable '@out_value'.
CREATE FUNCTION dbo.fn_Get_Day_Late_( @SO_ID_NUMBER AS CHAR(20) , @LOAD_ID_NUMBER AS NUMERIC)
RETURNS int
AS
BEGIN
DECLARE @out_value INT
CASE WHEN @LOAD_ID_NUMBER = 0 THEN @out_value = 0
ELSE
EXEC @out_value = SELECT DAY_LATE FROM JDE_CRP.CRPDTA.GRAYCONDAYLATE WITH (NOLOCK) WHERE SO_NUMBER = @SO_ID_NUMBER AND LOAD_NUMBER = @LOAD_ID_NU
END
RETURN @out_value
END
Anybody can help me please ....
Thanks
André
October 4, 2007 at 2:19 pm
CASE doesn't work that way. CASE is used for inlining simple functions, but cannot encapsulate whole SQL statements (a function call itself, but not an EXEC statement).
Try this instead:
CREATE FUNCTION dbo.fn_Get_Day_Late_( @SO_ID_NUMBER AS CHAR(20) , @LOAD_ID_NUMBER AS NUMERIC)
RETURNS int
AS
BEGIN
DECLARE @out_value INT
If (@LOAD_ID_NUMBER = 0)
BEGIN
set @out_value = 0
end
ELSE
BEGIN
select @out_value = DAY_LATE FROM JDE_CRP.CRPDTA.GRAYCONDAYLATE WITH (NOLOCK) WHERE SO_NUMBER = @SO_ID_NUMBER AND LOAD_NUMBER = @LOAD_ID_NUmber
END
RETURN @out_value
END
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 4, 2007 at 2:22 pm
It's because i'm trying to catch
if parameter = 0 then
I don't need to run SQL statement
Else
Execute SQL to return a value from it
October 4, 2007 at 2:24 pm
Sorry - some of my first post got whacked - check the response above now...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply