Solved

MSSQL Stored Procecedure to Insert data

Posted on 2014-12-20
11
80 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 34

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

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
 
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 34

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 34

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 34

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

679 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