Table of Contents
You would seen procedures in Oracle that will call a subroutine within the same procedures for 50 times in different lines in the procedure. Now how do you recreate the same procedure in SQL Server?
In SQL Server procedure, If subroutine is performing data modifications or need to use temporary tables(#teporary_table) it needs to be stored procedure. In other cases, you can use create a function. You can choose from different types of functions depending on their complexity and return values:
Simple Stored procedure will look like
DECLARE @Input INT
DECLARE @Result INT
//Some other SQL statements here...
EXEC myStoredProcedure @Input, @Result OUT
In SQL Server there is SQL CLR and you can create functions or procedures (or as called them “subroutines”) in .net code also.
In SQL Server, we have stored procedures and table and scalar functions. Another feature that is convenient are CTE (common table expressions). Please refer for article here for complete CTE details https://sqlserver-dba.co.uk/cte/sql-server-tsql-cte-common-table-expressions.html
For example:
;WITH CTPName AS
(
// complex SQL statements
)
SELECT *
FROM myTable INNER JOIN CTPName ON...
Functions you can use directly in SQL statements (SELECT, INSERT, UPDATE, JOIN), so they seamless to reuse.
A Stored Procedure is a type of SQL code with queries used in SQL that can be stored for later use and can be used as many times needed.
Syntax :
CREATE PROCEDURE procedurename
AS
sql_statements
GO;
To execute a stored procedure –
EXEC procedurename;
Simple Stored procedure Example :
CREATE PROCEDURE Address
AS
SELECT *
FROM homeaddress
GO;
The procedure calls are quick and efficient as stored procedures are compiled once and stored in executable form. Hence the response time is quick. The executable code is automatically cached, hence lowers the memory requirements in SQL Server.
Since the code/ queries are used again and again so, it results in higher productivity.
To create a stored procedure, once you get hang of it, it will be a life saver for repeated queries and parameter inputs can be used for various different outputs. For SSRS reports also, stored procedures make things really easy with parameters.
Stored procedures in SQL Server increase scalability by isolating application processing on the server.
Maintaining a procedure on a SQL server is much easier then maintaining copies on various client machines, this is because scripts are in one location.
Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime. But once you get hang of it, it is fairly simple.
Depending on the database technology, debugging stored procedures will either be very difficult. SQL Server have some debugging capabilities.
Version control is not supported by the stored procedure. But manually can be implemented using commenting and making backup of stored procedures before making any changes.
An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost but will be good money spent.
MIgration of one Database server to another like,from one database type(Oracle) to another database type(MS SQL Server) will need Stored procedures rewriting.
Some of these are already covered in my previous articles…
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error 1222 lock request time out period exceeded Lock request time out…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
This website uses cookies.