Change SP to Function

[Sedanta]

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

[gpl]

You will not be able to do this as your code uses an Exec call, because this could return anything, a function will not use it

[ck9663]

Looks like your exec is being executed multiple times...or am I reading it wrong? If your EXEC is executed only once after building the dynamic query, there's still a solution to your problem. If your EXEC is executed multiple times, I don't know if there's a solution for that.

From the looks of it, you're trying to read the table if the table name are in T_TProjekte table. How many records are there in T_TProjekte?

~~ CK