[AccessD] Raising Error in Function

I don't think you can raise an error in a function.

Why don't you you do the case statement in the actual sproc or view?

I'd actually make a table and left join it in.

I have a sproc that actually takes an input parameter delimited by a
character and returns a table.
You can use something like that, but I still think a real table would work
better.

Here's the function that I was talking about:

CREATE FUNCTION [dbo].[udfListToTable]
(@HList VarChar(1000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Mystr VARCHAR(20))
AS
BEGIN
    --Purpose: To convert a Comma delimited text to a Temp Variable table
To help avoid dynamic sql
    --         Instead you can join the temp table or use it in your where
clause if a field is IN the subquery
    DECLARE @Mystrtext as VarChar(20)

    IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
        SET @HList = @HList + @Delimiter

    WHILE CHARINDEX(@Delimiter, @HList) > 0
    BEGIN
        IF CHARINDEX(@Delimiter, @HList) > 0
           BEGIN
            SELECT @Mystrtext =
LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
           END
        ELSE
           BEGIN
             SELECT @Mystrtext = RTRIM(LTRIM(@HList))
           END
        --Insert into Variable Table
        INSERT INTO @ListTable(Mystr)
               SELECT RTRIM(LTRIM(@Mystrtext))
        --Remove Item from list
        SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) -
CHARINDEX(@Delimiter, @HList))
    END
    RETURN
END


--Call the function in this manner:
--SELECT * FROM dbo.udfListToTable('jim,joe,bob,tom',',')



On Fri, Nov 9, 2012 at 2:44 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> No replies from the SQL group so I thought I would try here.
>
>
>