Error in Function?

Question

question

Dorababu asked May 17, '10

Error in Function?

CREATE FUNCTION pages_Allowed (@username varchar(35))

RETURNS varchar(30)
AS
BEGIN
-- Declare the return variable here
DECLARE @pages varchar(30)

DECLARE @PRvalidat tinyint,@PRdownload tinyint,@PRupload tinyint


-- Add the T-SQL statements to compute the return value here
select @PRvalidat=Rvalidate,@PRdownload=Rdownload,@PRupload=Rupload where Username=@username

select @pages
when @PRvalidate=1 and @PRdownload=1 and @PRupload=1 then @Pages='Admin'
when @PRvalidate=1 and @PRdownload=0 and @PRupload=0 then @pages='Validate Only'
when @PRvalidate=0 and @PRdownload=1 and @PRupload=0 then @pages='Download Only'
when @PRvalidate=1 and @PRdownload=0 and @PRupload=0 then @pages='Validate and Download'
End;


RETURN @pages

END
GO

I am getting an error as incorrect syntax near keyword at WHEN

user-defined-function

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-17T11:41:20Z

Grant Fritchey answered May 17, '10

How about something like this:

SELECT  @pages = CASE WHEN (@PRvalidate = 1
                            AND @PRdownload = 1
                            AND @PRupload = 1
                           ) THEN 'Admin'
                      WHEN (@PRvalidate = 1
                            AND @PRdownload = 0
                            AND @PRupload = 0
                           ) THEN 'Validate Only'
                      WHEN (@PRvalidate = 0
                            AND @PRdownload = 1
                            AND @PRupload = 0
                           ) THEN 'Download Only'
                      WHEN (@PRvalidate = 1
                            AND @PRdownload = 0
                            AND @PRupload = 0
                           ) THEN 'Validate and Download'
                 END

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · May 17, 2010 at 11:44 AM

Additionally, you declare variable as @PRvalidat and then reference it in the code as @PRvalidate, the names don't match

1 ·

Oleg commented · May 17, 2010 at 11:50 AM

And the last when has an error in logic, perhaps you meant the code to branch there when both @PRvalidate and @PRdownload are equal to 1. Otherwise, your last when is identical to the second and as a result you will never get 'Validate and Download' result.

1 ·

Grant Fritchey ♦♦ commented · May 17, 2010 at 11:46 AM

Good catch. I didn't notice that when I put the sample together.

0 ·

Grant Fritchey ♦♦ commented · May 17, 2010 at 12:26 PM

You really did read it closer than I did. I just swished through the simple coding errors. You should have posted your as an answer.

0 ·

question