Generic CRUD Operations(Insert Update Delete Select)

    This procedure will gives you complete solution for creating a generic insert,update,delete and select. We have to just pass table name and the expected parameters. When we pass all this parameters to that procedure we can handle DML in generic manner.

Procedure:

USE [AssetManagement]
GO
/****** Object:  StoredProcedure [dbo].[MASTERCRUDOPERATIONS]    Script Date: 10/04/2014 11:28:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MASTERCRUDOPERATIONS]
(
@TABLENAME  VARCHAR(MAX),--Table Name-->UserMaster
@TABLEPARAM  VARCHAR(MAX),--Table Parameter-->Param1|~Splitter~|Param2
@IdParam VARCHAR(MAX),--Id-->10
@Action INT=1--Action-->1
)

AS
BEGIN
--DeviceModelMaster
--BrandMaster

--DECLARE @TABLENAME VARCHAR(MAX) = 'AssetMaster';
--DECLARE @TABLEPARAM VARCHAR(MAX) = 'ASD|~Splitter~|ASDASD';
--DECLARE @IdParam VARCHAR(MAX) = '1';

SET NOCOUNT ON;

DECLARE @COLNAMES VARCHAR(MAX)='';
DECLARE @COLVALUES VARCHAR(MAX)='';
DECLARE @UPDATECOLS VARCHAR(MAX)='';
DECLARE @UPDATECONDITIONS VARCHAR(MAX)='';
DECLARE @PARAM INT =1;
DECLARE @PARAM1 INT =1;

DECLARE @INSERTQUERY VARCHAR(MAX)='';
DECLARE @UPDATEQUERY VARCHAR(MAX)='';
DECLARE @DELETEQUERY VARCHAR(MAX)='';
DECLARE @SELECTQUERY VARCHAR(MAX)='';
DECLARE @SELECTWITHCONDITIONSQUERY VARCHAR(MAX)='';

SELECT ROW_NUMBER() OVER(ORDER BY column_id)[NO],*
INTO #temp
FROM sys.columns WHERE 
OBJECT_ID IN
(
SELECT OBJECT_ID FROM sys.tables WHERE NAME=@TABLENAME
)

SELECT @COLNAMES=@COLNAMES+'['+name+'],',
@COLVALUES=@COLVALUES+'''|~Param'+CONVERT(VARCHAR(100),@PARAM)+'~|'',',
@UPDATECOLS = @UPDATECOLS+'['+name+']='+'''|~Param'+CONVERT(VARCHAR(100),@PARAM)+'~|'',',
@PARAM=@PARAM+1
FROM #temp
WHERE [NO]>1
ORDER BY column_id


SELECT @UPDATECONDITIONS='['+name+']'  
FROM #temp
WHERE [NO]=1
ORDER BY column_id

SET @COLNAMES=SUBSTRING(@COLNAMES,0,LEN(@COLNAMES))

SELECT @COLVALUES=SUBSTRING(@COLVALUES,0,LEN(@COLVALUES))

SELECT @UPDATECOLS=SUBSTRING(@UPDATECOLS,0,LEN(@UPDATECOLS))

IF @Action = 1 --Select All records

BEGIN 

SELECT @SELECTQUERY=('SELECT '+@COLNAMES+' FROM '+ @TABLENAME)

EXEC(@SELECTQUERY)

END
IF @Action = 2--Select records With Conditions

BEGIN 

SELECT @SELECTWITHCONDITIONSQUERY=('SELECT '+@COLNAMES+' FROM '+ @TABLENAME +' WHERE '+@UPDATECONDITIONS+'='+@IdParam)

EXEC(@SELECTWITHCONDITIONSQUERY)

END
IF @Action = 3--Insert Records

BEGIN 

SELECT @INSERTQUERY=('INSERT INTO '+@TABLENAME+'('+ @COLNAMES +') VALUES ('+ dbo.SETPARAMETERS(@COLVALUES,@TABLEPARAM)+')')

EXEC(@INSERTQUERY)

END
IF @Action = 4--Updates Records

BEGIN 

SELECT @UPDATEQUERY=('UPDATE SET '+dbo.SETPARAMETERS(@UPDATECOLS,@TABLEPARAM)+' WHERE '+@UPDATECONDITIONS+'='+@IdParam)

EXEC(@UPDATEQUERY)

END
IF @Action = 5--Delete Records

BEGIN 

SELECT @DELETEQUERY=('DELETE FROM '+ @TABLENAME +' WHERE '+@UPDATECONDITIONS+'='+@IdParam)

EXEC(@DELETEQUERY)

END

DROP TABLE #temp;

SET NOCOUNT OFF;

END

Dependent Function:

[dbo].[Split]

GO

/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 10/04/2014 01:37:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter varchar(MAX))    

returns @temptable TABLE (items varchar(MAX))    

as    

begin    

    declare @idx int    

    declare @slice varchar(8000)    

    select @idx = 1    

        if len(@String)<1 or @String is null  return    

    while @idx!= 0    

    begin    

        set @idx = charindex(@Delimiter,@String)    

        if @idx!=0    

            set @slice = left(@String,@idx - 1)    

        else    

            set @slice = @String

     

        SET @idx=@idx+LEN(@Delimiter)  

        if(len(@slice)>0)

            insert into @temptable(Items) values(@slice)    

        set @String = substring(@String, @idx,len(@String))  

        if len(@String) = 0 break    

    end

return

end;

 [dbo].[SETPARAMETERS]

USE [AssetManagement]
GO
/****** Object:  UserDefinedFunction [dbo].[SETPARAMETERS]    Script Date: 10/04/2014 11:27:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[SETPARAMETERS]
(
@COLVALUES VARCHAR(MAX),
@TABLEPARAM VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @ReturnString VARCHAR(MAX);

DECLARE @tempTable TABLE ([NO] INT IDENTITY,items VARCHAR(MAX))

DECLARE @INCR INT=0

INSERT INTO @tempTable
(
items
)
SELECT items
FROM dbo.Split(@TABLEPARAM,'|~Splitter~|')

WHILE EXISTS(SELECT 1 FROM @tempTable)
BEGIN

DECLARE @Param VARCHAR(MAX);

SET @INCR=@INCR+1;

SELECT @Param=items FROM @tempTable WHERE  [NO] = @INCR;

SET @COLVALUES=REPLACE(@COLVALUES,'|~Param'+CONVERT(VARCHAR(MAX),@INCR)+'~|',@Param);

DELETE FROM @tempTable WHERE  [NO] = @INCR;

END

RETURN @COLVALUES

END

Example:

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[MASTERCRUDOPERATIONS]
@TABLENAME = N'CustomerMaster',
@TABLEPARAM = N'Test1|~Splitter~|Test2',
@IdParam = N'1',
@Action = 3

SELECT 'Return Value' = @return_value

GO

SQL Comparer

     Every times developers creates there project under small span of time. In that case they create n number of procedures and function and do thousands of changes but it is difficult to remember all changes to single developer. It is best practice to write all procedures and all changes to one excel or notepad. But If we can not handle all details then we can use SQL Comparer which is created for this scenario only.

Simple Steps

1. Login to your database and create connection.

    

2. Select which type of object you want to compare and select version.

4. Just Compare It.

4. Or get version of Procedures.

Web Boot Screen

              Web boot screen is the app which  is web based operating system. The idea behind this is to create operating system on the web but this is only the prototype for entire idea. Just Check this screen which is shared below.