Solved

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

Posted on 2014-10-15
5
123 Views
Last Modified: 2014-10-15
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
Comment
Question by:PaulBS
5 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40381682
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40381697
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
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
ID: 40381725
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40381728
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
 

Author Closing Comment

by:PaulBS
ID: 40381748
Thanks for the quick response and code example.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question