[AccessD] Raising Error in Function

Thanks for the feedback.

The reason for not including it direct in the sproc is that it applies a
number of places in the database and having it in one place means that there
is only one place to update when new items are added.

David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Saturday, 10 November 2012 12:03 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [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.
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com