The articles about SQL Server stored procedures, triggers, and cursors that have previously been posted on this blog are collected here. For quick reference only.
Stored Procedures in SQL Server
Introduction: A collection of SQL statements is referred to as a stored procedure in SQL and is stored together in a database. It can execute one or more DML operations on the database and return value, if any, depending on the statements in the procedure and the arguments you pass. As a result, it enables…
SET NO COUNT ON – Stored Procedures
For every statement in a stored procedure, SET NOCOUNT ON stops procedure outcome messages from being sent to the client. In other words, setting NOCOUNT to ON will make messages like “number of rows affected” ineffective. Image Credits: Google. As per the documentation, for stored procedures that contain several statements that do not return much…
CREATE or ALTER STORED PROCEDURE – SQL Server
There are times when we believe that a procedure does not exist in the database and attempt to create it; however, if the procedure already exists, it returns an error, There is already an object named x in the database. If we attempt to ALTER a stored procedure that we believe already exists but does…
Search If A Specific Column is Used in Stored Procedure or Functions – MySQL
INFORMATION SCHEMA (also referred as system catalog) gives you access to database metadata and MySQL server information like the name of a database or table, the data type of a column, and access privileges. We’ll look at how to search a specific column if it’s utilized in Stored Routines (Functions and Stored Procedures) in this…
Last Modified Date – Stored Procedure
Usually, several stored procedures will be provided to DBAs to deploy in the Production environment. Some of the existing stored procedures need to be replaced or sometimes the stored procedures are new to the environment. If the deployment process is not automated, it can cause some confusion in DBAs if the existing stored procedure is…
SQL Server – INSERT through Stored Procedure
There is one post in this blog in which we explored how to use the INSERT statement in different ways. Here is another instance of inserting the data through a stored procedure into the table. Let us create a stored procedure that takes student data and marks as input and returns the student ‘s total…
Triggers in SQL Server
A trigger is a sort of stored procedure that is triggered when an event happens in the database server. There are 3 types of triggers as described below: DML Triggers: DML triggers are triggered when a user attempts to edit data using a data manipulation language (DML) event. These triggers are triggered anytime any eligible…
SQL Server Triggers
This is a list of articles about SQL Server Triggers that have been published on this website. This is intended to be a quick reference.
SQL Server Triggers – Enable and Disable
In this article, you’ll know how to enable and disable triggers in SQL Server. Syntax and Example: DISABLE TRIGGER trgEmpInsDelUpd ON Emp; GO ENABLE TRIGGER trgEmpInsDelUpd ON Emp; GO A disabled trigger is still present in the current database as an object, but it does not fire. Enabling a trigger does not result in its…
SQL Server Triggers – After Insert, Update, and Delete – An example
When a DML statement is issued against a table, a DML trigger is fired. It can execute before or after the DML procedure depending on the condition. In this article, you’ll know how to write a trigger that writes an entry into an audit table, whenever a row is inserted or updated, or deleted on…
SQL Server Triggers – Instead of Delete – An example
An SQL trigger that is executed “instead of” a DML statement is known as an INSTEAD OF trigger. There is absolutely no actual insert, remove, or update activity. Instead, it carries out other commands listed in the trigger. The DML triggers known as INSTEAD OF triggers are those that are triggered in place of triggering…
SQL Server – List-out Procedures, Functions, Triggers
In order to know which procedures, functions, and triggers exist in the database, the following queries can help you to find this out.: The below set of statements can be used to retrieve stored procedures and the number of parameters in it: Use [Test1] Go SELECT SPECIFIC_NAME, COUNT(PARAMETER_NAME) FROM INFORMATION_SCHEMA.PARAMETERS GROUP BY SPECIFIC_NAME The below…
Cursors in SQL Server
An object called a database cursor makes it possible to browse through the rows of a result set. Unlike TSQL commands that operate on all the rows in the result set at one time, it enables you to handle a single row from the result set of a query. Sometimes, you might want to process,…
Example – Cursor in SQL Server
The following gives a SQL Server cursor example. It is useful to back-up all the databases from SQL Server. Please click here for more information about what a cursor is, how to use one, and its drawbacks. /* Variable Declaration */ DECLARE @DB VARCHAR(50) DECLARE @FolderPath VARCHAR(100) DECLARE @FileNamePath VARCHAR(100) — Make sure the specified…
Big Data & SQL 