Adding tablename dynamically in Stored Procedure

Question

I have a stored procedure as below in modify mode. I need to pass tablename dynamically or even string concatenation is also fine. But as am not very familiar with stored procedures am not finding a way to do this. Any guidance will be helpful. testdata is the table name. I need to pass @TableName dynamically.

 USE [test1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TestDataTest]

@TicketId VARCHAR(12),
@TesterId int,
    @ValidatorId varchar(10),
@count int, 
   @TableName varchar(20),
@ReturnVal int output



AS
BEGIN
SET NOCOUNT ON;
DECLARE @ReserveStatus char(1),@ret int, @index int,@rs CHAR,@secindex INT,@value INT,@SQL nvarchar(500),@SQL1 nvarchar(500)
    BEGIN TRAN
    INSERT INTO TblStatus (TicketId,TesterId,ValidatorId)
    VALUES
   (@TicketId,@TesterId,@Validator)

    IF (@@ERROR<>0 )
      BEGIN
      ROLLBACK TRAN 
      SET @ReturnVal=2


      Return @ReturnVal
      END

    WHILE @count >0

       BEGIN

       SELECT  @index=CHARINDEX(' ', @TestDataIdstring,0)
       SELECT  @secindex=CHARINDEX(' ', @TestDataIdstring,(CHARINDEX(' ', @TestDataIdstring,0))+1)
       SELECT  @value=@secindex-@index
       SELECT @value
       select @SQL1 = N'Select ReserveStatus from ' + QUOTENAME(@TableName) + ' where TestDataId= rtrim(Ltrim(SUBSTRING(''' + @TestDataIdstring + ''',' + @index +',' + @value + ')))'
       execute sp_executesql @SQL1 , N'@RS int OUTPUT', @RS = @RS output;


       IF (@rs='N')
         BEGIN

     Set @SQL =  N'Update ' + QUOTENAME(@Tablename) + ' set  ReserveStatus=''Y'',TicketId=' + @TicketId + ' where TestDataId= rtrim(Ltrim(SUBSTRING(''' + @TestDataIdstring + ''',' + @index +',' + @value + ')))'
     Execute sp_executesql @SQL
         IF (@@ERROR<>0 )
            BEGIN
            ROLLBACK TRAN 
            SET @ReturnVal=2
            Return @ReturnVal
            END




COMMIT TRAN  

   SET @ReturnVal=3
   return @SQL
  return @returnval

  END

Whenever you have to pass in the table name to a procedure it should be a red flag that perhaps there is a better data design you could be using. — Sean Lange
– Sean Lange, Commented May 3, 2017 at 13:13
Your code has a number of issues. You are using an OUTPUT parameter but you also return it. No need to do both. And I really don't think you need a loop here but I can't make heads or tails of what that loop is trying to accomplish. — Sean Lange
– Sean Lange, Commented May 3, 2017 at 13:21
Hi...Hmmm...you know.. given all the issues you are having.. I think Sean's point is very valid. Cloning your stored procedure so you have 1 for each table is not such a bad idea. I'm presuming you only have a handful of different tables you can pass in. If this is the case, i would change the proc you execute and make changes in code. The amount of time you are spending on this, you could have made the changes by now. — Wheels73
– Wheels73, Commented May 4, 2017 at 13:27

Wheels73 · Accepted Answer · 2017-05-04 13:14:10Z

2

You can use the sp_ExecuteSQL command. Below is a simple example that takes a table name as a parameter.

Create Procedure dbo.DynamicSQL
(
    @Tablename nvarchar(50)
)
As

Declare @SQL nvarchar(500)

Set @SQL =   N'Select * from dbo.' + QUOTENAME(@Tablename)

EXECUTE sp_executesql @SQL

go

Below is my attempt at constructing your update sql for you and have then executed it via sp_executesql

Declare @SQL nvarchar(500)

Set @SQL =  N'Update ' + QUOTENAME(@Tablename) + ' set  ReserveStatus=''Y'',TicketId=' + @TicketId + ' where TestDataId= rtrim(Ltrim(SUBSTRING(''' + @TestDataIdstring + ''',' + @index +',' + @value + ')))'
Execute sp_executesql @SQL

I've assumed that the variables passed in are all varchars!

To get the value into @RS which is also dynamic you need to add this.

   select @SQL = N'Select ReserveStatus from ' + QUOTENAME(@TableName) + ' where TestDataId= rtrim(Ltrim(SUBSTRING(''' + @TestDataIdstring + ''',' + @index +',' + @value + ')))'
   execute sp_executesql @SQL , N'@RS char(1) OUTPUT', @RS = @RS output;

edited May 4, 2017 at 13:14

answered May 3, 2017 at 13:04

Wheels73

2,9001 gold badge14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

Sean Lange Over a year ago

If you wrap @Tablename in QUOTENAME it would help considerably to prevent sql injection.

user3660473 Over a year ago

I have seen all these examples..but am not able to use them in my stored procedure

Wheels73 Over a year ago

@SeanLange - Oh ok... That's sounds good. I've not actually used quote name before! thanks!

Wheels73 Over a year ago

@user3660473 - Why can't you use it?

user3660473 Over a year ago

For the line Update testdata, I am passing testdata as '+@TableName+'. But it gives invalid object name error

|

Collectives™ on Stack Overflow

Adding tablename dynamically in Stored Procedure

1 Answer 1

19 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related