In this article we are going to see how to create a table and insert the records using the stored procedure and how to out the identity value while inserting the records in table. Also we are going to see how to update the records against the id.
First let we create a Employee table
Create a stored procedure which will accept three input parameters and one out parameters through which an employee id is out from the stored procedure.
Exec the Store procedure
Output:
EMPID
--------
1
update the records using the ID
View the records
Output:
I hope from this article you will understand the basic concepts of stored procedures in sql server.
First let we create a Employee table
/* CREATE A
EMPLOYEE TABLE */
CREATE TABLE EMPLOYEE
(
ID INT IDENTITY(1,1),
NAME VARCHAR(40),
AGE INT,
ADDRES VARCHAR(150),
PHONENO VARCHAR(10),
SALARY INT
)
Create a stored procedure which will accept three input parameters and one out parameters through which an employee id is out from the stored procedure.
/* CREATE A
STORED PROCEDURE TO INSERT THE RECORDS IN THE TABLE*/
CREATE PROCEDURE CREATE_EMP(@NAME
VARCHAR(40),@AGE INT,@ADDR VARCHAR(150),@ID INT OUT)
AS
BEGIN
DECLARE @EID TABLE (ID INT)
BEGIN TRY
INSERT INTO EMPLOYEE
(
NAME,
AGE,
ADDRES
)
OUTPUT INSERTED.ID INTO @EID
VALUES
(
@NAME,
@AGE,
@ADDR
)
END TRY
BEGIN CATCH
SELECT @@ERROR
SELECT @ID = -1
END CATCH
SELECT @ID = ID FROM @EID
END
Exec the Store procedure
/* INSERT THE
RECORDS AND GET THE EMPLOYEE UNIQUE ID*/
DECLARE @NAME VARCHAR(40)
DECLARE @AGE INT
DECLARE @ADD VARCHAR(150)
DECLARE @EMPID INT
DECLARE @PHONE INT
SELECT @NAME='HANISH',@AGE = 25, @ADD = 'CHENNAI'
/* STORED
PROCEDURE HAVE THREE IN PARAMETER AND ONE OUT PARAMETER i.e IT TAKES THREE
PARAMETER AND INSERTS THE RECORDS AND RETURN THE OUTPUT IN OUT DEFINED
PARAMETER */
EXEC
CREATE_EMP @NAME,@AGE,@ADD, @EMPID OUT
SELECT @EMPID AS EMPID
Output:
EMPID
--------
1
update the records using the ID
/* UPDATE THE PHONE
FOR THAT EMPLOYEE IN DATABASE */
UPDATE
EMPLOYEE
SET PHONENO = '22222222'
WHERE ID = @EMPID
View the records
/* SEE THE RECORDS
IN EMPLOYEE TABLE */
SELECT ID,NAME,AGE,ADDRES,PHONENO FROM
EMPLOYEE
I hope from this article you will understand the basic concepts of stored procedures in sql server.
No comments:
Post a Comment