Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Sunday, November 2, 2014

SQL Server -Create Stored Procedure with Parameter

Create Parameterizes STORED PROCEDURE IN SQL :


In this post we learn how to work with Sql-store procedure with parameterize. In my old post we describe what the Store procedure, how many types of procedure in sql and how to create simple store procedure.

Meaning of Parameter:

A Parameter is refers to:

Measurable factor forming one of a set that defines a system or sets the conditions of its operation”.

The meaning of Parameter is a characteristic, feature, or measurable factor that can help in defining a particular system.


How to create parameterizes stored procedure in sql:


In this last post “starting work with store procedure” we explain how to start creating and managing sql store procedure clearly. Now we give the example of how to pass parameter in procedure.


Sql- Create a Stored Procedure by sql code:


Alter Procedure Poc_Login
(
-- Declear variable here .....
@QueryType varchar(50)=null,
@Name varchar(50)='Jon',
@Password varchar(50)=null
)

As
begin
--write your bussionss Login here.
select Name,Password from Login_table where Name=@Name

end


SQL-Create Stored Procedure in SQL Server:

We can create as we want in store procedure similarly. When I am working in my own project the we use separate procedure for each table. By this you can make all possible queries and run these queries when you want by procedure. Here we listed some importance/benefits of sql-procedures.

Benefits of Stored Procedures:

  • Stored Procedures reduce the overhead of compiling each time.
  • Create once and call it N number of times
  • Reduce traffic.
  • You can execute selected Query with store procedure.

How to Run This Procedure in sql server:


USE [my_testing]
GO
DECLARE     @return_value int

EXEC  @return_value = [dbo].[Poc_Login]
SELECT      'Return Value' = @return_value
GO

Result of this Sql Procedure:





Sql Server Interview Qus:



No comments:

Post a Comment