Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Stored Procedures in SQL

Stored procedures are one of the important areas in database programming. In this article we will discuss about different areas of stored procedure in details.

The actual supremacy of stored procedures is the capability to go by parameters and include the stored procedure which handles the conflicting requirements. In this subject we will look at short-lived parameter principles to a stored procedure.

One Parameter of Stored Procedure

In this instance we will query the Address tuple from the Works database, but in its place of receiving back all proceedings will limit it to immediately a exacting city. This instance assumes an accurate match on the City worth that is approved.

Listing 1: Sample showing One Parameter plan

 
ALTER procedure [dbo].[ProcessEmployeePayScale]      
@ActionType INT,      
@empid INT,      
@scaleid smallint,      
@doeff datetime,      
@dofi datetime,      
@startingbasic numeric(18,2),      
@presentbasic numeric(18,2),      
@dor datetime,      
@slheadid int,      
@amount numeric(18,2),     
@org_id VARCHAR(10),     
@unit varchar(50)      
as begin      
 if(@ActionType=1)      
 begin      
  --DECLARE @SalaryHeadID int      
  SELECT @scaleid = isnull(Max(scaleid),0)+ 1 FROM empPayMst  where org_id=@org_id   

Listing 2: Sample showing One Parameter plan

 
CREATE PROCEDURE GtAddres @City varchar(30)
AS
SELECT * 
FROM Works.Address
WHERE City = @City
GO

In the direction of describing this stored procedure we would implement it as underneath:

EXEC GtAddres @City = 'Kolkata'

We can as well perform the similar obsession, but permit the users to provide us a preliminary tip to look for the information. At this time we will be able to alter the "=" to a LIKE and use the "%" wildcard.

Listing 3: Sample showing like operator

 
CREATE PROCEDURE GtAddres @City varchar(30) 
AS 
SELECT * 
FROM GtAddress 
WHERE City LIKE @City + '%' 
GO

In both of the happening instance it assumes that a stricture worth will forever be approved. If we attempt to perform the process devoid of transitory a stricture worth we will acquire an error note such as the subsequent:

Msg 207, Level 19, State 6, Procedure GtAddres, Line 2

Procedure or function ' GtAddres ' expects parameter '@City', which was not supplied.

Default Parameter Values:

Within nearly all cases it is for all time a high-quality do to go by in each and every one constraint values, but from time to time it is not potential. So in this instance we employ the NULL alternative to permit us to not go by in a constraint value. If we make and run this stored procedure as is it determination not returns any information.

Listing 4: Sample showing Default Parameter plan

 
Create PROCEDURE [dbo].[ProcessPayslip]  
 @empid VARCHAR(30),  
 @empname VARCHAR(100),  
 @email VARCHAR(MAX),  
 @machinename VARCHAR(MAX),  
 @filepath VARCHAR(MAX),  
 @filename VARCHAR(MAX),  
 @month VARCHAR(15),  
 @year VARCHAR(4),  
 @usernm VARCHAR(25),  
 @org_id VARCHAR(10),  
 @dateofsave DATETIME,
 @alterntemail VARCHAR(100),
 --@mailaddress VARCHAR(100),
 @ActionType INT 

Listing 5: Sample showing Default Parameter plan

 
 CREATE PROCEDURE GtAddres @City varchar(30) = NULL
AS
SELECT *
FROM GtAddress
WHERE City = @City
GO

We could alter this stored procedure and employ the ISNULL job to acquire approximately this. So if a worth is approved it resolve use the worth to thin the consequence place and if a value is not accepted it will go back all records.

CREATE PROCEDURE GtAddres @City varchar(30) = NULL
AS
SELECT *
FROM GtAddress
WHERE City = ISNULL(@City,City)
GO

Multiple Parameters:

Setting up multiple parameters is very effortless to achieve. We immediately necessitate enListing every constraint and the data type estranged by a ‘,’ as exposed underneath.

CREATE PROCEDURE GtAddres @City varchar(30) = NULL, @AddressLine1 varchar(60) = NULL
Or

Listing 6: Sample showing Multiple Parameter plan

 
IF @ActionType=2          
BEGIN          
UPDATE [dbo].[EmpMst]          
   SET [firstname] =@firstname          
      ,[middlename] = @middlename          
      ,[lastname] = @lastname          
   ,[empfullname] = @empfullname          
      ,[streetname] =@streetname          
      ,[housename] = @housename          
   ,[houseno]=@houseno          
      ,[pin] =@pin          
      ,[city] =@city          
      ,[district] =@district          
      ,[state] = @state                
      ,[fathername] =@fathername          
      ,[spousename] = @spousename          
      ,[sex] = @sex          
      ,[religion] = @religion          
      ,[caste] =@caste          
      ,[phone] = @phone          
      ,[mobile1] =@mobile1          
      ,[mobile2] = @mobile2          
      ,[email] = @email          
      ,[qualification] = @qualification          
      ,[hqualification] = @hqualification          
      ,[dob] =@dob          
      ,[doj] = @doj          
      ,[dor] = @dor          
      ,[doi] = @doi          
      ,[bankacc] =@bankacc          
      ,[bankname]=@bankname          
      ,[branchname] =@branchname          
      ,[branchadd] = @branchadd          
      ,[IFSCCode] =@IFSCCode          
      ,[designation] =@designation          
      ,[pan] =@pan          
   ,[pfregno]=@pfregno      
   ,[pfnominee]=@pfnominee      
   ,[relationN]=@relationN      
   ,[esiregno]=@esiregno      
      ,[adharcardno] =@adharcardno          
      ,[fmlymemno] =@fmlymemno          
      ,[dobofspouse] =@dobofspouse          
      ,[bloodgroup] =@bloodgroup          
      ,[resgnremarks] =@resgnremarks          
      ,[accounttype]=@AccountType          
   ,[schooladdress1]=@schooladdress1      
   ,[schooladdress2]=@schooladdress2      
      ,[schooldistrict]=@SchoolDistrict          
      ,[schoolname]=@SchoolName   
   ,[resigmemono]=@resigmemono  
   ,[resigmemodate]=@resigmemodate  
   ,[resigproposaldt]=@resigproposaldt  
   ,[rollno]=@rollno  
   ,[alterntemail]=@alterntemail
 WHERE empid=@empid AND [org_id]=@org_id

Disadvantages of Business Logic in the Database:

One of the prime disadvantages is short of ability to produce the application to venture levels. Frequently, the business logic enclosed in an application is pretty multifaceted. If everywhere this logic resides in the database, we will run into locking and blocking issues as well as all-purpose sluggish answer times. This is because SQL Server is intended as a database server, not an application server.

Supplementary issues by means of this move approach if we still have to migrate our application from SQL Server to an additional RDBMS, such as Oracle. SQL Server functions and data types do not unavoidably have a precise communication in Oracle.

Procedure to create Stored Procedures using SQL Server:

Regrettably, in spite of the name of this part, SQL Server doesn't create stored procedures for us. We have to map out how the stored procedure will toil, write down the code, and then correct the code.

However, when we lastly have our code written and implement the CREATE PROCEDURE declaration, SQL Server has to go from end to end a pair of steps earlier than the code is really produced as a stored procedure.

Currently, when CREATE PROCEDURE is executed, SQL Server first parses the declaration to make certain that all statements are syntactically accurate. If the server encounters some kind of syntax error, the formation of the procedure is cancelled and an error is returned to the client. If there are no syntax errors, SQL Server supplies the text of the stored procedure in the syscomments tuple in the database that the user who formed the stored procedure was using at the time CREATE PROCEDURE was executed. Not anything in addition happens until the stored procedure is really executed. Subsequent to the stored procedure is run for the initial time, SQL Server prepares the code controlled in the process to be executed.

Processing structure of Stored Procedures using SQL Server:

While we initially implement a stored procedure, the SQL Server query processor reads the code of the stored procedure from the syscomments tuple and begins the delayed name declaration process.

Deferred Name Resolution:

Deferred name resolution is the procedure of inspection the names of every items so as to a stored procedure uses to make certain to live. Tuples, stored procedures, or additional objects referenced by the stored procedure don't contain to live while the procedure is first formed. All through the decision procedure, not just does SQL Server make sure the substance that are referenced in the stored procedure, other than it too performs other corroboration, such as the subsequent:

  • Ensuring that the column information types in any referenced tuples are friendly with any variables that mention those columns.
  • Scrutinize that any variables and columns approved into SQL Server functions are friendly with the predictable inputs for the functions.

How SQL Server Chooses Query Plans:

While a stored procedure or any additional SQL declaration is sent to the SQL Server, the doubt engine identifies from side to side the process store to observe whether an implementation map has been formed for that stored procedure or SQL declaration. If a map has been stored in the procedure store, SQL Server stores and employs that diagram, therefore cutback the time and operating cost necessary to re-create a new hesitation diagram. If the query analyzer doesn't locate an implementation diagram, it must generate one and store up it in the process cache for potential use. When SQL Server finds the procedure cache to observe whether an implementation diagram exist, it uses an awfully capable technique that approximately forever uses less scheme possessions than compiling a new process. One main warning to the investigate technique SQL Server uses is that it want that all topic names be utterly proficient for it to effort. Come across at the subsequent code:

Listing 7: Sample showing Query plan

 
IF @ActionType=4          
BEGIN          
 SELECT          
      [empid]          
     ,[firstname]          
           ,[middlename]          
           ,[lastname]          
           ,[streetname]          
           ,[housename]          
           ,[houseno]          
           ,[pin]          
           ,[city]          
           ,[district]          
           ,[state]          
           ,[empfullname]          
           ,[fathername]          
           ,[spousename]          
           ,[sex]          
           ,[religion]          
           ,[caste]          
           ,[phone]          
           ,[mobile1]          
           ,[mobile2]          
           ,[email]          
           ,[qualification]          
           ,[hqualification]          
           ,[dob]          
           ,[doj]          
           ,[dor]          
           ,[doi]          
           ,[bankacc]          
           ,[bankname]          
           ,[branchname]          
           ,[branchadd]       
           ,[IFSCCode]          
           ,[designation]          
           ,[pan]          
     ,[pfregno]      
     ,[pfnominee]      
     ,[relationN]      
     ,[esiregno]      
           ,[adharcardno]          
           ,[fmlymemno]          
           ,[dobofspouse]          
           ,[bloodgroup]          
           ,[resgnremarks]          
     ,[accounttype]          
     ,[schooladdress1]      
     ,[schooladdress2]      
      
     ,[schooldistrict]          
     ,[schoolname]    
  ,[resigmemono]  
  ,[resigmemodate]  
  ,[resigproposaldt]  
     ,[rollno]   
	,[alterntemail]
 FROM dbo.EmpMst WHERE [org_id]=@org_id    
order by empfullname        
END 

OUTPUT Parameters:

Information is approved into the stored procedure for dispensation from side to side parameters. Information will be able to be approved not in the procedure by using output parameters. While we make a stored procedure, we can inform the process so as an exacting constraint is departing away to be worn to go by information rear to the request.

After that, while the stored procedure is performed, we have to employ the OUTPUT keyword once more to identify that the stored procedure resolve the transitory information not in and we want to imprison it. Since an evaluation, the syntax significant for the formation of a stored procedure so as to return information from a parameter is as after:

CREATE PROCEDURE <procedure_name>
{<@parameter> <data type>} [VARYING] [=
default] [OUTPUT]][,...
n]
AS
<sql_statements>

Listing 8: Sample showing Output Parameter plan

 
if(@ActionType=4)      
 begin      
  select pm.empid,em.empfullname,pm.scaleid,em.qualification,em.designation,pm.doeff,pm.dofi,      
  pm.startingbasic,pm.presentbasic,pd.dor,pd.slheadid,sm.headname,pd.amount,      
  pd.unit      
  from empPayMst pm      
  left outer join empPayDtls pd on pm.empid=pd.empid and pm.org_id=pd.org_id    
  left outer join SalaryHeadMst sm on pd.slheadid=sm.slheadid  and pd.org_id=sm.org_id    
  left outer join EmpMst em on pm.empid=em.empid  and pm.org_id=em.org_id    
  where pm.empid=@empid  and pm.org_id=@org_id
  order by sm.headname desc  
 end 

Passing an Open Cursor:

One more implementation for OUTPUT parameters is to utilize them to get ahead of an unlock pointer reverse to the calling stored procedure or trigger. This enables us to pass back a recordset that can be scrolled throughout, enabling us to execute operations on numerous rows. There are a few belongings that we have to be conscious of earlier than we employ CURSOR OUTPUT parameters.

  • Stored procedures can employ only the CURSOR data type for OUTPUT parameters. If we employ the CURSOR data type, we have to employ the VARYING and OUTPUT parameters. Similarly, if we identify the VARYING keyword, we have to employ the CURSOR data type and it has to be an OUTPUT parameter.
  • While by forward-only cursors, merely the rows precedent the present positions are approved rear to the calling procedure. For instance, if a cursor is opened containing 700 rows, and 300 rows are fetched as of it previous to, it is approved back to the calling procedure. The calling procedure will obtain a forward-only cursor so as to have merely rows 301 through 700. The 301st row will be measured the first row in the fresh cursor.

Security Mechanism for Stored Procedures:

In stored procedures as a safety instrument, one method can use for safety purpose. As by means of all database applications, we first have to make the tuples so as to path our information. While the tuples are formed, we have to make stored procedures for each information stroke that we will create on those tuples. For instance, if we have a tuple from which we will forever insert and delete information, we have to make one stored procedure that inserts information and one stored procedure that deletes information.

Listing 9: Sample showing Security Mechanism plan

 
ALTER procedure [dbo].[ProcessEmployeePayScale]      
@ActionType INT,      
@empid INT,      
@scaleid smallint,      
@doeff datetime,      
@dofi datetime,      
@startingbasic numeric(18,2),      
@presentbasic numeric(18,2),      
@dor datetime,      
@slheadid int,      
@amount numeric(18,2),     
@org_id VARCHAR(10),     
@unit varchar(50)      
as begin      
 if(@ActionType=1)      
 begin      
  --DECLARE @SalaryHeadID int      
  SELECT @scaleid = isnull(Max(scaleid),0)+ 1 FROM empPayMst  where org_id=@org_id    
  IF NOT EXISTS(SELECT empid FROM empPayMst WHERE empid=@empid and org_id=@org_id)      
  BEGIN      
   insert into empPayMst(empid,scaleid,doeff,dofi,startingbasic,presentbasic,org_id)      
   values(@empid,@scaleid,@doeff,@dofi,@startingbasic,@presentbasic,@org_id)      
      
   insert into empPayDtls(empid,dor,slheadid,amount,unit,org_id)      
   values(@empid,convert(varchar(100),getdate(),106),@slheadid,@amount,@unit,@org_id)      
  END      
  ELSE      
  BEGIN      
   insert into empPayDtls(empid,dor,slheadid,amount,unit,org_id)      
   values(@empid,convert(varchar(100),getdate(),106),@slheadid,@amount,@unit,@org_id)      
  END      
 end      
      
 if(@ActionType=2)      
 begin      
  update empPayMst set scaleid=@scaleid,doeff=@doeff,dofi=@dofi,      
  startingbasic=@startingbasic,presentbasic=@presentbasic      
  where empid=@empid  and org_id=@org_id    
      
  update empPayDtls set dor=convert(varchar(100),getdate(),100),    
  amount=@amount,unit=@unit    
  where empid=@empid and slheadid=@slheadid and org_id=@org_id    
        
 end      
      
 if(@ActionType=3)      
 begin      
  delete from empPayDtls where empid=@empid  and org_id=@org_id    
  delete from empPayMst where empid=@empid  and org_id=@org_id    
 end      
      
 if(@ActionType=4)      
 begin      
  select pm.empid,em.empfullname,pm.scaleid,em.qualification,em.designation,pm.doeff,pm.dofi,      
  pm.startingbasic,pm.presentbasic,pd.dor,pd.slheadid,sm.headname,pd.amount,      
  pd.unit      
  from empPayMst pm      
  left outer join empPayDtls pd on pm.empid=pd.empid and pm.org_id=pd.org_id    
  left outer join SalaryHeadMst sm on pd.slheadid=sm.slheadid  and pd.org_id=sm.org_id    
  left outer join EmpMst em on pm.empid=em.empid  and pm.org_id=em.org_id    
  where pm.empid=@empid  and pm.org_id=@org_id
  order by sm.headname desc  
 end      
      
 if(@ActionType=5)      
 begin      
  select pm.empid,em.empfullname,pm.scaleid      
  from empPayMst pm        
  left outer join EmpMst em on pm.empid=em.empid and  pm.org_id=em.org_id  
  order by em.empfullname  
 end      
end  

Conclusion:

This piece was predestined to give us an operation which is established by stored procedures and SQL Server. We’ll locate that the procedure becomes easier and the settlement perceptible after we do a pair of stored procedures. We have also described different aspects of stored procedures. I would suggest you to write some stored procedure and follow the instructions. Hope you will be enjoying it.



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login