using Extended Proc in Function

[jazerr]

Books online says I should be able to call an Extended Stored Procedure from a function.

Here's what i am doing:
CREATE FUNCTION page_getData
(@table varchar(500), @field varchar(500), @pCode int, @db nvarchar(50))
RETURNS VARCHAR(7000)
AS
BEGIN
DECLARE @result varchar(7000)
DECLARE @sql nvarchar(4000)

SET @sql = N'SELECT TOP 1 @stroutput=['+@field+'] FROM '+@db+'.dbo.['+@table+'] WHERE personcode='+CAST(@pCode AS nvarchar(400))
SET @result= @sql

EXEC master.dbo.[sp_executeSQL] @Sql, N'@strOutput varchar(7000) OUT', @result OUT

RETURN @result
END
GO

I [should] be able to do this right? I basically need an inline function I can pass a tablename, fieldname, dbname, and param value (in this case @pCode) and get the single value back. Anyone have anything that does this, or see whats wrong with mine?

 

[nigelrivett]

>> I [should] be able to do this right?
Nope - can't execute dynamic sql in a udf.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jazerr]

Well, this sucks. ANybody have a thought on how I might accomplich the same thing by other means?

 

[JayKusch]

Can you not call it as a stored procedure instead of a UFD?

Thanks

J. Kusch

 

[nigelrivett]

Think you need to rethink what you are trying to do.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jazerr]

I need to call it basically in the middle of another select.

The purpose is this (and I didnt design it so dont yell at me): There is an ASP page that reads what we call a 'fieldspec'. This is basically a set of data that tells the ASP page what to do. Example data would be the tablename, fieldname, inputtype, defaultvalue, etc. In this data, I need to also have the actual data that is supposed to be displayed on the page; the data in that table.field in the DB.

So the query looks something like
SELECT tablename, fieldname, inputtype, defaultvalue,(here's where I want my data to be)

The actual data returned might look like:
person firstname singlelinetext (blank) Michael



does that make sense?

 

[nigelrivett]

I quess you have a resultset that you want to do this for.

In the SP you call from the ASP page loop through the data getting the values. It's what the udf would cause anyway so you aren't losing much.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.