asked on

Create a stored procedure based on stored procedure or combine into one?

HI,
Using one or more stored procedures I want to insert a number of record from table A into table B but I want to only insert records from table A that match 2 variables x and y. Then along with the resulting list I want to insert against each record another variable (the same for each record) z.

So -

Select column1, column2, column3, column4 FROM A WHERE column1 = x AND column2 = y

then using the output of above -

INSERT INTO B column1, column2, column3, column4, z

I want to be able to call this procedure by specifying x, y and z in one go.

Should I have two stored procedures (one for the Select and the second for the insert) or can it be done with one stored procedure?

Can anyone supply the code if it can be done with one?

Cheers,

Paul

Haris Dulic

you can create one procedure and do something like this

INSERT INTO B column1, column2, column3, column4, z
Select column1, column2, column3, column4 FROM A WHERE column1 = x AND column2 = y

Open in new window

Phillip Burton

Why not combine the two into one statement:

INSERT INTO B 
SELECT column1, column2, column3, column4, z
FROM
(Select column1, column2, column3, column4 FROM A WHERE column1 = x AND column2 = y)

Open in new window

ASKER CERTIFIED SOLUTION

Dale Burrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Haris Dulic

here is the complete procedure :

create procedure dbo.insert_all 
x as varchar(20),y as varchar(20),z as varchar(20)
as
INSERT INTO B column1, column2, column3, column4, column5
Select column1, column2, column3,  column4, rtrim(@z) as column4FROM A WHERE column1 = rtrim(@x) AND column2 = rtrim(@y)
GO

Open in new window

Paul Stevenson

ASKER

Thanks for the quick response and code example.