Stored procedure with table name as parameter amongst others

Question

I have tried to update a stored procedure which worked fine without the need to use sp_executesql. I now want to have the table name as a parameter as I have a number of tables with the same structure and don't want to create new stored procedures for each of them.

The problem I have is that this version seems to require all the parameters, while the previous one accepted any number of parameters. For instance, if I remove all the WHERE parameters and just have the @TableName parameter it works fine. I;ve tried looking for an example, but I cannot find anything like this. All the examples of parsing the table name have only that parameter.

CREATE PROCEDURE cafgTenantNamesTEST2
    @TableName sysname,
    @Square nvarchar(100) = null,
    @Location nvarchar(100) = null,
    @Name nvarchar(100) = null,
    @NormalizedName nvarchar(100) = null,
    @SharedLand int = 0,
    @FieldNumber int = 0,
    @Description nvarchar(255) = null,
    @Dwelling nvarchar(100) = null
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX)
    SET @sql = 'SELECT * FROM [' + @TableName + ']' + 
    'WHERE ([Square] LIKE ''' + @Square + ''' OR ''' + @Square + ''' IS NULL)' + 
    'AND ([Location] = ''' + @Location + ''' OR ''' + @Location + ''' IS NULL)' +
    ...
    ...
--PRINT @sql
EXEC sp_executesql @sql
END

Suggestions please.

Check the value of @sql and make sure it can execute. Can you build a sql string by hardcoding the table name and get the Where clause to work? — JeffO, Jun 15 '13 at 01:09
Try adding a space before the `WHERE` and the `AND` that you are concatenating without whitespace. — HABO, Jun 15 '13 at 02:08
Does this answer your question? [How should I pass a table name into a stored proc?](https://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc) — Michael Freidgeim, Oct 27 '21 at 17:37

StrayCatDBA · Accepted Answer · 2013-06-18T15:23:47.610

Suggestion 1: Use QUOTENAME() to handle proper escaping of the table name.

Suggestion 2: You are inserting the value of the parameter into @sql. Don't do that. Instead you should use pameterized the sql.

Suggestion 3: Eliminate the OR logic by conditionally building the query's WHERE clause.

 CREATE PROCEDURE cafgTenantNamesTEST2
    @TableName sysname,
    @Square nvarchar(100) = null,
    @Location nvarchar(100) = null,
    @Name nvarchar(100) = null,
    @NormalizedName nvarchar(100) = null,
    @SharedLand int = 0,
    @FieldNumber int = 0,
    @Description nvarchar(255) = null,
    @Dwelling nvarchar(100) = null
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX)
    SET @sql = N'SELECT * FROM ' + QUOTENAME(@TableName ) + 
    ' WHERE 1=1 '
    IF  @Square IS NOT NULL
      SET @sql = @sql + ' AND ([Square] LIKE   @Square )'  -- still patameterized
   IF @Location IS NOT NULL
      SET @sql = @sql + N'  AND ([Location] = @Loc )'
    ...
    ...
--PRINT @sql
EXEC sp_executesql @sql, N'@Square nvarchar(100), @Loc nvarchar(100)...', @square=@square, @loc=@location  -- the param names can be the same or different, sp_executesql has it's own scope.
END

Sp_executesql can execute parameterized sql in addition to plain sql. It is the underlying system stored procedure that is used by client libraries to execute parameterized code. For example, System.Data.SqlClient.SqlCommand will call sp_executesql if you have added any parameters. It is atypical in that it accepts a variable number of parameters. The msdn docs on sp_executesql provide some good information, but isn't clear. Capturing activity in SQL profiler is the easiest way to see sp_executesql in action.

Brilliant. Thanks for this @StrayCatDBA. I've used straightforward sql for years, but I have only just started using sp's and I am still struggling with them. I will work on it tomorrow now as I am tied up today. Can I assume with this method then that I can include none or any number of parameters when calling this proc? — Peter C, Jun 15 '13 at 14:28
Sorry, could you also please explain the EXEC line? I.e. why the declarations. — Peter C, Jun 15 '13 at 14:36
That worked, thanks. Except, I still can't see what the exec line does as if I include the parameters in it I get an error: _Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'_. — Peter C, Jun 16 '13 at 18:07
make sure that you are using Nvarchar's for the parameters instead of varchars. i.e. N'...' instead of '...' — StrayCatDBA, Jun 18 '13 at 03:37

Stored procedure with table name as parameter amongst others

1 Answers1