Friday, January 24, 2014

SQL SERVER STORED PROCEDURE if else and variable declaration

stored procedure sample for
-how to create if else statement
-how to declare vaiable and set field value for this variable and re-using in procedure


Create PROCEDURE [dbo].[xxx](@ProfileID int,@MR_TYPE INT ,@PATIENT_ID INT)
AS
DECLARE @XPROFILE varchar(200)

if(@MR_TYPE = 0)
BEGIN
SELECT @XPROFILE = ProfileID FROM Profile WHERE MR_PATIENT_IF=@PATIENT_ID
SELECT [Doc_ID],[ProfileID],[Doc_Title]
FROM [Profile_Docs]
 WHERE [ProfileID] = @XPROFILE
order by CreateDate desc;
END
else
BEGIN
SELECT [Doc_ID],[ProfileID],[Doc_Title],[ApplicationID],[Phisical_Name],[Locked],[DocServerID],[Author],[CreatedBy],[CreateDate],[LastupdatedBy],[LastUpdateDate],[Comment],[LastLockedBy],[LastLockedDate],[LastAccessedBy],[LastAccessDate],[published],[PublishDate],[PublishedBy],MR_TYPE
FROM [Profile_Docs]
 WHERE [ProfileID]=@ProfileID
 and (MR_TYPE =@MR_TYPE)
 order by CreateDate desc;
 END;

No comments:

Post a Comment