SQL Function or SQL Procedure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Function or SQL Procedure

Author

Topic

mlawton
Starting Member

35 Posts

Posted - 2009-06-14 : 17:54:36

This is what I would like to do. I have 2 update statements that I want to turn into one function or one store procedure that I can apply to any table that needs region, division, and market updated. Here are my update statements:

update anytable
set region = g.region,division = g.division, market = g.marketname
from anytable at
JOIN Geography g on billing_corp = g.[BillingCode]

update anytable
set region = g.region,division = g.division
from anytable at
JOIN Geography g on billing_corp = g.[BillingCode]
where market is null

The end result is that I want to either call this function within a stored procedure or exec a stored procedure for any table.

Maybe something like this for example:

UPDATE anytable
SET Division = fn_geography(division)
SET Region = fn_geography(region)
SET Market = fn_geography(market)
FROM anytable

Or if you can get it into one function or one procedure would be fine.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-14 : 21:49:07

You have to use Dynamic SQL to do what you want. And with Dynamic SQL you have to use Stored Procedure. You can't have Dynamic SQL in a function

Read this The Curse and Blessings of Dynamic SQL

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

mlawton
Starting Member

35 Posts

Posted - 2009-06-15 : 09:42:15

Hi KH:

Thanks for the link to The Curse and Blessings of Dynamic SQL. It is very helpful, but I am a newbie to sql. Will you be able to write the script for me? I would really appreciate it.

Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources