Call function from a procedure and assign return value to variable

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:
> 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..
>ah - i've been enlightened by a colleague
MSSQL isn't Oracle - many things don't work the same - functions and
cursors in particular.
in oracle (prior to 9i), it's best to use a function to return single
values [in mssql - only if the scalar function will truly get a lot of
re-use; sql2005 is better at udfs than sql2k],
and i understand that performance can be better with the cursor/fetch
first row value [hopefully, there was supposed to be a where on that
select *] in mssql, cursors are to be avoided.
unless this udf is re-used a lot, in mssql this works better in-line
with more specific ddl, a better mssql approach can be suggested
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:
>|||see reply to myself :)
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'
> Trey Walpole wrote:
>
>