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, May 03 '17 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, May 03 '17 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, May 04 '17 at 13:27
Possible duplicate of [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) — Ian Ringrose, May 24 '17 at 10:18

Wheels73 · Answer 1 · 2017-05-04T13:14:10.493

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 04 '17 at 13:14

answered May 03 '17 at 13:04

Wheels73

2,850
1
11
20

1

If you wrap @Tablename in QUOTENAME it would help considerably to prevent sql injection. – Sean Lange May 03 '17 at 13:12
I have seen all these examples..but am not able to use them in my stored procedure – user3660473 May 03 '17 at 13:16
@SeanLange - Oh ok... That's sounds good. I've not actually used quote name before! thanks! – Wheels73 May 03 '17 at 13:18
@user3660473 - Why can't you use it? – Wheels73 May 03 '17 at 13:18
For the line Update **testdata**, I am passing testdata as '+@TableName+'. But it gives invalid object name error – user3660473 May 04 '17 at 08:11
@Wheels73 And the same with SELECT @RS=ReserveStatus from **testdata** line – user3660473 May 04 '17 at 08:12
Comment out the sp_executesql and change your proc to select the @SQL. Copy and paste this SQL and execute it in a new query window to see what the problem is. Perhaps the schema isn't dbo for you? or you are misspelling the table name when you pass it in? – Wheels73 May 04 '17 at 08:14
The spelling and dbo are fine. am not using sp_executesql anywhere. Please check my SP once – user3660473 May 04 '17 at 08:26
You have to create a sql string like "@SQL" in my answer. Once you have built up the string including "@Tablename", you execute it via the sp_executesql command. – Wheels73 May 04 '17 at 08:33
I need to update a table and not select. How can I make it work for this scenario – user3660473 May 04 '17 at 08:43
how about this line. Am getting error here too SELECT @RS=ReserveStatus from **testdata** ///Tablename=testdata WHERE TestDataId=rtrim(Ltrim(SUBSTRING(@TestDataIdstring, @index, @value))); – user3660473 May 04 '17 at 12:09
@Wheels73 getting this error: Conversion failed when converting the varchar value 'ReserveStatus from TblControls WHERE TestDataId=rtrim(Ltrim(SUBSTRING('+@TestDataIdstring+',' to data type int. – user3660473 May 04 '17 at 12:27
What type is ReserveStatus in the table "testdata". You have declared @RS as a varchar above? – Wheels73 May 04 '17 at 12:38
it is char. when I hardcode the table name I am getting expected result. but when I pass tablename dynamically I am getting error. – user3660473 May 04 '17 at 12:41
Well change @rs to be a char. There may be an implicit conversion happening that doesn't when you build up dynamic sql. Also, I can't see to find "TblControls" in your post, which is mentioned in the error. – Wheels73 May 04 '17 at 12:47
same error after changing @rs to char type. TblControl is the actual table I am passing. I had tried hardcoding it. But it throws same error when I use @ tablename – user3660473 May 04 '17 at 12:59
Well i've also noticed that when you set "@Rs" you need to use the dynamic SQL. I've modified my answer to construct the sql you need to get the value from the table passed in into "@RS". – Wheels73 May 04 '17 at 13:02
same error ..for update statement too am getting same error – user3660473 May 04 '17 at 13:18
@Wheels73 I have posted code behind code and updated SP – user3660473 May 04 '17 at 13:29

Adding tablename dynamically in Stored Procedure

1 Answers1