[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Function results to update table

Greg Chagnon

3/20/2007 10:25:00 PM

I have an Account table with fields SSN, Account_Number, and Market_Value,
in SQL 2000.

I populate SSN and Account_Number from some other source.

I have a scalar function called GetMarketValue that takes three parameters:
SSN, Account Number, and Date, and returns Money.

Then I do this:


Update Account
Set Mkt_Val = dbo.GetMarketValue(SSN, Account_Number, '12/31/2006')


It works just like I would expect. Even with the absence of a WHERE
clause.

And there's no FROM clause either, I just realized, but the answers seem to
be in the right records.

I don't see using this kind of scalar function using entire sets of input
parameters, actually documented anywhere in BOL.

Is there some implied JOIN or WHERE clause going on here, to put each of
the correct market values into the correct record with the corresponding
SSN and account number, or am I doing something wrong?

Thanks.

David Walker
5 Answers

Andrew J. Kelly

3/21/2007 12:05:00 AM

0

The function will only operate on one row at a time. This is the big
downside of a function such as this since it takes a set based operation and
essentially turns it into a cursor so to speak. But for what you are doing
that is what you want. You want update the column Mkt_Val to the return
value of the function using the values for the other columns in the same
row. The WHERE clause will simply limit which rows it acts on not what
values it uses.

--
Andrew J. Kelly SQL MVP

"DWalker" <none@none.com> wrote in message
news:OlGqf6zaHHA.4396@TK2MSFTNGP06.phx.gbl...
>I have an Account table with fields SSN, Account_Number, and Market_Value,
> in SQL 2000.
>
> I populate SSN and Account_Number from some other source.
>
> I have a scalar function called GetMarketValue that takes three
> parameters:
> SSN, Account Number, and Date, and returns Money.
>
> Then I do this:
>
>
> Update Account
> Set Mkt_Val = dbo.GetMarketValue(SSN, Account_Number, '12/31/2006')
>
>
> It works just like I would expect. Even with the absence of a WHERE
> clause.
>
> And there's no FROM clause either, I just realized, but the answers seem
> to
> be in the right records.
>
> I don't see using this kind of scalar function using entire sets of input
> parameters, actually documented anywhere in BOL.
>
> Is there some implied JOIN or WHERE clause going on here, to put each of
> the correct market values into the correct record with the corresponding
> SSN and account number, or am I doing something wrong?
>
> Thanks.
>
> David Walker
>


xyb

3/21/2007 1:06:00 AM

0

On 3?21?, ??8?05?, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>
wrote:
> The function will only operate on one row at a time. This is the big
> downside of a function such as this since it takes a set based operation and
> essentially turns it into a cursor so to speak. But for what you are doing
> that is what you want. You want update the column Mkt_Val to the return
> value of the function using the values for the other columns in the same
> row. The WHERE clause will simply limit which rows it acts on not what
> values it uses.
>
> --
> Andrew J. Kelly SQL MVP
>
> "DWalker" <n...@none.com> wrote in message
>
> news:OlGqf6zaHHA.4396@TK2MSFTNGP06.phx.gbl...
>
>
>
> >I have an Account table with fields SSN, Account_Number, and Market_Value,
> > in SQL 2000.
>
> > I populate SSN and Account_Number from some other source.
>
> > I have a scalar function called GetMarketValue that takes three
> > parameters:
> > SSN, Account Number, and Date, and returns Money.
>
> > Then I do this:
>
> > Update Account
> > Set Mkt_Val = dbo.GetMarketValue(SSN, Account_Number, '12/31/2006')
>
> > It works just like I would expect. Even with the absence of a WHERE
> > clause.
>
> > And there's no FROM clause either, I just realized, but the answers seem
> > to
> > be in the right records.
>
> > I don't see using this kind of scalar function using entire sets of input
> > parameters, actually documented anywhere in BOL.
>
> > Is there some implied JOIN or WHERE clause going on here, to put each of
> > the correct market values into the correct record with the corresponding
> > SSN and account number, or am I doing something wrong?
>
> > Thanks.
>
> > David Walker- ??????? -
>
> - ??????? -

I have some question about row based operation,as you have
say,function operation is one of this,
i think such code:
delcare @str varchar(1000)
select @str = @str + columnname +',' from table where cretiral
is also excute row by row :)
so my question is:
whether the set based operation is externally?

Hugo Kornelis

3/21/2007 8:10:00 PM

0

On 20 Mar 2007 18:05:58 -0700, xyb wrote:

(snip)
>I have some question about row based operation,as you have
>say,function operation is one of this,
>i think such code:
>delcare @str varchar(1000)
>select @str = @str + columnname +',' from table where cretiral
>is also excute row by row :)
>so my question is:
>whether the set based operation is externally?

Hi xyb,

First: that construction is not documented, so you better not use it
anywhere where yoou need reliable results. I've seen cases where only
one row ended up in the result. Be warned!

Second - in the end, everything has to be done row by row. But with all
logic in the query, the optimizer can push things around and change the
order to get the best possible performance. With a user-defined
function, the logic isn't "visible" for the query optimizer; the UDF is
a black box that has to be called for each individual row.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

changliw

3/26/2007 1:15:00 PM

0

Hi, David,
Just check with you to see if you need further assistance on this issue.

Your statement:
"Update Account Set Mkt_Val = dbo.GetMarketValue(SSN, Account_Number,
'12/31/2006')"
has no problem. It is a concise writing.

The implicit WHERE clause must be there, but unfortunately this issue is
undocumented. Per my understanding, the reason that it can be correctly
executed row by row is decided by the SSN and Account_Number fields which
can match the Mkt_Val field row by row since they are the columns of the
same table; if the right value is retrived from a result set from other
datasources without any column association with the updating table, SQL
Server will use the first record of the result set to update all the
records of the updating table.
For example:
Update Account Set Mkt_Val = newMkt_Val FROM OtherTable
This will update all Mkt_val by using the first record in the record set
(select newMkt_Val from OtherTable).

Please feel free to let me know if you have any other questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

changliw

3/28/2007 2:55:00 PM

0

Hi, DWalker,
Just check with you to see if you need further assistance on this issue.

Please feel free to let us know if you have any other questions or concerns.
Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================