catching values from a function

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

SQL Server Development (2000)

catching values from a function

Author

Topic

saurabhjj
Starting Member

13 Posts

Posted - 2007-09-21 : 07:48:25

hi friends, i want to write a user defined function which will return a value. then i want to catch the value returned in another variable from another stored procedure or function,

i am not able to do this, pls help me out and if possible please give me a sample code also

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 07:52:41

I have no idea what you want. But this is how you assign the value from a UDF to a variable

select @var = dbo.yourudf()

Can you describe more in detail and preferably with some example ?

KH
[spoiler]Time is always against us[/spoiler]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-21 : 07:53:36

[code]create function dbo.test (@a int)
returns int
as
begin
return(@a*@a)
end
go

-- Retrieve function value in variable
declare @x int

set @x = dbo.test(2)
select @x[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 08:15:56

from email :

quote:
i have written a parameterized user defined function, which has to return a value

i another procedure or function, i want to catch the value that the above function returns,

i hope i was able to explain you what i want, please provide me with a sample code now


create function dbo.another_function(@a int)
returns int
as
begin
    declare @b int, @c int

    select @b = dbo.yourudf(@a)

    -- for example, perform some calculation 
    select @c = @b * 1 / 1

    return @c
end

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 08:20:03


create function dbo.another_function(@a int)
returns int
as
begin
    declare @c int

    -- for example, perform some calculation 
    select @c = dbo.yourudf(@a) * 1 / 1

    return @c
end

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 08:48:43

from email again.

quote:
thanks for your prompt replies,

i think you have understood the problem but, please do one more thing, i am passing 2 parameters both being varchar to the function and am calling the fucntion from another procedure which is catching the value and displaying the output,

the problem is, if i give the parameters in single or double quotes, then the ouput is null, but if i pass the parameters without any quotes then it fires an error, which says invlaid column name

saurabhjj,

You can use the Quick Reply at the end of this page to reply. Isn't it better to have more people helping you than just one ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 08:49:44

quote:
the problem is, if i give the parameters in single or double quotes, then the ouput is null, but if i pass the parameters without any quotes then it fires an error, which says invlaid column name

Please post the code of your function here.

KH
[spoiler]Time is always against us[/spoiler]

saurabhjj
Starting Member

13 Posts

Posted - 2007-09-21 : 08:53:02

following is my function and am calling this function from another procedure, please help me out

CREATE function dbo.fixed_assets (@datset varchar, @year varchar)
returns money
as
begin
declare @fa money
select @fa=sum(amount) from account_details,account_master

where

dataset_code=@datset and period=@year

and account_details.s_heading=account_master.s_heading

and account_master.category='fixed_assets' group by account_details.dataset_code

return (@fa)
end

--------calling procedure

CREATE proc myproc
as
declare @x money
set @x=dbo.fixed_assets1("itchotels001","2005")
select @x
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 08:58:14

the size of your variable should correspond to the column size.

CREATE function dbo.fixed_assets (@datset varchar(10), @year varchar(10))

why are you using varchar for @year ? @year clearly it is an integer value.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 09:04:04

Note that if you dont specify length for a varchar datatype, by default, 1 would be taken

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources