anipassion.com

How to write SQL Query to return output parameters

Voir le sujet précédent Voir le sujet suivant Aller en bas

How to write SQL Query to return output parameters

Message  Admin le Jeu 28 Avr - 9:33

Here I will explain how to write query to return output parameters in SQL Server.

Description:

In some situations we need to return the output value after execution of our query here I will explain with simple user registration here I am returning one out parameter if the query executes successfully else I am returning another output parameter .

Query:

CREATE PROCEDURE sp_userinformation
@UserName varchar(50),
@Password varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@Email varchar(50),
@PhoneNo varchar(50),
@Location varchar(50),
@Created_By varchar(50),
@ERROR VARCHAR(100) OUT

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

---Checking Condition if User exists or not if user not exists returns different message if exists returns different message

IF NOT EXISTS(SELECT * FROM User_Information WHERE UserName=@UserName)

BEGIN

INSERT INTO User_Information
(
UserName,
[Password],
FirstName,
LastName,
Email,
PhoneNo,
Location,
Created_By
)
VALUES
(
@UserName,
@Password,
@FirstName,
@LastName,
@Email,
@PhoneNo,
@Location,
@Created_By
)

--If User Successfully Registerd I am returing this Message as Output Parameter
SET @ERROR=@UserName+' Registered Successfully'

END

ELSE

BEGIN

--If User already Exists i am returning this Message as Output Parameter

SET @ERROR=@UserName + ' Already Exists'

END

END






This way we will write queires to return output parameters

Admin
Admin

Nombre de messages : 45
Date d'inscription : 20/01/2009

Voir le profil de l'utilisateur http://programming.formyjob.net

Revenir en haut Aller en bas

Voir le sujet précédent Voir le sujet suivant Revenir en haut

- Sujets similaires

 
Permission de ce forum:
Vous ne pouvez pas répondre aux sujets dans ce forum