Stored Procedures Make things Better

Stored Procedures are a set of SQL(T-SQL) statements that compiled in to single execution plan. Think it like Method in Class the method is stored procedure and Class is Database

Benefits of Stored Procedures

 1. Increase Program performance
 Stored procedures are compiled only once and stored it as executable form and that executable cached and shared among all users.Stored procedures execute within a single call even though it has many SQL statements in the body.

2. Interoperability
If you work with stored procedure you don't need to change lot of code lines to make it functional.Stored procedure call is just enough. Each of modern languages have specific libraries for calling stored procedures

3. Security
You can restrict the data access in database with stored procedures. Ex: Just only for read.

4. Scalability
Stored procedures increase scalability by isolating application processing on the server. In addition, automatic dependency tracking for stored procedures aids the development of scalable applications.

5.  Maintainability
Once it is validated, a stored procedure can be used with confidence in any number of applications. If its definition changes, only the procedure is affected, not the applications that call it. This simplifies maintenance and enhancement. Also, maintaining a procedure on the server is easier than maintaining copies on various client machines.  


Stored Procedure Outputs

Stored procedures can return data or cursor values . Return codes in stored procedure always returns integer values and cursor can reference to out side . And there is return data sets for select statement results.

Lets Work on it

I'm working on local database file that attached with my project in Visual studio. Bit there is no difference between SQL Server procedure creation and Procedure creation in Visual studio.

You can add your own SQL Server database to the project.
CTRL+SHIFT+A -> Data->SQL Server Database

Here is tables that I'm going to working on

CREATE TABLE [dbo].[PurchaseHistory] (

    [Id]        INT              IDENTITY (1, 1) NOT NULL,

    [UserId]    UNIQUEIDENTIFIER NOT NULL,

    [AlbumId]   INT              NOT NULL,

    [Units]     INT              DEFAULT ((0)) NOT NULL,

    [Buydate]   DATETIME         DEFAULT (getdate()) NOT NULL,

    [Payed]     BIGINT           DEFAULT ((0)) NOT NULL,

    [PayedDate] DATETIME         NULL,

    [Total]     FLOAT (53)       DEFAULT ((0.00)) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)

);

CREATE TABLE [dbo].[Users] (

    [ApplicationId]    UNIQUEIDENTIFIER NOT NULL,

    [UserId]           UNIQUEIDENTIFIER NOT NULL,

    [UserName]         NVARCHAR (50)    NOT NULL,

    [IsAnonymous]      BIT              NOT NULL,

    [LastActivityDate] DATETIME         NOT NULL,

    PRIMARY KEY CLUSTERED ([UserId] ASC),

    CONSTRAINT [UserApplication] FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[Applications] ([ApplicationId])

);

CREATE TABLE [dbo].[Albums] (

    [AlbumId]        INT           IDENTITY (1, 1) NOT NULL,

    [AlbumName]      VARCHAR (255) NOT NULL,

    [ImageSource]    VARCHAR (50)  NOT NULL,

    [Artist]         VARCHAR (255) NOT NULL,

    [Price]          MONEY         NOT NULL,

    [Description]    VARCHAR (MAX) NOT NULL,

    [Tracks]         INT           NULL,

    [Label]          INT           NOT NULL,

    [OrigYear]       INT           NULL,

    [AdditionalInfo] VARCHAR (550) NULL,

    [Rating]         INT           NULL,

    PRIMARY KEY CLUSTERED ([AlbumId] ASC)

);

And make sure your tables containing data to test our procedures

Expand your database objects from Server explorer and R-Click on the stored procedures and select Add New Stored procedure

If you working on SQL Server Add New Stored Procedure is on Programability->Stored Procedures 

Here is my requirement is create procedure that returns all unpayed purchases from PurchaseHistory by selected user with Album details. This selected user will indicate by user input from outside. It will take userName as parameter

If we took it as function its like this
List<Object> Procedure(string UserName)

Here is Procedure for it. My procedure name is GetAllCartProcedure

CREATE PROCEDURE [dbo].[GetAllCartProcedure]

    @UserName varchar(255)

AS

    BEGIN

        DECLARE @UserId uniqueidentifier

        DECLARE @Total money

        --select userId from the user table to make connection between Purchase history

        SELECT @UserId=(UserId) FROM Users WHERE UserName=@UserName

        --Select all purchases that not payed by selected User

        SELECT

            Albums.AlbumName,'Images/AlbumImages/'+Albums.ImageSource,Albums.Price,

            PurchaseHistory.Buydate,PurchaseHistory.Units,PurchaseHistory.Total

        FROM Albums,PurchaseHistory

        WHERE

            Albums.AlbumId=PurchaseHistory.AlbumId

            AND

            PurchaseHistory.UserId=@UserId

            AND

            PurchaseHistory.Payed=0         

    END

RETURN

After procedure created you ca test the procedure on R-Click on the created procedure

It will prompt this dialogbox and give the relevent parameters to the prompt and check

Or Use T-SQL

USE [aspnet-E commerce-20140712191038]

GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[GetAllCartProcedure]

        @UserName = 'UseName'

SELECT  @return_value as 'Return Value'

GO

Here is result

You can access those stored procedures easily with entity framework and ADO.NET libraries. Use those with those and make better code..