Creating Stored Procedure with TableName as parameter

Question

I am attempting to create a stored procedure where a table name is passed as a parameter, as well as updating contents of the table. Here is my code I have so far.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE TableSelect
    @TableName varchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    Declare @String varchar(100)
    SELECT @String = 'Update '
    SELECT @String = @String + @TableName
    SELECT @String = @String + 'Set Internet = 1'

    EXEC (@String)

END
GO

When I attempt to execute by this command:

EXEC TableSelect 'Waterford';

I get the error:

Msg 102, Lvevl 15, State 1, Line 1
Incorrect syntax near 'Internet'.

Any help would be great. FYI, the column Internet, is just a bit so 0/1 or False/True. Thanks

Answer 1

Something seems really, really, really bad in this design.

1. Do you really have a large number of tables that have a column called Internet? What does that column even mean?
2. Do you really want to call this stored procedure such that it updates every row in the specified table? Why? You don't have a one-row table for every location / city / room, do you? Why?

Anyway, there are a couple of problems in your code; allow me to suggest a re-write, if you can't consider a complete redesign.

ALTER PROCEDURE dbo.TableSelect -- always use schema prefix!
    @TableName NVARCHAR(512) -- this must be a Unicode data type!
AS
BEGIN
    SET NOCOUNT ON;

    -- should at least validate that the @TableName is valid
    -- which can at least somewhat help avoid SQL injection
    IF OBJECT_ID(@TableName) IS NULL
    BEGIN
      RETURN;
    END

    DECLARE @String NVARCHAR(4000);-- should always use Unicode here too
        -- and always use semi-colons

    SET @String = N'Update ' + @TableName + N' Set Internet = 1';
        -- needed a space here ---------------^
        -- which PRINT @String would have shown you.

    EXEC sys.sp_executesql @String;
        -- sp_executesql preferred over EXEC()
END
GO

Now, when you call it, specify the schema too, in both places, and specify the parameter name too, e.g.:

EXEC dbo.TableSelect @TableName = N'dbo.Waterford';

Relevant links:

• Bad Habits to Kick : Avoiding the Schema Prefix
• Protecting yourself from SQL Injection - Part 1 / Part 2
• Ladies and gentlemen, start your semi-colons!
• Bad Habits to Kick : Using EXEC() instead of sp_executesql