CODEWITHSUNDEEP
sql-serverstored-proceduresdynamic-sql
Peter C
Peter C

Reputation: 553

Stored procedure with table name as parameter amongst others

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.

Upvotes: 7

Views: 18268

Answers (1)

StrayCatDBA
StrayCatDBA

Reputation: 2880

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.

Upvotes: 10

Related Questions