How to dynamically generate recordset in Function?

[goodmorningsky]

Hi, all

I'm using Sql server 2000

I want to make select statement dynamically and return table using function.

in sp, I've done this but, in function I don't know how to do so.

(I have to create as function since our existing API..)

 

Following is my tials...

1.

alter Function fnTest

( @fromTime datetime, @toTime datetime)

RETURNS Table

AS

 

RETURN Exec spTest @from, @to

GO

 

Yes, it give syntax error..

 

2. So, I found the following

 

 

From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..

 

"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: "

.....

* EXECUTE statements calling an extended stored procedures.

 

So, I tried.

 

alter Function fnTest

( @fromTime datetime, @toTime datetime)

RETURNS Table

AS

 

RETURN Exec master..xp_msver

GO

 

It doesn't work... syntax err...

 

Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?

 

 

Now, I'm stuck.. how can I create dynamic select statement using function?

 

I want to know if it's possible or not..

[akiaz]

If you can't do it in a function, then why not just do it in your stored procedure. A stored procedure _can_ call another stored procedure using exec so stick with that.

[goodmorningsky]

I don't get it still..

 

then what's meaning of the following statement?

EXECUTE statements calling an extended stored procedures.

 

please check following link and Remarks section.

http://msdn.microsoft.com/library/d...create_7r1l.asp

 

I need example of this case.

[Nerseus]

You've got a function returning a table.

As far as I know, you can't return the results of the EXEC call as that table. Meaning, if your proc does a "SELECT...", you cannot return the results of that select from a function.

 

To use the EXEC statement in a function, you would normally do this on a proc that has an output parameter or returns a value. For example:

DECLARE @result int

EXEC @result = myproc @param1, @param2

 

or

DECLARE @result int

EXEC myproc @param1, @result output

 

(I can't remember if you need parenthesis around params in an EXEC call)

 

Maybe if you explained what you're trying to do...? For example, why must you use a function to return a table? Why are you calling a system proc within the function, etc.?

 

-ner

[goodmorningsky]

thank you for reply.

Our all existing codes using functions.

but, the functions doesn't work in dynamic way.

We have to create a function for each case.

Now I want to do it in dynamical way.

Anyway, i have to create function, not sp and

This this what I intend to do.

 

Use Northwind

create procedure spExecuteCommand (

@sqlCommand varchar(1000))

AS

BEGIN

Exec (@sqlCommand)

select 1

END

 

alter function fnExcuteCmd(@tableName varchar(100))

returns int

as

begin

declare @cmd varchar(1000)

set @cmd = 'SELECT * FROM Orders INTO ' + @tableName

declare @result int

Exec @result = spExecuteCommand @cmd

RETURN @result

end

go

 

select dbo.fnExcuteCmd('TestOrders')

 

 

but it gives following error

 

Server: Msg 557, Level 16, State 2, Procedure fnExcuteCmd, Line 8

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