Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-15
5
Medium Priority
?
130 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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

916 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