question

Dorababu avatar image
Dorababu asked

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

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

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered

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 comments
10 |1200

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

Additionally, you declare variable as @PRvalidat and then reference it in the code as @PRvalidate, the names don't match
1 Like 1 ·
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 Like 1 ·
Good catch. I didn't notice that when I put the sample together.
0 Likes 0 ·
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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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