They are created one stored procedure but i can't understand.

They are created one stored procedure but i can't understand.if any know knows this stored procedure pls Explain what functionality the are used and tell me with examples.





SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




Create PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN



SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME( @SearchStr ,'''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) and (@TableName != 'eLogs_Master_Company')
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= PARSENAME(@TableName, 2)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
if(@TableName <> '[dbo].[eLogs_Master_Company]')

SET @SQL='UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' = ' + @SearchStr2

EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT CAST(@RCTR AS varchar)
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Thanks&Regs

Magesh.A

Dear,

        This procedure only works for 'char', 'varchar', 'nchar', 'nvarchar' Datatypes.

        In This Procedure you can replace Any Fields of above datatypes cell values of any of the table
         of your current database.

        Try to check below example
        Where I create one table with 2varchar fields And one Autogenerated primary key.
        Then i passed value of last cell 'Ajeet' And  new value 'Ajit' So procedure itself search the value And Replace by your second
        Value.
        i.e. 'Ajeet' Replaced by 'Ajit'


-- Create Table
CREATE TABLE [dbo].[TblUsers](
    [UId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [UName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Pwd] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
) ON [PRIMARY]

GO
-- Insert Values
INSERT INTO [dbo].[TblUsers] ([UName],[Pwd]) VALUES('Tedulkar','Sachin')
INSERT INTO [dbo].[TblUsers] ([UName],[Pwd]) VALUES('Sewag','Virendra')
INSERT INTO [dbo].[TblUsers] ([UName],[Pwd]) VALUES('Ganguli','Saurav')
INSERT INTO [dbo].[TblUsers] ([UName],[Pwd]) VALUES('Dhoni','Mahi')
INSERT INTO [dbo].[TblUsers] ([UName],[Pwd]) VALUES('Agarkar','Ajeet')

GO
-- Check Records
Select * From TblUsers
GO
-- Execute Procedure
Exec SearchAndReplace @SearchStr = 'Ajeet', @ReplaceStr = 'Ajit'

GO
-- Again Check Records To see Updation
Select * From TblUsers


Try it and let me know if you want to understand more.

Its great logical thing to change same Records of whole database at once execution.