Return dynamic value from function / proc

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

SQL Server 2000 Forums

Transact-SQL (2000)

Return dynamic value from function / proc

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2008-11-11 : 12:48:02

Hi,

Ultimately, I need to return a recordset that I can pass to a crystal report (for member statements). What's complicating it slightly is that the 'standard' statement that I'm trying to populate can change a lot depending which client its run for.

The solution that I've come up with is this:

1. Make a database to control which fields appear for which client
2. Make a view to return all the report data from the source database
3. Write a stored procedure to return the actual values from the view, based on the data from the control database.

Bits 1 and 2 went swimmingly, and I'm now trying to retrieve actual values from my view.

Here's what I want to do:

Lets pretend that this is my source data table (In reality there are a bunch more, but this will do for the example):

create table dbo.Example1
(
ID int identity not null,
surname varchar(20),
dateofbirth datetime,
value1 decimal(10,2)
)

insert into dbo.example1
select 'Smith', '01/01/1980', 15000 union all
select 'Jones', '01/01/1981', 14000

And I've made a view to get the data from all the tables into one place. The view will contain all the data that can possibly go on the report. e.g.

create view dbo.vw_Example1
as

select * from dbo.example1

I've then got a query from the control database to get only the fields the client wants. I wanted to use a function in this to retrieve the actual value of the field from the view.

So in pseudo code, the query looks like this:

select
clientname,
[some other data],
[the field name],
case when isnote = 0 then '[function to retrieve the actual value goes here (passing the field name in)]' else [Some standard text goes in here]end as Value

The function needs to accept a field name (defined in my control database, and return the corresponding value from the view.

As soon as I started writing it, I realised it would have to be dynamic, which is fine, but I'm stuck on how to return the value.

This is as far as I've got:

create function dbo.GetStatementValue (@ID int, @Field varchar(100))

Returns varchar(100)

As

Begin
declare @ReturnValue varchar(100)
declare @QueryString varchar(8000)

set @Querystring = 'select ' + @Field + ' from vw_example1 where ID = ' + @ID + ''

select @ReturnValue = exec(@QueryString)

End

The problem being, that

exec(@QueryString)

works fine in real life, and gives me the value I'm after, but how do I get it to return the value out of the function???

I thought about writing it as a stored procedure with an output, but I want to be able to use it in a select statement.

Any Ideas?

Cheers,

Yonabout

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 13:17:31

at the end of your function:

return @returnvalue

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-12 : 02:31:57

It is not possible to use dynamic sql inside a user defined function
Read about sp_executesql in sql server help file

Madhivanan

Failing to plan is Planning to fail

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-12 : 03:26:23

try this :

declare @param nvarchar(4000)
select @Querystring = 'select @Field=fieldname from vw_example1 where ID = ' + @ID + ''
select @param='@Field datatype output'
exec @QueryString,@param,@Field=@returnvalue output

this will return the required value

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 04:11:34

quote:
Originally posted by malaytech2008

try this :

declare @param nvarchar(4000)
select @Querystring = 'select @Field=fieldname from vw_example1 where ID = ' + @ID + ''
select @param='@Field datatype output'
exec @QueryString,@param,@Field=@returnvalue output

this will return the required value

malay

you need to use sp_executesql rather than exec here. do as Madhi suggested

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-12 : 05:17:36

thats right

sp_executesql @QueryString,@param,@Field=@returnvalue output

malay

yonabout
Posting Yak Master

112 Posts

Posted - 2008-11-12 : 05:32:02

Hi,

Thanks for the comments.

I read up on sp_executesql, and I've got it working in a procedure - I pass in a userid and a field name, and I get the correct single value back.

I understand that you can't use dynamic sql in a function, but as I want to return the value into a select statement e.g.

select
a,
b,
c,
myfunction @fieldname, @id
from
my table

What are my options for doing this without using a function?

Cheers,

Yonabout

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 05:36:24

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

yonabout
Posting Yak Master

112 Posts

Posted - 2008-11-12 : 11:14:02

OK,

Thanks for that - I got the OPENROWSET stuff working - I just need to link that to the rest of my query, but that won't be a problem.

Thanks All for your help.

Cheers,

Yonabout

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 11:54:25

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources