Update column of a table with a sproc

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)

Update column of a table with a sproc

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2008-06-10 : 11:10:30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:19:49

Whats the rule for determining whether ticker is valid? You should explain more elaborately how you think the SProc will give you the given output.

daman
Yak Posting Veteran

72 Posts

Posted - 2008-06-10 : 11:40:01

The sproc is basically just a lookup function that looks at several tables and determine if a company's name has been changed, merged
on a given date. It will just return a new name or old name if nothing happens.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:48:02

quote:
Originally posted by daman

The sproc is basically just a lookup function that looks at several tables and determine if a company's name has been changed, merged
on a given date. It will just return a new name or old name if nothing happens.

Then how do you think you will get AAB from AAB-Bank?

daman
Yak Posting Veteran

72 Posts

Posted - 2008-06-10 : 11:51:00

because on 6/6/08, AAB-Bank becomes AAB according to my lookup table.
On 6/5/08, AAB-Bank is still the same.
And that's the job of my sproc.

Not sure why you want to know how the sproc works. Maybe I didn't ask the question the right way?
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 11:58:16

quote:
Originally posted by daman

because on 6/6/08, AAB-Bank becomes AAB according to my lookup table.
On 6/5/08, AAB-Bank is still the same.
And that's the job of my sproc.

Not sure why you want to know how the sproc works. Maybe I didn't ask the question the right way?
thanks

Without knowing what your SProc does, how do you expect us to provide you a solution? your question was
I like to have the sproc going through each ticker in the table and return the valid tickers.

How could i tell the solution without knowing you SProc functions and also your table data?

daman
Yak Posting Veteran

72 Posts

Posted - 2008-06-10 : 12:03:23

quote:

Without knowing what your SProc does, how do you expect us to provide you a solution? your question was
I like to have the sproc going through each ticker in the table and return the valid tickers.

How could i tell the solution without knowing you SProc functions and also your table data?

Opps, I just read my post again and I apologize for it. It was a mispost.
Now I understand why you asked what you asked.

What I mean is that I already have a sproc that return the valid ticker. I'd like to have
a simple query that go through that sample table, use the sproc on the ticker column and return the updated table.

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 12:27:06

[code]DECLARE @ID int,@Ticker varchar(100),@Date datetime

SELECT @ID=MIN(ID)
FROM Table

WHILE @ID IS NOT NULL
BEGIN

SELECT @Ticker=Ticker,@Date=yourdatevalue
FROM Table
WHERE ID = @ID
EXEC usp_validTicker @Ticker,@Date

SELECT @ID=MIN(ID)
FROM Table
WHERE ID > @ID
END[/code]

daman
Yak Posting Veteran

72 Posts

Posted - 2008-06-10 : 12:59:21

thanks for helping.
My question is more "Can I update the current table with EXEC Sproc"

I did have something similar that spits out separate tickers for each current one. I'm interested in knowing if I can do in in-line?

Something like
UPDATE TABLE
set ticker = EXEC usp_validTicker @Ticker,@Date
WHERE ID = @ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:01:43

quote:
Originally posted by daman

thanks for helping.
My question is more "Can I update the current table with EXEC Sproc"

I did have something similar that spits out separate tickers for each current one. I'm interested in knowing if I can do in in-line?

Something like
UPDATE TABLE
set ticker = EXEC usp_validTicker @Ticker,@Date
WHERE ID = @ID

Not directly
You need to populate a temp table with results of sp like


INSERT INTO #temp
EXEC usp_validTicker @Ticker,@Date

then use this table for update

UPDATE m
set m.ticker = t.Value
FROM TABLE m
INNER JOIN #Temp t
ON fields
WHERE ID = @ID...

daman
Yak Posting Veteran

72 Posts

Posted - 2008-06-10 : 13:07:20

Sounds good.
How would i insert into #temp table ID and Ticker where ticker is the result of the sproc

INSERT INTO #temp
select ID from Table where ticker = @ticker, EXEC usp_validTicker @Ticker,@Date

I'm not sure if it's the correct syntax.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:17:32

quote:
Originally posted by daman

Sounds good.
How would i insert into #temp table ID and Ticker where ticker is the result of the sproc

INSERT INTO #temp
select ID from Table where ticker = @ticker, EXEC usp_validTicker @Ticker,@Date

I'm not sure if it's the correct syntax.

Thanks

I think thinking about your requirement you could make matters more easier if you use User Defined Function instead of stored procedure as UDFs have more flexibility and can be used directly in SELECT statement

you could even do

UPDATE TABLE
set ticker = usp_validTicker (@Ticker,@Date)
WHERE ID = @ID

If it was a user defined function.

daman
Yak Posting Veteran

72 Posts

Posted - 2008-06-10 : 13:30:49

Exactly what I started out to do. I created a UDF instead of the sproc but someone mention that you can't
call sproc inside a UDF.

So if i understand you correctly, I will change the sproc to become UDF and call that UDF inside my queries.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:36:02

quote:
Originally posted by daman

Exactly what I started out to do. I created a UDF instead of the sproc but someone mention that you can't
call sproc inside a UDF.

So if i understand you correctly, I will change the sproc to become UDF and call that UDF inside my queries.

Exactly and you could simply do like this

UPDATE TABLE
set ticker = dbo.usp_validTicker (ticker,YourDateValue)

and it will do the updates of all valid Ticker values in one shot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources