call a stored procedures from INSERT - SELECT stat

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

SQL Server Development (2000)

call a stored procedures from INSERT - SELECT stat

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-03 : 17:10:47

Please help guys, I need to call a stored procedure from a select statement

I'm basically updating a table with a select statement, one of the columns in the tables requires me to call a stored procedure.

Can I do something like

INSERT INTO myUpdateTable
SELECT (exec dbo.getNextUpdateValue), @var1, @var2, @var3
where ....etc

the stored procedure getNextValue returns a single value.

Please help someone...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:13:15

INSERT INTO myUpdateTable (Column1)
exec dbo.getNextUpdateValue @var1, @var2, @var3

Tara Kizer
aka tduggan

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-03 : 17:21:40

Here is a clearer picture, I'm selecting values from another table into the updatetable columns

insert into myUpdateTable col1, col2, col3, col4....etc
select (exec getNextUpdateValue ), @NewPoolId, getdate(), IndexID ... etc
from ItemsListTable

quote:
Originally posted by tkizer

INSERT INTO myUpdateTable (Column1)
exec dbo.getNextUpdateValue @var1, @var2, @var3

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:26:51

You can't do that. You'll have to put the value returned from stored procedure into a variable. Then use the variable inside the select query in the insert statement.

Tara Kizer
aka tduggan

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-03 : 17:31:06

does that mean a cursor...I'm looking to update tens of thousands of rows - the cursor runs real slow...I'm looking to replace it

quote:
Originally posted by tkizer

You can't do that. You'll have to put the value returned from stored procedure into a variable. Then use the variable inside the select query in the insert statement.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 17:36:22

I don't really have a clear picture of what you want to do yet, so it's hard to say that a cursor will be what you need.

Tara Kizer
aka tduggan

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-03-03 : 17:59:30

insert into myUpdateTable col1, col2, col3, col4....etc
select (exec getNextUpdateValue ), @NewPoolId, getdate(), IndexID ... etc
from ItemsListTable

I'm basically copy values from the ItemsListTable table into myUpdateTable. The getNextUpdateValue starts a transaction to ensure data integrity and does some calculation to determine the next value to be added to the table.

I'm basically trying to do an insert via a select statement without having to use a cursor.

If I have to get the result for the stored procedure for each row into a variable and use that variable for the insert statement, then I'd be looking at a cursor...I'm trying to see if I can avoid this path

Thanks a million for your input

quote:
Originally posted by tkizer

I don't really have a clear picture of what you want to do yet, so it's hard to say that a cursor will be what you need.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 18:07:05

You'll need to either use a cursor or a while loop. Both will be slow. The only way to avoid the looping is to redesign it. That would mean changing the schema (using identity values instead), making the stored procedure handle more than one value, incrementing it outside of the stored procedure, etc...

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-03 : 18:17:17

quote:
Originally posted by tchinedu
...The getNextUpdateValue starts a transaction to ensure data integrity and does some calculation to determine the next value to be added to the table...

You are running into the biggest disadvantage of a design where you have to run a query or update a table to get the next table key value.

It is possible you could work around this for situations where you have to do a large number of inserts, but without knowing your table design or how the next key is actually generated, it is very hard to say. Also, if other people are inserting data at the same time, things can be very messy.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources