problem creating a UDF using t-sql

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

General SQL Server Forums

New to SQL Server Programming

problem creating a UDF using t-sql

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2009-02-05 : 05:20:14

Hi I am geting the following error when trying to create a UDF by running the following script, Can anyone help me? Thx

ERROR MESSAGE:
Server: Msg 156, Level 15, State 1, Procedure checkPermissions, Line 24
Incorrect syntax near the keyword 'GRANT'.

MY SCRIPT:
CREATE FUNCTION dbo.checkPermissions(@ItemPermissionsList varchar(8000),@UserPermissionsList varchar(8000))
RETURNS bit
AS
BEGIN
declare @Return bit
declare @Value varchar(8000)
set @Return = 1
set @ItemPermissionsList = @ItemPermissionsList + ','
While (Charindex(',',@ItemPermissionsList)>0)
Begin
Set @Value = ltrim(rtrim(Substring(@ItemPermissionsList,0,Charindex(',',@ItemPermissionsList))))

if (@Value!=null AND @Value!='')
begin
set @return = case WHEN Charindex(@Value,@UserPermissionsList) > 0 THEN @return ELSE 0 END
END

Set @ItemPermissionsList = Substring(@ItemPermissionsList,Charindex(',',@ItemPermissionsList)+1,len(@ItemPermissionsList))
End

Return @Return
END

GRANT EXECUTE ON [dbo].[checkPermissions] TO [sqlaccount]
GRANT EXECUTE ON [dbo].[checkPermissions] TO [sqlbungi]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 05:22:08

END
GO
GRANT EXECUTE ON [dbo].[checkPermissions] TO [sqlaccount]
GRANT EXECUTE ON [dbo].[checkPermissions] TO [sqlbungi]

E 12°55'05.63"
N 56°04'39.26"