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 […]
You could use following query to find all queries (processes) currently in your server.
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 […]
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.