Problem With Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem With Function

Author

Topic

martes
Starting Member

13 Posts

Posted - 2011-06-08 : 05:07:37

I have just written my first function, and surprise, it failed. It is a very simple function, start simple, see below.

The error occurs at the CASE statement. I have one case commented out as the data is not there yet for that case.

Can anyone see the problem?

TIA

CREATE FUNCTION [dbo].[ufn_GetFacilityPurpose](
@Application as nvarchar(max),
@LocalRefBL as nvarchar(max),
@LocalRefLD as nvarchar(max)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @me as nvarchar(100)
DECLARE @myparms as nvarchar(500)

DECLARE @returnstring as nvarchar(max)
DECLARE @workstring as nvarchar(max)

-- ------------------------------------------------------------------------------------------
-- procedure name and version
-- ------------------------------------------------------------------------------------------
SET @me = OBJECT_NAME(@@PROCID) + N' (0001.02) '

SET @myparms = @Application + ', ' + @LocalRefBL + ', ' + @LocalRefLD

SET @returnstring = ''

SET @Applicatiion = LTRIM(RTRIM(@Application)

CASE

-- WHEN @Application = 'AC" THEN @returnstring = 'Ref-Accounts'

WHEN @Application = 'BL' THEN @returnstring = 'Ref-Bills'

WHEN @Application = 'LD' THEN @returnstring = 'Ref-Loans'
END

RETURN @returnstring

END

GO

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-06-08 : 05:19:15

CASE

should be:

SELECT CASE

martes
Starting Member

13 Posts

Posted - 2011-06-08 : 05:30:44

I wondered that, but thought it was part of the overall query.

Anyway, I tried it, and it errors on SELECT now. :-(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-08 : 05:33:29

see this example

create function dbo.myfunc(@inString varchar(255))
returns varchar(255)
as
begin
return case when @inString = 'Fred' then 'firstname'
            when @inString = 'webfred' then 'nickname'
            else 'unknown'
       end
end

select dbo.myfunc('Fred')
select dbo.myfunc('webfred')
select dbo.myfunc('blabla')

No, you're never too old to Yak'n'Roll if you're too young to die.

martes
Starting Member

13 Posts

Posted - 2011-06-08 : 05:42:43

Thanks mate. I had a couple of other errors, a mis-spelt variable, and a missing bracket, but your elegant example gave me the right steer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources