problem creating db function via cfquery tag

Forum|Forum|17 years ago
February 5, 2009
3 replies
1164 views

I am trying to create a database UDF using the coldfusion cfquery tag. My query is pasted below. The error I get is:

"Incorrect syntax near 'go'.. "

Is there a way of running multiple sql commands in a cfquery tag?

<cfquery name="createUDFcheckPermissions" datasource="#arguments.extranetBuilderDSN#">
use bungi;
go

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

if (@Value!=null AND @778398!='')
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
go
GRANT EXECUTE ON [dbo].[checkPermissions] TO [sqlaccount]
GRANT EXECUTE ON [dbo].[checkPermissions] TO [sqlbungi]
</cfquery>

Database access

This topic has been closed for replies.

N

Newsgroup_User

Inspiring

> <cfquery name="createUDFcheckPermissions"
> datasource="#arguments.extranetBuilderDSN#">
> use bungi;

Is this necessary? Does the DB user you're using for the DSN have
permissions to any more than one DB?

Usually even if the user does, you'd specify the specific DB when
name-spacing the object you're referencing, via some variation of
[db][user][object name] or something like that, wouldn't you?

> go

I think you'll find this is is a client directive, not a DB server
directive. It tells the DB client that you copy and pasted this from to
execute the SQL. It shouldn't be in SQL you're sending to the DB server
via CF.

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

Put these two in separate, subsequent <cfquery> calls.

--
Adam

J

JR__Bob__Dobbs-qSBHQ2

Inspiring

"GO" is not a SQL statement, it is only recognized by MS SQL Server's built in utilities. As stated by the previous responders you will need to execute your statements in several cfquery tags.

http://msdn.microsoft.com/en-us/library/aa258908(SQL.80).aspx

Z

zubairAuthor

Inspiring

The database i am using is MSSQL 2000.....

D

Dan_Bracuk

Inspiring

unless it's an MSSQL or sybase db, you can't have more than one sql query inside a cfquery tag.