INSERT stored procedure

Question

INSERT stored procedure

kdford10

Valued Member

Points: 74
More actions
October 7, 2010 at 11:31 am

#239495

I just wanted someone to help verify my insert stored procedure. What I am trying to accomplish is passing the LoanOfficerID and declaring and setting those values in the LoanOfficer table, then inserting the values into the table. Is this stored procedure performance what i'm asking?
USE [BankLoans]
GO
/****** Object: StoredProcedure [dbo].[usp_insert_LoanOfficer] Script Date: 10/07/2010 09:58:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_insert_LoanOfficer]
-- Add the parameters for the stored procedure here
(
@LoanOfficerID int
)
AS
DECLARE @BranchID int;
DECLARE @LoanOfficerNumber int;
DECLARE @LoanOfficerFirstName varchar(30);
DECLARE @LoanOfficerLastName varchar(30);
SET @BranchID = '9'
SET @LoanOfficerNumber = '4597';
SET @LoanOfficerFirstName = 'Hyun';
SET @LoanOfficerLastName = 'Mahone';
BEGIN
INSERT INTO [dbo].[LoanOfficer]
(BranchID
,LoanOfficerID
,LoanOfficerNumber
,LoanOfficerFirstName
,LoanOfficerLastName)
VALUES
(@BranchID
,@LoanOfficerID
,@LoanOfficerNumber
,@LoanOfficerFirstName
,@LoanOfficerLastName)
END

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Assuming that the parameters are going to be the variables that you declared and initialized, then this looks good. Just ensure that you remove the declaring/setting of the variables!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kdford10 Valued Member Points: 74 More actions · Answer 2

Thanks Wayne, but what do you mean by removing the declaring/setting of variables?

Wainaps SSC Journeyman Points: 81 More actions · Answer 3

Yeah that should work fine. But I would recommend passing in all the VALUES to be inserted as parameters

(CREATE PROCEDURE usp_insert_loanOfficer (@id int, @branch int, @variable, etc)

If you hard code the other values for the insert it kind of defeats the purpose of creating a procedure. unless those values will be the same every time..

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

USE [BankLoans]

GO

/****** Object: StoredProcedure [dbo].[usp_insert_LoanOfficer] Script Date: 10/07/2010 09:58:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_insert_LoanOfficer]

-- Add the parameters for the stored procedure here

(

@LoanOfficerID int,

@BranchID int = 9,

@LoanOfficerNumber int = 4597,

@LoanOfficerFirstName varchar(30) = 'Hyun',

@LoanOfficerLastName varchar(30) = 'Mahone'

)

AS

BEGIN

INSERT INTO [dbo].[LoanOfficer]

(BranchID

,LoanOfficerID

,LoanOfficerNumber

,LoanOfficerFirstName

,LoanOfficerLastName)

VALUES

(@BranchID

,@LoanOfficerID

,@LoanOfficerNumber

,@LoanOfficerFirstName

,@LoanOfficerLastName)

END

GO

This would work for you. The stored procedure parameters have defaults set in case you choose not to pass them. I also removed the quotes from the integer values as they are only needed for strings.

If I were you, I wouldnt set defaults for the parameters unless you want a lot of loan officers with the same name 🙂

kdford10 Valued Member Points: 74 More actions · Answer 5

If I wanted to look up the data that was just inserted by the primary key, how would i go about doing that?

lukus_g SSC Eights! Points: 958 More actions · Answer 6

i guess you know the value form parsing it in? you could just select on that.

otherwise, perhaps your value increments by 1 each time? (not sure how you are creating these IDs)

in that case you could find the highest one with a SELECT MAX(LoanOfficerID)...

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

Depends on what you want to do with it. You could load it into a parameter and manipulate/return it or just return the identity as a result set.

DECLARE @Ident INT

SELECT @Ident = SCOPE_IDENTITY()

/* Do something here */

--or

SELECT SCOPE_IDENTITY()