Procedure result table - 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

General SQL Server Forums

New to SQL Server Programming

Procedure result table

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-08 : 11:28:35

Hi,

I have created a procedure that outputs results into a table. When I want to execute the procedure with different values I get an error:

'There is already an object named 'TableName' in the database.'

How can run the procedure multiple times and store its different output values without the need to drop the table each time?

Here is my procedure:

Create Procedure Test (@CallsLastWeek_Input int, @NumberPaymentsLastWeek_Input int, @TotalActivityNextMonth_Output int OUTPUT)

as

Declare @Calls_Payments Table (TotalActivity_NextMonth int)

Declare @CallsNextWeek_Value int

set @CallsNextWeek_Value = @CallsLastWeek_Input + 5

Declare @NumberpaymentsNextWeek int

Set @NumberpaymentsNextWeek = @NumberPaymentsLastWeek_Input - 3

set @TotalActivityNextMonth_Output = (@NumberpaymentsNextWeek + @CallsNextWeek_Value)* 4

Insert into @Calls_Payments
Values (@TotalActivityNextMonth_Output)

select * into TotalActivityTest
from @Calls_Payments
select * from TotalActivityTest
_____
End of procedure

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-08 : 11:42:14

1. You can use temp table (# table).
2. You can check the existance of table in your table and drop it if exists.
3. You can use table variable.

If you want to store the previous output then you need to create different table upon each execution
or
Create the table first and have a column which can hold the time of execution and then insert in that table in SP

May be more ways.

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:45:37

select * into TotalActivityTest
from @Calls_Payments

is trying to CREATE a table called "TotalActivityTest"

If you just want to INSERT into the existing table "TotalActivityTest" you need

INSERT into TotalActivityTest
select *
from @Calls_Payments

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:48:17

NOTE: that you should not use SELECT * - you should list all the columns you actually need (otherwise if someone adds a massive TEXT column to the table in the future ALL your SELECT * routines will be passing it to the application, and the application won't be expecting it ... and that will have disastrous implications for performance. Where you modify your application to need the new column then you should change the corresponding Sproc to include that table. So ... only pass the columns that the application actually needs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources