I am using Microsoft AdventureWorks database. In this article, I am going to explain how to return only one record from the left outer join. Regular Left Outer Join As you would notice, it is one-to-many relationship between Product & ProductInventory . For example, Product ID 1 has 3 records in ProductInventory table Output: Modified […]
This can be done using SQL Server application locks (sp_getapplock and sp_releaseapplock). However you need permissions for the account under which the stored proc is executed. The same functionality can be achieved using GLOBAL TEMPORARY TABLES. uspInsertProduct (Stored Proc) In the stored proc, we first check if the global temporary table exists. If it exists […]
DOTNET exposes several ways to achieve mutual exclusion in code such as Mutex, Lock, SynLock, Manual reset event or Thread wait etc. The same thing can be achieved even in SQL Server using application locks. It is achieved by using 2 system stored procs (sp_getapplock and sp_releaseapplock) Permissions: However to be able to call sp_getapplock […]
sp_MSforeachtable allows you to easily process statements against every table in a single database. This could also be done via cursor, but why sweat when we have this stored procedure which has many provisions and options. Parameters sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand @command1 nvarchar(2000) – is the first command to be executed […]
You might run into a requirement where you need to delete all rows from all tables without writing script for each table. This might look trivial but there are things you might need to keep in mind such as if there are foreign key relationships between tables, you have to disable those constrains before deleting […]
HAVING clause is used in conjunction with the SELECT clause to specify a search condition for a group or aggregate. Usually Having clause is used to specify a search condition in a SQL statement which contains group by or some other aggregate condition. However WHERE clause is applied to individual rows and not to groups. […]
In SQL Server 2005 and 2008, the OUTPUT clause makes your DML (INSERT/DELETE/UPDATE) statements return data back to you. In this article we will see examples of how OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.
Transactions are often used within stored procedures to guarantee that all steps succeed or all steps fail. For Example, if you want to insert data into header table and line tables, ideally you would want the data to be inserted into both header and line tables or you do not want both of the inserts to fail.
Using a temporary table is a convenient way to store intermediate results, and then use them at a later phase in our application logic. When using temporary tables SQL Server will inherit the collation for our newly created temporary table from the SQL Server instance default. In case our SQL Server database has a different […]
Imagine having to insert a collection of objects into database. Making a database connection for each object is expensive. Instead it is sometimes advisable to send the whole data (in XML or some other format) to database and have it processed in the stored procedure. This is what we are going to do in this […]