Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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
0
Paul Stevenson
Asked:
Paul Stevenson
1 Solution
 
Haris DjulicCommented:
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

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

0
 
Dale BurrellDirectorCommented:
Something like:

create procedure dbo.MyProc
(
 @X datatype
 , @Y datatype
 , @Z datatype
)
as
begin
  set nocount on

  insert into dbo.B (column1, column2, column3, column4, column5)
    select column1, column2, column3, column4, @Z
    FROM dbo.A
    WHERE column1 = @X AND column2 = @Y
end

Open in new window

0
 
Haris DjulicCommented:
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

0
 
Paul StevensonMarketing and IT ManagerAuthor Commented:
Thanks for the quick response and code example.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now