MVC

  • Home
  • HTML
    • Overview
    • Formatting
  • Java Script
    • Overview
    • Validation
  • CSS
    • Overview
  • Sql
    • Overview
    • SQL Select
    • SQL Command
    • Advance Query
    • SQL Create
    • Function
    • Function vs StoreProcedure
    • Stored Procedures
  • MVC
    • OverView
    • Evolution of MVC
    • Install VisualStudio-2015
    • Create First Application
  • LINQ
    • Introduction
  • Privacy Policy

Friday, 31 March 2017

What Is Store Procedure

  Unknown       08:26       StoredProcedures       No comments    

Stored Procedure in SQL Server can be defined as the set of logically group of SQL statement which are grouped to perform a specific task. We Use stored procedure is that it increases the performance of the database. stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL injection. </ br>

Create Store Procedure in SQL Server


Step 1 : Firstly Create Open Computer Serach SQl Server Click and Open.
Step 2 : Create Databse like in Example as below.
EX.
CREATE DATABASE Db_Employee

Step 3 : Write this command in Sql Server and Run (F5) This Command. We Use This Db .
USE Db_Employee

Step 4 : After that created Database Create New Table With Tbl_Employee Name.
Ex.
CREATE TABLE Tbl_Employee
(
Id int,
Name NVARCHAR(50)
)
Step 5 : Created Table Then Create Procedure as Below -

Syntex-

Create Procedure Sp_Name ( @paerameter ParameterType(Size) ) Write Sql Query

Example

In this example we will query the Tbl_Employee table from the Db_Employee database, but instead of getting back all records we will limit it to just a particular Employee. This example assumes there will be an exact match on the Employee value that is passed.
USE Db_Employee GO CREATE PROCEDURE dbo.SP_Employee @Name nvarchar(30) AS SELECT * FROM Tbl_Employee WHERE Name= @Name Note- Here SP_Employee Store Procedure Name @Name is a Parameter and we give Multiple Parameter also.

Execute of Store Procedure

EXEC SP_Employee @Name= 'Ram'

How TO Alter Store Procedure

Syntex- ALTER PROCEDURE Procedure_Name EXAMPLE ALTER PROCEDURE SP_Employee @Name= 'Ram'

Advantage Of Using Store Procedure

1. Using the Stored procedure is that it reduces the amount of information sent to the database server. 2.Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time. 3.Code Usability User Can Call Multiple time Store Procedure. 4.Using Store Procedure Maintain Security. 5.In SQL Server we have different types of stored procedures: System stored procedures User-defined stored procedures Extended stored Procedures System-stored procedures are stored in the master database and these start with a sp_ prefix. These procedures can be used to perform a variety of tasks to support SQL Server functions for external application calls in the system tables Example: sp_helptext [StoredProcedure_Name] User-defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use the sp_ prefix because if we use the sp_ prefix first, it will check the master database, and then it comes to user defined database. Extended stored procedures are the procedures that call functions from DLL files. Nowadays, extended stored procedures are depreciated for the reason it would be better to avoid using extended stored procedures.

Count number of words in string through function in SQL


In This article make a function in sql server to find out the how may time word occurrence in string and you can count how many words present in this string through function in sql server

CREATE FUNCTION fnCountOccurences      
               (@ShortString VARCHAR(100),      
                @LongString  VARCHAR(8000))      
RETURNS INT      
AS      
  BEGIN      
    DECLARE  @Text      VARCHAR(8000),      
             @Frequency INT      
          
    SET @Text = @LongString      
          
    SET @Text = REPLACE(@Text,@ShortString,'')      
          
    SET @Frequency = (len(rtrim(@LongString)) - len(rtrim(@Text)))      
                    / len(rtrim(@ShortString))      
          
    RETURN (@Frequency)      
  END

We Get OutPut:-

 SELECT[dbo].[fnCountOccurences](',','a,b,c,c,')
Execute
OutPut- 4



  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
Email This BlogThis! Share to X Share to Facebook
Newer Post Older Post Home

0 comments :

Post a Comment

learningmvctutorial

Popular Posts

  • Difference Between function v/s store procedure
    Definition  of Store Procedure In a database management system (DBMS), a  stored procedure  is a set of Structured Query Language (SQL...
  • Overview of HTML
    What is HTML? First developed by Tim Berners-Lee in 1990 . HTML is used to create electronic documents (called pages) that are displ...
  • Overview
      SQL (Structured Query Language)   SQL  is a language that interacts with the database on the server-side. This dynamite duo has changed ...
  • What is LINQ
    LINQ  (Language Integrated Query) is a Microsoft programming model and methodology that essentially adds formal query capabilities into Mic...
  • What is Function in Sql Server
    A function is a block of code that has a name and it has a property that it is reusable. We use function in store Procedure and queries in...
  • About Javascript
    What is JavaScript? JavaScript was designed to add interactivity to HTML pages JavaScript is a scripting language A scripting langua...
  • Create Duplicate Table of existing table in SQL Server
    In This Tutorial we create table and want to same structure and same data then We create new table with data type after that  insert in ta...
  • Operator in JavaScript
    What is an operator? JavaScript has both binary and unary operators, and one special ternary operator, the conditional operator. A binar...
  • SQL Select
    The SQL SELECT Statement The SELECT statement is used to select data from a database. SQL SELECT Syntax SELECT column_name , column_n...
  • Create First Application in MVC
    We want to create Firstly application in MVC first of how we create first application Show Steps   as below-     STEP 1 .  Start Computer ...

Blog Archive

  • ▼  2017 ( 35 )
    • ►  November ( 1 )
    • ►  October ( 2 )
    • ►  September ( 4 )
    • ►  August ( 1 )
    • ►  July ( 1 )
    • ►  May ( 3 )
    • ►  April ( 7 )
    • ▼  March ( 13 )
      • What Is Store Procedure
      • Create First Application in MVC
      • How To Install Visual Stdio Step by Step Process
      • Overview of CSS
      • Overview of HTML
      • What is LINQ
      • Overview
      • Difference Between function v/s store procedure
      • What is Function in Sql Server
      • How To Create DataBase & Table in SQL Server
      • SQL Select
      • Open Application in MVC
      • MVC OverView
    • ►  February ( 3 )
Powered by Blogger.

Categories

  • AdvanceQuery
  • CreateApplication
  • CreateDb
  • CssOverview
  • Evolution
  • Formatting
  • Function
  • FunctionVSSp
  • HtmlOverview
  • InstallVisualStudio
  • Introduction
  • javascriptOverview
  • JsValidation
  • Mvc-vs-Asp.Net
  • OverView
  • Select
  • SqlCommand
  • SQLOverview
  • StoredProcedures

Text Widget

Sample Text

Pages

  • Home

Copyright © MVC | Powered by Blogger
Design by Vibha Acharya