Must Declare the table variable "@table"?

Question

So this might just be a matter of me not knowing enough about Procedures, but I am trying to make a Procedure which update a Column in a Table to remove trailing spaces as it's a regular occurrence we are getting lately at work.

This is because we are in a transition phase from an old system to a new one, so we also made a new database to accompany this. The old Database used ~~nvarchar~~ nchar which means that even if you don't use all the slots for characters, it will fill the rest of the slots with spaces. In the new database though we use varchar so we want to get rid of white spaces!

So this is pretty handy:

UPDATE table SET column = LTRIM( RTRIM( column ) );

So I tried to make a Procedure out of it as we have to use it once in a while:

USE omitted
GO
CREATE PROCEDURE TrimTrailingSpaces
  @table  VARCHAR(50),
  @column VARCHAR(50)
AS
BEGIN
UPDATE @table
SET
    @column = LTRIM( RTRIM( @column ) );
END
GO

But whenever I try and execute this I get the error:

Msg 1087, Level 16, State 1, Procedure TrimTrailingSpaces, Line 6 [Batch Start Line 2]: Must declare the table variable "@table".

Is what I am doing just not possible?

@MitchWheat Well Please explain then why the database consistently have white spaces in it, when we don't add those white spaces. — OmniOwl, Oct 26 '16 at 08:25
@rbr94 As is seen in the query I am simply trying to create the procedure and it won't let me. — OmniOwl, Oct 26 '16 at 08:26
`The old Database used nvarchar which means that even if you don't use all the slots for characters, it will fill the rest of the slots with spaces` - Thats not what the difference between `nvarchar` and `varchar` is! You're thinking of the difference between `varchar` and `char` or `nchar` and `nvarchar` — Jamiec, Oct 26 '16 at 08:29
Hi. The var in nVarChar and VarChar stands for [variable](https://msdn.microsoft.com/en-us/library/ms176089.aspx?f=255&MSPPError=-2147217396). This change isn't, directly, the source of the unexpected whitespace. To pass table names as parameters you will need to use [dynamic sql](https://msdn.microsoft.com/en-us/library/ms709342(v=vs.85).aspx). — David Rushton, Oct 26 '16 at 08:29
Dont change from nchar to varchar, you should have made it nvarchar — Jamiec, Oct 26 '16 at 08:31
@Jamiec raises a good point. Switching from nChar to VarChar could result in a loss of data. Consider nVarChar instead. The n versions of these data types consume more storage but support a wider range of characters. See this [question](http://stackoverflow.com/questions/612430/when-must-we-use-nvarchar-nchar-instead-of-varchar-char-in-sql-server) for more on the differences. — David Rushton, Oct 26 '16 at 08:38

Akshey Bhat · Accepted Answer · 2016-10-26T08:38:03.327

8

USE omitted
GO
CREATE PROCEDURE TrimTrailingSpaces
  @table_name  VARCHAR(50),
  @column_name VARCHAR(50)
AS
BEGIN
declare @sql varchar(200);
set @sql='UPDATE ' + QUOTENAME(@table_name) 
     +' SET '+  @column_name+ ' = LTRIM( RTRIM( ' + QUOTENAME(@column_name) +' ) )';
END
GO

You need to use dynamic sql for this. Table name can't be given as a variable in UPDATE query. Also change variable names to other than keywords in SQL.

edited Oct 26 '16 at 08:38

answered Oct 26 '16 at 08:28

Akshey Bhat

8,227
1
20
20

1

When constructing dynamic sql you should be aware of [sql injection](https://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx). [QuoteName](https://msdn.microsoft.com/en-us/library/ms176114.aspx) can help reduce the chances of an attack. Exmaple: `SET @sql = 'UPDATE ' + QUOTENAME(@table_name) + ' SET...`. – David Rushton Oct 26 '16 at 08:33
@destination-data thanks for your suggestion. Modified my answer to include it. – Akshey Bhat Oct 26 '16 at 08:38

score 1 · Answer 2 · answered Oct 26 '16 at 08:31

1

USE omitted
GO
CREATE PROCEDURE TrimTrailingSpaces
  @table  VARCHAR(50),
  @column VARCHAR(50)
AS
BEGIN

declare @sql varchar(max)
set @sql='UPDATE '+@table+'
SET
    '+@column+' = LTRIM( RTRIM( '+@column+' ) );
END'

execute(@sql)
end
go

answered Oct 26 '16 at 08:31

karan yadav

59
1
6

Must Declare the table variable "@table"?

2 Answers2