Solved

sql server: An invalid option was specified for the statement "CREATE/ALTER FUNCTION".

Posted on 2011-03-02
12
564 Views
Last Modified: 2012-05-11
i try this
CREATE FUNCTION GetOrders ()
RETURNS TABLE
WITH EXECUTE AS 'pat'
AS
RETURN ( SELECT * FROM Sales.SalesOrderDetail )

but i have this error
Msg 487, Level 16, State 1, Procedure GetOrders, Line 4
An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
Msg 178, Level 15, State 1, Procedure GetOrders, Line 5
A RETURN statement with a return value cannot be used in this context.
0
Comment
Question by:enrique_aeo
  • 6
  • 4
  • 2
12 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35021302
You will need to declare a table variable and populate it, then return it from the function:


AS
BEGIN

 declare @ret table( col1 int, col2, varchar(255) ... )

 insert into @ret
 select col1, col2 ...
 from Sales.SalesOrderDetail

 return @ret

END
0
 
LVL 15

Expert Comment

by:pateljitu
ID: 35021316
WITH EXECUTE will work under login that created the function.

CREATE FUNCTION GetOrders()
RETURNS TABLE
-- WITH EXECUTE AS 'pat'
AS
RETURN ( SELECT * FROM Sales.SalesOrderDetail
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35021379
actually, the table structure must be specified in the RETURNS statement:


  CREATE FUNCTION GetOrders ()
  RETURNS @ret table( col1 int, col2, varchar(255) ... )
  -- WITH EXECUTE AS 'pat'
  AS
  BEGIN
   insert into @ret
   select col1, col2 ...
   from Sales.SalesOrderDetail

   return @ret

  END
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35021393
... which raises the question, why use a function for this when you can just use the query instead?
0
 

Author Comment

by:enrique_aeo
ID: 35021626
I want to prove this, as users create pat and  ted

executionAS.jpg
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35021648
I don't know if the code in that image was meant to be taken literally - I have my doubts.  Is this for SQL Server?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:enrique_aeo
ID: 35022299
yes, sql server 2008 oficial course
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35022345
I was wrong about that syntax - it appears it does work in 2008.  I created a generic function with this exact text and it worked:

  CREATE FUNCTION myTableFunction ()
  RETURNS TABLE
  --WITH EXECUTE AS 'pat'
  AS
  RETURN ( SELECT 1 as one )

so, I would guess that the source of the error you are seeing is in the WITH EXECUTE clause, or with the database/table name.
Does "pat" exist as a user in the database?
0
 

Author Comment

by:enrique_aeo
ID: 35033742
yes, pat exits
userEXISTS.jpg
0
 

Author Comment

by:enrique_aeo
ID: 35033748
EXPERTS PLEASE I need your help (Transact SQL) to play the slide
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 125 total points
ID: 35036528
Per this documentation:  http://msdn.microsoft.com/en-us/library/ms188354.aspx

It looks like the EXECUTE AS clause cannot be used on table-valued functions.  Not sure why that would be the case, but that is what it says.

To verify this, I created a dummy function that returns INT and uses the EXECUTE AS clause, and it works just fine:

create function dropme()
  returns  INT
  WITH EXECUTE AS 'my_login'
  as
  begin
    declare @i int
    select  @i = 0
    return  @i
  end


But this does not work:

  CREATE FUNCTION myTableFunction ()
  RETURNS TABLE
  WITH EXECUTE AS 'my_login'
  AS
  RETURN ( SELECT 1 as one )

 
 
0
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 125 total points
ID: 35039340
Do create function without WITH EXECUTE statement

CREATE FUNCTION GetOrders ()
RETURNS TABLE
AS
RETURN ( SELECT * FROM Sales.SalesOrderDetail )

Right-click on function and select properties > permissions
Click ADD and select 'pat', once the user is selected it will list explicit permission that can be assigned to user 'pat' GRANT select permission to user (do similar procedure for all user you want to GRANT / DENY).

Once the permission is set and if you try to execute Store Procedure with username as parameter, if you supply 'pat' as user, grant permission was set for user and procedure will return rows. And say similar 'ted' was denied select permission when parameter is 'ted' SP will return error.
Check Permission:


SELECT * FROM fn_my_permissions('GetProducts', 'OBJECT') 
    ORDER BY subentity_name, permission_name ;  
REVERT;
GO

Open in new window

create procedure sproc_GetOrders 

@user varchar(50) = ''

as            
begin            
set nocount on   

	EXECUTE AS USER = @user;
	BEGIN TRY
		select * from GetOrders();
	END TRY
	BEGIN CATCH
		-- Execute error retrieval routine.
		Print 'Error Occured;'
	END CATCH;

Set nocount off              
end

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now