Solved

MSSQL Stored Procecedure to Insert data

Posted on 2014-12-20
11
60 Views
Last Modified: 2015-01-24
Hi,
I need to create an Store Procedure that I can assign a Job to insert records from 1 table to a second one.

From table tblOCPR, columns: Name, Dni, Company, Phone
To tblContacts, columns: UsrName, UsrDni, UsrCompany, UsrPhone

I would like to use this sp template:
CREATE PROCEDURE sp_InsertContacts
AS
BEGIN
 BEGIN TRANSACTION 
 BEGIN TRY 
Insert into Employee() Values()
 set @flag=1;
IF @@TRANCOUNT > 0
 BEGIN commit TRANSACTION;
 END
 END TRY 
BEGIN CATCH
IF @@TRANCOUNT > 0
 BEGIN rollback TRANSACTION;
 END
 set @flag=0;
 END CATCH
END

Open in new window

0
Comment
Question by:José Perez
  • 5
  • 5
11 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 40511340
Looks like a homework problem to me - just sayin'.
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40511344
The purpose of a proc is to be used repeatedly. The way you are using (hard coding) your proc defeats this purpose.

You need to add some parameters to your proc to function as intended. This will be like:

CREATE PROCEDURE spInsertContacts    -- also sp_  generally is used by then system procs, use spInsertContacts
@OCPRID int
AS
BEGIN

inside the proc then you need

Insert into  tblContacts(UsrName, UsrDni, UsrCompany, UsrPhone)
Select Name, Dni, Company, Phone From tblOCPR
Where OCPRID = @OCPRID


to test:
Exec spInsertContacts 2    -- for example, if 2 is a primary key in tblOCPR

you need to complete this without transaction and make sure it works the continue with Transaction.

Do you need to copy from tblOCPR  to tblContacts or move it?

Mike
0
 
LVL 2

Author Comment

by:José Perez
ID: 40511397
Copy.

It displays an error in "Where OCPRID = @OCPRID"
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40511546
post your code and exact error message. Also OCPRID I assumed exists in your table. I referred to it as primary key. What ID (pk) you have in that table?

You were supposed to replace OCPRID with pk field that exists in your table. Now that you are at point you are designing stored procedures, you are expected to know about pk field very well.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40517131
Tomorrow I'll try, please gime time.
Thanks.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:José Perez
ID: 40518067
ok, I updated the SP but now it says "invalid object name [dbo].[spInsertContacts]" (I checked the name and it is correct)

USE [dbDevelopment]
GO
/****** Object:  StoredProcedure [dbo].[spInsertContacts]    Script Date: 01-10-2014 23:29:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spInsertContacts]
@id int
AS
BEGIN

Insert into tblContacts(usrName, usrDNI, usrCompany, usrPhone)
Select name, dni, company, phone from tblOCPR
Where id=@id
End

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40518071
If spInsertContacts exists and you are just altering it, you will use:

ALTER PROCEDURE [dbo].[spInsertContacts]

Otherwise, as you know, you will be using:

 CREATEPROCEDURE [dbo].[spInsertContacts]

Is this where the problem was?
0
 
LVL 2

Author Comment

by:José Perez
ID: 40518159
It is now running but nothing is inserted into tblContacts.
I have 5 rows in tblOCPR

I added an id int as pk to both tables but still nothing happens.
Insert into tblContacts(id, usrName, usrDNI, usrCompany, usrPhone)
Select id, name, dni, company, phone from tblOCPR

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40518187
Are  you testing this in SSMS? Or, via ADO.NET?

Oh BTW, you cannot include id field, try:

Insert into tblContacts(usrName, usrDNI, usrCompany, usrPhone)
Select name, dni, company, phone from tblOCPR Where id = @Id

Id fields are auto generated.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40519171
Same error :(
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40519188
Are  you testing this in SSMS? Or, via ADO.NET?

If in SSMS, what is exact syntax of running the stored procedure.

Does this error occur when you are creating the stored procedure or executing the stored procedure?
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now