Change SP to FUNCTION - SQL Server Forums

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

Change SP to FUNCTION

Author

Topic

Sedanda
Starting Member

13 Posts

Posted - 2010-09-23 : 02:43:28

Hi Forum,
I have an SP that uses a table to retrive data from many tables.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Alter Procedure [dbo].[Fetch_Table_Data]
AS
Begin
-- Declare the variables to store the values returned by FETCH.
DECLARE @Table varchar(8000);
declare @cmd varchar (8000);
set @cmd = ''
DECLARE Table_cursor CURSOR FOR
SELECT distinct ProjektTabelle
FROM dbo.T_TProjekte;

OPEN Table_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM Table_cursor
INTO @Table;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
if @cmd <> ''
set @cmd = @cmd + ' union '
set @cmd = @cmd + 'Select CNI,CPROJEKT from ' + @Table
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Table_cursor
INTO @Table
exec(@cmd)
END
CLOSE Table_cursor
DEALLOCATE Table_cursor
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Ho would I go about changing this SP to a Function that RETURNS A list of all Records?
thanks in advance
Jeff

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 03:35:22

This will return a table and you can use the function as:

SELECT fn_Fetch_Table_Data
to get the data


CREATE FUNCTION [dbo].[fn_Fetch_Table_Data]
	
	returns @DataTable table (CNI VARCHAR(100), CPROJEKT varchar(100)) -- Table structure returned
AS

BEGIN
-- Declare the variables to store the values returned by FETCH.
DECLARE @Table varchar(8000);
declare @cmd varchar (8000);
set @cmd = ''

DECLARE Table_cursor CURSOR FOR
SELECT distinct ProjektTabelle
FROM dbo.T_TProjekte;

OPEN Table_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 
FETCH NEXT FROM Table_cursor
INTO @Table;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
if @cmd <> ''
set @cmd = @cmd + ' union '

set @cmd = @cmd + 'Select CNI,CPROJEKT from ' + @Table
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Table_cursor
INTO @Table
exec('INSERT INTO @DataTable '+@cmd)
END
CLOSE Table_cursor
DEALLOCATE Table_cursor

RETURN -- Returns the table.

END




GO

Sedanda
Starting Member

13 Posts

Posted - 2010-09-23 : 04:07:43

Hi rohitvishwakarma
I Get the error message "Incorrect syntax near 'returns." What could cause that?

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:19:02

SORRY!
aren't you using SQL server 2008?



CREATE FUNCTION [dbo].[fn_Fetch_Table_Data]() --  was missing
	
	returns @DataTable table (CNI VARCHAR(100), CPROJEKT varchar(100)) -- Table structure returned
AS

BEGIN
-- Declare the variables to store the values returned by FETCH.
DECLARE @Table varchar(8000);
declare @cmd varchar (8000);
set @cmd = ''

DECLARE Table_cursor CURSOR FOR
SELECT distinct ProjektTabelle
FROM dbo.T_TProjekte;

OPEN Table_cursor;
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 
FETCH NEXT FROM Table_cursor
INTO @Table;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
if @cmd <> ''
set @cmd = @cmd + ' union '

set @cmd = @cmd + 'Select CNI,CPROJEKT from ' + @Table
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Table_cursor
INTO @Table
exec('INSERT INTO @DataTable '+@cmd)
END
CLOSE Table_cursor
DEALLOCATE Table_cursor

RETURN -- Returns the table.

END




GO

Sedanda
Starting Member

13 Posts

Posted - 2010-09-23 : 04:21:49

No I am using SQL 2000

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:34:10

quote:
Originally posted by Sedanda

No I am using SQL 2000

???? then I am afraid there is no table datatype in sql 2000. above won't execute. ANother thing to note is EXEC() can't be used inside a fucntion.
May I know the reason you want to convert the Stored Procedure to a Function

Sedanda
Starting Member

13 Posts

Posted - 2010-09-23 : 04:35:15

Now I get "Invalid use of 'EXECUTE' within a function" I dont think we can use exec in a function, Any Ideas?
Jeff

Sedanda
Starting Member

13 Posts

Posted - 2010-09-23 : 04:38:31

my Boss wants it as a function. I dont see any way to get these records without an Exec in the Function. Thanks for your help.
Jeff

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:41:12

quote:
Originally posted by Sedanda

Now I get "Invalid use of 'EXECUTE' within a function" I dont think we can use exec in a function, Any Ideas?
Jeff

Reason 1 - Performance
Dynamic queries are not prone to automated optimization. An ad-hoc query analyzer will surely be run on every query just before execution, but static queries can be evaluated at creation time, while dynamic queries must wait until the very end.

Reason 2 - Trust
The contract for a function (user defined or otherwise) holds that the state of the database will not change as a result of executing the function. This means that no new records will be created, none will be modified, and none will be deleted. Further, no tables or databases will be created or dropped

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:45:20

go through the following links:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98512

http://www.sommarskog.se/dynamic_sql.html

and give your boss some piece of advice

i hope he is is not reading this forum

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:47:31

Stick with PROCEDURE instead of a FUNCTION.

There are restrictions to what can be done in a function..dynamic SQL AND cursors in a function is pretty inefficient

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources