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

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

MSSQL Stored Procecedure to Insert data

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
José Perez
Asked:
José Perez
  • 5
  • 5
1 Solution
 
knightEknightCommented:
Looks like a homework problem to me - just sayin'.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
José PerezAuthor Commented:
Copy.

It displays an error in "Where OCPRID = @OCPRID"
0
Independent Software Vendors: 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!

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
José PerezAuthor Commented:
Tomorrow I'll try, please gime time.
Thanks.
0
 
José PerezAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
José PerezAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
José PerezAuthor Commented:
Same error :(
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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