Modify columns using stored procedure in SQL Server

Question

I wish to modify strings in several columns (for example all columns containing the 'sound' string), for example replacing ',' by '.'. Further to this post, I understand I have to use dynamic SQL. I created the following procedure:

USE [myDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[RemoveStringInColumn] (@colName varchar(50), @tableName varchar(50), @to_remove varchar(50), @to_add varchar(50))
AS

DECLARE @sql nvarchar(4000)
SET @sql = 'UPDATE ' + @tableName + ' SET ' + @colName + ' = REPLACE(' + @colName + ',' + @to_remove + ','+ @to_add + ');'
PRINT @sql
EXEC sp_executesql @sql

Which is called by:

EXEC dbo.RemoveStringInColumn 'COL_1', 'TABLE_1', ',', '.'

1) The problem is the @sql command does not contain the little hyphen arond the comma and the dot. How can I solve this?

2) In this post they use a SELECT command to fetch all column names. So far, I managed to fetch column names containing 'sound'.

select COLUMN_NAME AS my_cols
from INFORMATION_SCHEMA.COLUMNS 
where table_name = 'TABLE_1' AND COLUMN_NAME LIKE '%sound%'

How can I put column names into a list and use a for loop to go through them calling the RemoveStringInColumn procedure?

Thanks


Show source
| sql-server   | sql   2017-01-03 16:01 1 Answers

Answers to Modify columns using stored procedure in SQL Server ( 1 )

  1. 2017-01-03 16:01

    Just double the single quotes around @to_remove and @to_add

    DECLARE @sql NVARCHAR(4000)
    
    SET @sql = 'UPDATE ' + Quotename(@tableName) + ' SET ' + Quotename(@colName)
               + ' = REPLACE(' + Quotename(@colName) + ',''' + @to_remove + ''','''
               + @to_add + ''');'
    
    PRINT @sql
    
    EXEC Sp_executesql
      @sql 
    

    Update : To do the replace for more than one column

    DECLARE @sql       NVARCHAR(4000),
            @col_list  VARCHAR(8000)= ''
    
    SET @col_list = (SELECT ',' + Quotename(COLUMN_NAME) + ' = REPLACE('
                            + Quotename(COLUMN_NAME) + ',''' + @to_remove
                            + ''',''' + @to_add + ''')'
                     FROM   INFORMATION_SCHEMA.COLUMNS
                     WHERE  table_name = 'TABLE_1'
                            AND COLUMN_NAME LIKE '%sound%'
                     FOR xml path(''))
    SET @col_list = Stuff(@col_list, 1, 1, '')
    
    SELECT @col_list
    
    SET @sql = 'UPDATE ' + Quotename(@tableName) + ' SET '
               + @col_list
    
    PRINT @sql
    
    EXEC Sp_executesql
      @sql 
    

Leave a reply to - Modify columns using stored procedure in SQL Server

◀ Go back