We can create temporary table(s) using stored procedure for intermediate data manipulation. Below code is used to remove duplicate records from tables using procedure. CREATE TABLE Employee_Test ( Emp_ID int identity, Emp_Name varchar (55) NULL, Emp_Sal decimal (10, 2) NULL, Emp_Designation varchar (20) NULL ) Go INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Mohan',15000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Mohan',15000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Riyaz',16000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Monu',27000,'SSE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE') GO --Now see table SELECT * FROM dbo.Employee_Test

Sometimes we required to remove duplicate records from a table although table has a UniqueID Column with identity. In this article, I would like to share a best way to delete duplicate records from a table in SQL Server. Suppose we have below Employee table in SQL Server. CREATE TABLE dbo.Employee ( EmpID int IDENTITY(1,1) NOT NULL, Name varchar(55) NULL, Salary decimal(10, 2) NULL, Designation varchar(20) NULL ) The da

SQL Injection arises since the fields available for user input allow SQL statements to pass through and query the database directly. SQL Injection: A Simple Example For explaining this issue, Let's create a table "tbluser" for describing the SQL Injection Attack. Create table tbluser ( userName varchar(50) primary key, userpwd varchar(50), address varchar(100) ) insert into tbluser(userName,userpwd,address)values('mohan@gmail.com','123456','Delhi'); insert into tbluser(userName,userpwd,address)values('shailendra@gmail.com','123456','Noida'); insert into tbluser(userName,userpwd,address)values('jitendra@gmail.com','123456','Gurgaon'); insert into tbluser(userName,userpwd,address)values('bipul@gmail.com','123456','Delhi'); select * from tbluser

Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers. Types of DML Triggers After Trigger (using FOR/AFTER CLAUSE) This trigger fires after SQL Server completes the execution of the action successfully that fired it. Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Group By Clause Group By clause is used for grouping the records of the database table(s).This clause creates a single row for each group and this process is called aggregation. To use group by clause we have to use at least one aggregate function in Select statement. We can use group by clause without where clause. Syntax for Group By Clause SELECT Col1, Col2, Aggreate_function FROM Table_Name WHERE Condition GROUP BY Col1, Col2 Let's see how the Group By clause works. Suppose we have a table StudentMarks that contains marks in each subject of the student. Create table StudentMarks

Suppose we have employee name and salary as shown in below fig. Query to get…

Remove all Tables -- drop all user defined tables EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" Remove all User-defined Stored Procedures -- drop all user defined stored procedures Declare @procName varchar(500) Declare cur Cursor For Select [name] From sys.objects where type = 'p' Open cur Fetch Next From cur Into @procName While @@fetch_status = 0 Begin Exec('drop procedure ' + @procName) Fetch Next From cur Into @procName End

CREATE FUNCTION ConvertListToTable ( @Delimiter    nvarchar(5), @List        nvarchar(max) ) RETURNS @TableOfValues TABLE ( RowNumber  …

CREATE FUNCTION GetSortSql ( @SortBy nvarchar(100), @SortAscending BIT, @Default nvarchar(100) ) RETURNS nvarchar(max) AS BEGIN…

create FUNCTION SplitString ( @sString nvarchar(2048), @cDelimiter nchar(1) ) RETURNS @tParts TABLE ( part nvarchar(2048)…