Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multi-statement Table_valued Function error Expand / Collapse
Author
Message
Posted Thursday, November 22, 2007 9:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 3:41 AM
Points: 27, Visits: 38
Hello friend
I have created Multi-statement Table_valued function which should return table. Function have dynamic cursor declared.
Function is created sucessfully but got error when it executing.

Error like "Only functions and extended stored procedures can be executed from within a function.".

Function code like below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FetchAddress]
()
RETURNS
@RetTable TABLE
(
adr_id uniqueidentifier
)
AS
BEGIN
declare @query nvarchar(2000)
set @query = (select query from Rules where mand=1 and station= 111)
--where @query = Select adr_id from Address where mand = 1 and station = 111 and city = 'ahmedabad'

DECLARE @STRSQL VARCHAR(2000)
SET @STRSQL = 'DECLARE ADDRESSDATA CURSOR READ_ONLY FOR ' + @query
exec sp_executesql @STRSQL
OPEN ADDRESSDATA
DECLARE @addressId uniqueidentifier

FETCH NEXT FROM ADDRESSDATA INTO @addressId
WHILE @@fetch_status = 0
BEGIN
INSERT INTO @RetTable VALues(@addressId)
FETCH NEXT FROM ADDRESSDATA INTO @addressId
END
CLOSE ADDRESSDATA
DEALLOCATE ADDRESSDATA
RETURN
END


I am executing above function by "Select * from [Test].[dbo].[FetchAddress]()"

Thanks
Post #425121
Posted Thursday, November 22, 2007 9:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
sp_ExecuteSQL is not an "extended stored procedure" or it would begin with "xp_". Neither is any form of dynamic SQL. I'm afraid that you're out of luck.

In fact, if you check out Books Online, which says the following about UDF's, you'll see that you've violated several rules for UDF's ;)

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.
Control-of-Flow statements.
DECLARE statements defining data variables and cursors that are local to the function.
SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #425125
Posted Thursday, November 22, 2007 10:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 3:41 AM
Points: 27, Visits: 38
Hi Jeff
Thanks for you reply.
There is no other workaround for fullfill my function requirment. As my requirement is that table return sql statement base on some criteria and then bt executing sql statement will return some Ids. I can't use Store procedure for fullfill above requirement as i want use this function for Replication(for filter row)
Post #425134
Posted Friday, November 23, 2007 4:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
I wish I knew enough about replication to help you out of the pickle you're in...

Thanks for the feedback.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #425368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse