Call function from a procedure and assign return value to variable (sql server)

Hi all,
I have a procedure proc1, which needs to update a table.
One of the columns to update the table is calculated using a function
funct1 (which returns a smalldatetime)
My problem is how do I call the function funct1 from procedure proc1,
so that the return value of function is saved in a variable in
procedure.
Sample Procedure Proc1
ALTER PROCEDURE proc1
@.x int
AS
declare @.ret smalldatetime
--Here, I want @.ret to be a smalldatetime value returned by function
funct1.
--I tried the below (though knowing it wont work):
--@.ret = select dbo.funct1(param1), but it gives error.
--I even tried(again, though knowing it wont work):
--update myTable set field1 = select dbo.funct1(param1), but it gives
error.
--WHERE ...
update myTable set field1 = @.ret
WHERE ...
Sample function funct1:
alter FUNCTION dbo.funct1
(
@.val1 decimal,
@.val2 int
)
RETURNS smalldatetime
AS
BEGIN
DECLARE @.ret1 decimal
DECLARE @.ret2 smalldatetime
DECLARE sel_Cursor CURSOR FOR SELECT field1, field2
FROM myTable
OPEN sel_Cursor
FETCH NEXT FROM sel_Cursor INTO @.ret1, @.ret2
CLOSE sel_Cursor
DEALLOCATE sel_Cursor
if @.val1 = @.ret2
return @.ret2
else
set @.ret2 = some function...
RETURN @.ret2
END
---
Pls help..
TIA..Whats the error ? Normally you could use it by using this sample query
SELECT @.ret = dbo.SomeFunction(@.SomeParam)
HTH, Jens Suessmeyer.|||Thanks guys..
Damn, I dont knw why i was using the 'select'. Now it worked for me.
Trey,
I am using the cursor to fetch value and assign it to a variable.
The 'where' clause fetches single row.
And the select query returns single value.
But still i have used cursor coz thts the only way I know (I am pretty
new to SQL :( ).
Is there any other way to do so'
Trey Walpole wrote:
> What is that cursor supposed to be doing?
> Are you really looping through the whole table to get a single value'
> And possibly some random value at that?
> i cannot in good conscience give you an answer and let you keep that UDF..
.
>
> Chris wrote:|||Chris wrote:
> Hi all,
> I have a procedure proc1, which needs to update a table.
> One of the columns to update the table is calculated using a function
> funct1 (which returns a smalldatetime)
> My problem is how do I call the function funct1 from procedure proc1,
> so that the return value of function is saved in a variable in
> procedure.
>
> Sample Procedure Proc1
> ALTER PROCEDURE proc1
> @.x int
> AS
> declare @.ret smalldatetime
> --Here, I want @.ret to be a smalldatetime value returned by function
> funct1.
> --I tried the below (though knowing it wont work):
> --@.ret = select dbo.funct1(param1), but it gives error.
> --I even tried(again, though knowing it wont work):
> --update myTable set field1 = select dbo.funct1(param1), but it gives
> error.
> --WHERE ...
> update myTable set field1 = @.ret
> WHERE ...
>
> Sample function funct1:
> alter FUNCTION dbo.funct1
> (
> @.val1 decimal,
> @.val2 int
> )
> RETURNS smalldatetime
> AS
> BEGIN
> DECLARE @.ret1 decimal
> DECLARE @.ret2 smalldatetime
> DECLARE sel_Cursor CURSOR FOR SELECT field1, field2
> FROM myTable
> OPEN sel_Cursor
> FETCH NEXT FROM sel_Cursor INTO @.ret1, @.ret2
> CLOSE sel_Cursor
> DEALLOCATE sel_Cursor
> if @.val1 = @.ret2
> return @.ret2
> else
> set @.ret2 = some function...
> RETURN @.ret2
> END
> ---
> Pls help..
> TIA..
Why does the function use a cursor? Why don't you just assign the
result of the function to a variable?
Please post DDL, sample data and required results as described in:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Chris wrote:
> Thanks guys..
> Damn, I dont knw why i was using the 'select'. Now it worked for me.
> Trey,
> I am using the cursor to fetch value and assign it to a variable.
> The 'where' clause fetches single row.
> And the select query returns single value.
> But still i have used cursor coz thts the only way I know (I am pretty
> new to SQL :( ).
> Is there any other way to do so'
>
In that case you probably don't need the cursor or the function. You
can do it in the UPDATE. For example:
UPDATE your_table
SET col =
(SELECT col
FROM other_table
WHERE ...)
WHERE ...
etc.
Set-based code should be your first choice for this kind of thing. If
you are new to SQL then you shouldn't be using cursors AT ALL. You can
do a lot of damage that way.
David Portas
SQL Server MVP
--|||Hey david,
In the function, I am doing calculation with two different tables, and
the value returned is compared with the parameter i am passing in the
function.
If the values are same, I am not supposed to change the value in
tables.
If its different, some calculations are made, and the new value is
inserted.
For this, I am using cursor, because I need to save value to a variable
to compare, as well as calculate.|||Chris wrote:
> Hey david,
> In the function, I am doing calculation with two different tables, and
> the value returned is compared with the parameter i am passing in the
> function.
> If the values are same, I am not supposed to change the value in
> tables.
> If its different, some calculations are made, and the new value is
> inserted.
> For this, I am using cursor, because I need to save value to a variable
> to compare, as well as calculate.
A) You don't need to use a cursor to assign a value to a variable. You
can use SET
SET @.var =
(SELECT col
FROM your_table
WHERE ...)
For multiple assignment you can use SELECT:
SELECT @.col1 = col1, @.col2 = col2
FROM your_table
WHERE ...
B) You don't need a function to do all the joins and calculations you
talked about. You should be able to do that in an UPDATE statement in
almost every case. Most cases, doing it in the UPDATE statement will
perform better than using a function.
Unfortunately you didn't give us enough information to reply with
alternative solutions. That's why the convention is to post DDL, sample
data, and your required end results. I posted a link earlier that
explained how to do that.
Let me explain why I said you shouldn't use cursors. I didn't meant
that as serious advice rather than a rebuke. The reason is that most of
the time cursors are a bad choice for solving problems in SQL. Those
who don't know SQL well tend to write over-complex, inefficient and
buggy cursor code. Only when you know SQL really well will you have the
experience to know when a cursor is a sensible idea. Until then it
really is safest to avoid them altogether.
Hope this helps.
David Portas
SQL Server MVP
--|||Thanx for the help Dave,
I will try doing it this way..