CODEWITHSUNDEEP
sqlstored-procedurestemp-tables
SumGuy
SumGuy

Reputation: 491

Stored Procedure: Reducing Table Data

A simple question about Stored Procedures.

I have one stored procedure collecting a whole bunch of data in a table. I then call this procedure from within another stored procedure. I can copy the data into a new table created in the calling procedure but as far as I can see the tables have to be identical.

Is this right? Or is there a way to insert only the data I want?

For example....

I have one procedure which returns this:

SELECT @batch as Batch, @Count as Qty, pd.Location, cast(pd.GL as decimal(10,3)) as [Length], cast(pd.GW as decimal(10,3)) as Width, cast(pd.GT as decimal(10,3)) as Thickness FROM propertydata pd GROUP BY pd.Location, pd.GL, pd.GW, pd.GT

I then call this procedure but only want the following data:

DECLARE @BatchTable TABLE ( Batch varchar(50), [Length] decimal(10,3), Width decimal(10,3), Thickness decimal(10,3), )

INSERT @BatchTable (Batch, [Length], Width, Thickness) EXEC dbo.batch_drawings_NEW @batch

So in the second command I don't want the Qty and Location values.

However the code above keeps returning the error:

"Insert Error: Column name or number of supplied values does not match table"

Upvotes: 0

Views: 167

Answers (1)

Matthew Flynn
Matthew Flynn

Reputation: 2238

The tables don't have to be identical, the results of the query from the first stored proc need to match the columns defined in the second, and they don't.

You need to change the first stored procedure to not return Qty and Location.

Select Batch, [Length], Width, Thickness FROM (
SELECT @batch as Batch, @Count as Qty, pd.Location, cast(pd.GL as decimal(10,3)) as [Length], cast(pd.GW as decimal(10,3)) as Width, cast(pd.GT as decimal(10,3)) as Thickness 
FROM propertydata pd GROUP BY pd.Location, pd.GL, pd.GW, pd.GT)

Upvotes: 1

Related Questions