|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 10th, 2004, 11:55 AM
|
|||
|
|||
|
Search And Replace Procedure
I have a search and replace stored procedure that I am trying to use.
It works if I hard code the Table Name, Column Name, What to Find and What to Replace it with inside SQL Query Analyzer. But I am trying to put it into a stored procdure like so. Code:
CREATE PROCEDURE [dbo].[Search_And_Replace] @Table VarChar(255), @Column VarChar(255), @Find VarChar(255), @Replace VarChar(255) AS DECLARE @patfind varchar(255) SELECT @patfind = '%' + @Find + '%' UPDATE @Table SET @Column = STUFF(@Column, PATINDEX( @patfind, @Column ), DATALENGTH(@Find ), @Replace ) WHERE @Column LIKE @patfind GO However when I check the syntax I get the error message "Must delcare the variable '@Table' " And "Incorrect syntax near the keyword 'WHERE' "" But I have declared what @Table is up above? Thanks in advanced! Last edited by jmlsgateway : November 10th, 2004 at 12:18 PM.
|
|
#2
November 12th, 2004, 10:44 AM
|
|||
|
|||
|
Solved the problem, if anyone wants to know the solution here is what the final stored procedure looks like
Code:
CREATE PROCEDURE [dbo].[SP_Search_And_Replace]
@Table VarChar(255),
@Column VarChar(255),
@Find VarChar(255),
@Replace VarChar(255)
AS
DECLARE @patfind varchar(255)
SELECT @patfind = '''%' + @Find + '%'''
DECLARE @Query Varchar(5000)
SET @Query = 'UPDATE ' + @Table + '
SET ' + @Column + ' = STUFF( ' + @Column + ', PATINDEX( ' + @patfind + ', ' + @Column + ' ), DATALENGTH(''' + @Find + '''), ''' + @Replace + ''' )
WHERE ' + @Column + ' LIKE ' + @patfind
Exec(@Query)
GO
and then in SQL, I call it like this Code:
EXEC SP_Search_And_Replace @Table = Table1, @Column = Description, @Find = 'GBELT', @Replace = 'Green Belt'
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Search And Replace Procedure |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
