1

i have created a stored procedure that contains a parameter. I want to use this parameter as part of the table name. there are 2 tables a user can choose by entering the final 4 values

  • [Summary_tableStore4302]
  • [Summary_tableStore4304]

i want the numericalportion as the parameter. This will be executed in Tableau, so user can enter either 4302 or 4304, the select statement will grab data from this table.

the trouble I am having (i think) is trying to concat the string portion of the table name to the parameter value and using it within the select statement

CREATE PROCEDURE testTableau4
    @BM1 int = 0

Set @tableName = concat('[Summary_tableStore',@BM1,']' );

select * from @tableName as t1

End
Go

what is the proper way to concat a string value and integer parameter value and use it as a table name within Select statement, specifically within a Stored Procedure. thank you!

  • 1
    You have to use dynamic sql for this. And you should at the very least use QUOTENAME to help prevent sql injection. Almost every time you see a table being passed as a parameter like this it indicates a design that is less than optimal. – Sean Lange Jul 31 '19 at 16:41
  • This is bad design, use an If statement to pick which table to use, instead of building dynamic sql if you only have 2 tables. – Brad Jul 31 '19 at 16:43
  • This really sounds like it should be a new column in a single table instead of 2 tables. This type of design is impossible to manage. – Sean Lange Jul 31 '19 at 16:45
  • Yes you can using `EXEC` https://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement – IronMan Jul 31 '19 at 16:46

0 Answers0