Solved

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

Posted on 2011-03-02
12
605 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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