Stored Procedure to remove characters from a column in a table, does not work

Question

I am trying to write a stored procedure to which I can pass a column name and an unwanted character, and the stored procedure will remove all instances of that unwanted character in the column.

The following SQL is the basis for my stored procedure and works when I execute it from the SQL Server Management Studio:

UPDATE MyTable
SET MyField = REPLACE(MyField, 'T', '');

But if I write a stored procedure, thus:

CREATE PROCEDURE uspRemoveCharFromColumn 
    @Fieldname nvarchar(50), 
    @UnwantedChar char(1)
AS
BEGIN
    UPDATE MyTable
    SET @Fieldname = REPLACE(@Fieldname, @UnwantedChar, '');
END

And execute it, thus:

EXEC uspRemoveCharFromColumn 'ADDR', 'T';

SELECT ADDR 
FROM MyTable 
WHERE ADDR LIKE '%T%';

I find that none of the "T"s have been removed from any of the field contents in the column.

I have spent the last 45 minutes Google-ing for a reason my stored procedure won't work and have come up empty. Can anyone help?

You are trying to use dynamic SQL without actually making it dynamic — Lamak, Mar 19 '15 at 18:07
To do this you'd either need to use dynamic SQL or use a massive CASE expression (depending on how many columns you have in the table) this is similar http://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure — legohead, Mar 19 '15 at 18:09
@Lamak - Could you expand on your comment? I'm not sure what you mean. — johncroc, Mar 19 '15 at 18:10

score 2 · Answer 1 · answered Mar 19 '15 at 22:32

Using the 'EXECUTE' command opens you up to SQL Injection. Use the sp_execute command to protect yourself from SQL Injection.

ALTER PROCEDURE uspRemoveCharFromColumn 
    @Fieldname nvarchar(50), 
    @UnwantedChar char(1)
AS
BEGIN
    DECLARE @qry NVARCHAR(MAX);
    DECLARE @paramDefinition NVARCHAR(500) = '@Fieldname VARCHAR(50), @UnwantedChar VARCHAR(5)';
    SET @qry ='UPDATE MyTable SET @Fieldname = REPLACE(@Fieldname,@UnwantedChar,'''')';

    EXEC sp_executesql @qry,@paramDefinition,@Fieldname,@UnwantedChar;
END

That's a good point Aaron. I would up-vote you but I only have "12" reputation and need "15" :-( — johncroc, Mar 25 '15 at 17:11

A_Sk · Accepted Answer · 2015-03-19T18:17:04.263

0

Use Dynamic SQL:

You are trying to pass Column_Name As parameter so you need to use Dynamic_SQl

CREATE PROCEDURE uspRemoveCharFromColumn 
    @Fieldname nvarchar(50), 
    @UnwantedChar char(1)
AS
BEGIN
    declare @qry varchar(max)
Set @qry ='UPDATE MyTable
SET '+ @Fieldname +'= REPLACE('+@Fieldname+','''+@UnwantedChar+''', '''')'

EXECUTE(@qry)

END

edited Mar 19 '15 at 18:17

answered Mar 19 '15 at 18:10

A_Sk

4,532
3
27
51

There will be a problem with the lack of single quotes around `@UnwantedChar` and the empty space too – Lamak Mar 19 '15 at 18:11
Excellent! I see now: Pass the arguments in, create a query-string from them, and execute that query-string. I learn something new every time I come here. Thank you! – johncroc Mar 19 '15 at 18:35
Not a great example, as the way that the params are being concatenated will lead to SQL Injection vulnerabilities. This is not a safe way to do it. – Steve Stedman Mar 19 '15 at 22:33

score 0 · Answer 3 · answered Mar 19 '15 at 18:11

0

putting a column name in a variable in the update query will not update the column data in table. You need to format the query by appending the column name. Then execute the query using sp_executesql

answered Mar 19 '15 at 18:11

wonderbell

1,126
9
19

Stored Procedure to remove characters from a column in a table, does not work

3 Answers3