Murugan.com
Murugan Andezuthu Dharmaratnam

  |  HOME   |  BLOG   |  TWITTER   |  ARTICLES   |  8086  |  C++   |  VC++   |  ASP .NET   |  VB .NET   |  JAVA SCRIPT   |  MS SQL   |  PHP   |  MY   |  VIDEOS   |  DOWNLOADS   |  CONTACT ME   |  



Generic Stored Procedure Insert Update Delete


Home  > MSSQL  > Generic Stored Procedure Insert Update Delete 
       
Generic Stored Procedure for insert update and delete. . U need to add the functions also. 

USE [CommunityPortal]
GO

/****** Object:  StoredProcedure [dbo].[usp_GenericInsertUpdateDelete]    Script Date: 10/13/2011 13:36:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Murugan Andezuthu Dharmaratnam
-- Create date: September 21 2011
-- Description:	Generic SP to be used in Master Forms with less fields to insert update & Delete rows from a table
-- =============================================
CREATE PROCEDURE [dbo].[usp_GenericInsertUpdateDelete] 
	-- Add the parameters for the stored procedure here
	@StatementType nvarchar(20) = null,
	@TableNameText nvarchar(250) = null,
	@ColoumnsText nvarchar(MAX) = null,
	@ValuesText nvarchar(MAX) = null,
	@FilterText nvarchar(MAX) = null,
	@LastUpdated datetime = null,
	@UserID nvarchar(20) = null,
	@Code nvarchar(20) = 'noGenerate'
AS
	DECLARE @SQLString nvarchar(MAX)
	DECLARE @GeneratedCode int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF @StatementType = NULL
		SELECT ''
	ELSE IF @StatementType = 'Insert'
	BEGIN
		IF @TableNameText IS NOT NULL  AND @ColoumnsText IS NOT NULL AND @ValuesText IS NOT NULL 
		BEGIN
			IF @Code = 'Generate'
			BEGIN
				SELECT @GeneratedCode = dbo.GetCode('GNumber','')	
				SELECT @GeneratedCode
				SELECT @SQLString = 'INSERT INTO ' + @TableNameText+'('+@ColoumnsText+') '+ 'Values('''+REPLACE(@ValuesText,',',''',''')+''')' + CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END
			END
			ELSE
			BEGIN
				SELECT @SQLString = 'INSERT INTO ' + @TableNameText+'('+@ColoumnsText+') '+ 'Values('''+REPLACE(@ValuesText,',',''',''')+''')' + CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END
			END
			EXEC(@SQLString)
		END
		SELECT @@ROWCOUNT
	END ELSE IF @StatementType = 'Update' 
	BEGIN
		IF @TableNameText IS NOT NULL  AND @ColoumnsText IS NOT NULL AND @ValuesText IS NOT NULL 
		BEGIN
			IF dbo.NameValueUpdate(@ColoumnsText,@ValuesText) != ''
			BEGIN
				SELECT @SQLString = 'UPDATE '+ @TableNameText+' SET ' + dbo.NameValueUpdate(@ColoumnsText,@ValuesText) + CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END
				EXEC(@SQLString)
			END
		END
		SELECT @@ROWCOUNT
	END ELSE IF @StatementType = 'Delete'
	BEGIN
		IF @TableNameText IS NOT NULL  AND @FilterText IS NOT NULL 
		BEGIN
			SELECT @SQLString = 'DELETE FROM ' + @TableNameText + CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END
			EXEC(@SQLString)
		END
		SELECT @@ROWCOUNT
	END
END


GO




=====================================================================================================

SQL Function to get name value paid for update. 

USE [CommunityPortal]
GO

/****** Object:  UserDefinedFunction [dbo].[NameValueUpdate]    Script Date: 10/13/2011 13:37:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Murugan Andezuthu Dharmaratnam
-- Create date: Sep 26 2011
-- Description:	Generate Name Value or Coloum Value Pair To be Used in Update Statement for Generic Stored Procedure for Update
-- =============================================
CREATE FUNCTION [dbo].[NameValueUpdate]
(
	-- Add the parameters for the function here
	@Name nvarchar(MAX) = null,
	@Value nvarchar(MAX) = null
)
RETURNS nvarchar(MAX)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @strNameValue nvarchar(MAX) = ''
	DECLARE @iNameIndex int = 0
	DECLARE @iValueIndex int = 0
	DECLARE @i int = dbo.CharCount(@Name,',')
	SELECT @Name = @Name + ','
	SELECT @Value = @Value + ','
	IF dbo.CharCount(@Name,',') = dbo.CharCount(@Value,',') AND @Name IS NOT null AND  @Value IS NOT null
	-- Add the T-SQL statements to compute the return value here
		WHILE @i >= 0
		BEGIN
			SELECT @strNameValue = @strNameValue + SUBSTRING(@NAME,@iNameIndex,CHARINDEX(',',@NAME,@iNameIndex)-@iNameIndex) + '=''' + SUBSTRING(@VALUE,@iValueIndex,CHARINDEX(',',@VALUE,@iValueIndex)-@iValueIndex) + ''' ' + CASE WHEN @i = 0 THEN '' ELSE ',' END
			SELECT @iNameIndex = CHARINDEX(',',@NAME,@iNameIndex)+ 1
			SELECT @iValueIndex = CHARINDEX(',',@VALUE,@iValueIndex) + 1
			SELECT @i = @i - 1
		END
	-- Return the result of the function
	RETURN @strNameValue
END


GO



=======================================================================

Function To Count the offcurance of character in a string

USE [CommunityPortal]
GO

/****** Object:  UserDefinedFunction [dbo].[CharCount]    Script Date: 10/13/2011 13:37:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Murugan Andezuthu Dharmaratnam
-- Create date: Sep 26 2011
-- Description:	Count the Number of Occurance of specified Character or substring in a String
-- =============================================
CREATE FUNCTION [dbo].[CharCount]
(
	-- Add the parameters for the function here
	@Input nvarchar(MAX),
	@Char nvarchar(MAX)
)
RETURNS int 
AS
BEGIN
	-- Return the result of the function
	RETURN (LEN(@Input) - LEN(REPLACE(@Input, @Char, '')))

END

GO

=====================================================================================

Example of calling delete

EXEC usp_GenericInsertUpdateDelete 'Delete','Country','','','CountryCode=91'






index

create udl file to test sql connection to the sql server

Error: 18452 SQL Server 2005 "Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection."

How to copy a table with data to one db to another in sql 2005

How to insert multiple records to a table using a single Insert query

How to insert values to multiple tables using a single insert query statement

How to replace a part of a string of a field using query in MSSQL

ms sql Add field to table using Alter Table

ms sql server 2005 express edition

MSSQL What is the use of COALESCE() function

System.Data.SqlClient.SqlError The backup set holds a backup of a database other than the existing

What are the different login modes in sql server

where is sql server 2005 files stored

Windows 7 SQL Server 2008 Express R2 Error Unable to connect from remote machine desktop

sql server error exception system.data.sqlclient.sqlerror the media set has 2 media families but only 1 are provided

Restore a database to another database The backup set holds a backup of a database other than the existing

Stored Procedure Introduction

How to execute a stored procedure on the sql server query analzer

Create Stored Procedure Example For beginner

stored procedure variable orderby

Stored Procedcure to Update Table

Stored Procedure Input Variables

not getting sql server properties on clicking udl file opening as text

Generic Stored Procedure To Return Get Data From Table With Paging

Single Stored Procedure Insert Update Delete

Stored Perocedure ROWCOUNT

Invalid use of a side effecting operator EXECUTE STRING within a function

SQL Query MAX Value column

MSSQL Select GroupBy

SQL Occurrence of character in a string

TSQL Functions

t sql string manipulation STRING BETWEEN TWO STRINGS

TSQL CHECK IF NULL

Generic Stored Procedure Insert Update Delete

Cannot call methods on nvarchar max



  |  HOME   |  BLOG   |  TWITTER   |  ARTICLES   |  8086  |  C++   |  VC++   |  ASP .NET   |  VB .NET   |  JAVA SCRIPT   |  MS SQL   |  PHP   |  MY   |  VIDEOS   |  DOWNLOADS   |  CONTACT ME   |  

Copyright 2009 @ Murugan Andezuthu Dharmaratnam