July 27, 2006 at 9:01 am
SQL Server 2000.
I need to emulate the behavior of an oracle function that takes an sql statement as a parameter, executes the sql within the function, loops through the recordset and returns a comma delimited string of the results of the recordset.
I know there are easier ways to get a comma delimited string of a recordset, but I don't have the luxury of using them, this has to take any sql that returns a single column as a parameter, and this has to be able to be run in a select statement, so I don't see any alternative to a function.
I know I cannot use EXEC(@sql) inside a function, but if anyone has any ideas what I can do, I would appreciate it.
thanks.
Here's what I attempted to do, to give an idea of what I need
CREATE FUNCTION mpe_join (@vsql varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
EXEC ('declare keys cursor for '+@vsql)
OPEN keys
DECLARE @keyname varchar(50),@keylist varchar(4000)
FETCH NEXT FROM keys INTO @KEYname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
Set @keylist=isnull(@keylist,'')+@keyname+','
FETCH NEXT FROM keys INTO @keyname
END
if substring(@keylist,len(@keylist),1)=','
begin
set @keylist=substring(@keylist,1,len(@keylist)-1)
end
DEALLOCATE keys
RETURN @keylist
END
GO
July 27, 2006 at 9:11 am
July 27, 2006 at 9:16 am
the developers use the oracle function like so:
select mpe_join('select fname||' '||lname from table1 where') as namelist, somecol,someothercol
from sometable
to get back something like
namelist somecol someothercol
bob jones,bill bobs,somebody else data data
I'm more an oracle than sql server guy, so if there's a way to pass a dynamic cursory to a sql server udf, that would be cool too. thanks
July 27, 2006 at 9:17 am
I messed up the escape characters in the example, sorry
select mpe_join('select fname||'' ''||lname from table1 where') as namelist, somecol,someothercol
from sometable
July 27, 2006 at 10:23 am
No function needed. Even if you decide to put this into a function, you certainly do not need to use a cursor......
declare @name varchar(4000)
set @name = ''
select @Name = @Name + fname + ' ' + lname + ',' from table1 --where....
select @Name as namelist,
somecol,
someothercol
from sometable
July 27, 2006 at 10:33 am
the problem is that this is coming in from a java front end in a predominantly database agnostic application, so I don't know that I could use the sql server specific declarations, etc.
the
select function('parm') from table
structure will work in both oracle and sql server, so that's why I'm trying to keep that.
July 27, 2006 at 11:38 am
I see no way to do what you are asking. Since you cannot execute dynamic SQL inside a function, you have no way to return a value based on the SELECT statement you pass into the function. Sorry.
July 27, 2006 at 12:47 pm
thanks for your attention, I was getting to the same conclusion.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply