Retrieving Data from stored Procedure

[atiqraza]

I have created a stored Procedure which performs a couple of updates on a table, deletes some data and selects some colums from the updated column.

Now that i have created this procedure, I need to use it to join with another table. How do i specify so that i can later query the result of the stored procedure...

 

[gmmastros]

There are several ways to do this.

http://www.sommarskog.se/share_data.html

I prefer the Insert-Exec method. Generally speaking, you create a temp table that has the same structure as the results of the stored procedure, and then you insert-exec from the stored procedure, like this...

[tt][blue]
Create Table #Temp(Field1 int, field2 varchar(10), etc...)

Insert Into #Temp
Exec StoredProcedureName

Select * From #Temp
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

And you may want to note that in SQL2000 you cannot do the same thing with a table variable only a temp table. I'm not sure about 2005, does anybody know if you can do this with table variables now?

Questions about posting. See faq183-874

 

[gmmastros]

Hey ya sis.

I just checked and it does appear to work in SQL2005. You can Insert/exec from a stored procedure in to a table variable. You don't need to use a temp table. Oh... happy day.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Yay good to know as we will be going over to 2005 sometime this year.

Questions about posting. See faq183-874

 

[SQLDenis]

>>Yay good to know as we will be going over to 2005 sometime this year.

Not me I will go to Katmai (2007) this year ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/