Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

Create, Alter, Drop and Execute SQL Server Stored Procedures

- 23.09.19 | 22.11.19 - ErcanOPAK

A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table.  A block of T-SQL code can be saved in a T-SQL script file.  You can also store the code from a script file in a stored procedure.

There are several benefits that result from saving code in a stored procedure rather than a script file.  These are some examples.

  • You do not need to expose the code in a stored procedure in order to run its T-SQL code.  In contrast, users need to open a script file with its code in order to run the code.
  • Stored procedures also offer a means of limiting access to the underlying tables for a query.  By granting access to run stored procedures without permission to read or write to the underlying tables, you can secure data but still allow visibility for data in the underlying tables through a stored procedure.
  • You can use input parameters with stored procedures to vary the operation of the code inside a stored procedure.  While script files do allow the use of local variables to modify the return sets from queries, script files must expose their code to allow you to modify local variables at run time.
  • By gaining proficiency in segmenting a programming solution into parts based on stored procedures, you make it easier to change code over time.  By adding code in short modular scripts, each script can be easier to read and maintain and even re-use in other applications.  Solutions based on SQL files with scripts for queries can become increasingly long, difficult to read, and maintain as successive changes continue to be made to a solution.

Stored procedures introduce a level of abstraction between the code for a solution and using the code that is not present when you maintain your code in a script file.  Therefore, if you have a simple solution that is used by one user who needs to have access to the underlying data sources for a query (or set of queries), then a script file may be better because it simplifies the solution.

CREATE:

CREATE PROCEDURE MyFirstSP

AS

BEGIN

SELECT Column1, Column2, Column3 FROM Table

END

EXECUTE (RUN):

EXEC MyFirstSP

ALTER (UPDATE):

ALTER PROCEDURE MyFirstSP

AS

BEGIN

SELECT Column1, Column2 FROM Table WHERE Column3 > 10

END

DROP (DELETE):

DROP PROCEDURE MyFirstSP

Related posts:

SQL — TRUNCATE Resets Identity Values

SQL — TRUNCATE Resets Identity Values

SQL Server: Fix a Query That Runs Instantly in SSMS but Times Out From the App Because ARITHABORT De...

SQL Server: Fix a Query That Runs Instantly in SSMS but Times Out From the App Because ARITHABORT De...

How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL

Views: 120

Post navigation

SQL Query to Find and Replace text in a stored procedures
How to solve “A cursor with the name already exists” problem?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com