1

I am creating two tables that have some rows and columns. And I created a stored procedure which dynamically takes table name and produce the result.

But when I pas the table name dynamically, then it not work.

CREATE PROC spGetAll (@tableName varchar(50))
AS
BEGIN
    SELECT
        *
    FROM @tableName
END

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sk Asraf
  • 163
  • 2
  • 14
  • What do you mean by `then it not take`? – Felix Pamittan May 27 '16 at 07:21
  • 1
    You can not use variables for object identifiers. You have to build the query dynamically and run it via `sp_executesql`. Do some research, there are shitload of solutions on SO to achieve this. – Pred May 27 '16 at 07:25
  • 1
    Generally, if you're wanting to treat multiple tables identically, it's a sign of a broken data model - if they contain the same "type" of data, that data ought to be stored in a *single* table, with possibly some additional columns to store the *data* that's currently being ignored (or only modelled as metadata) that determines which table the data should be stored in. Difficult to tell if that's the situation here with made up table names, but e.g. you wouldn't model `MaleEmployees` and `FemaleEmployees` as separate tables. – Damien_The_Unbeliever May 27 '16 at 07:29
  • Don't. This query is naturally an ad-hoc, so run it as ad-hoc. Especially with that `select *`. – Ivan Starostin May 27 '16 at 07:39

2 Answers2

3

Just use dynamic SQL. Example:

CREATE PROC spGetAll (
    @tableName SYSNAME)
AS
BEGIN
    DECLARE @sql nvarchar(max) 

    IF EXISTS (SELECT name FROM sys.objects WHERE name = @tableName)
    SET @sql = 'SELECT * FROM test.'+ QUOTENAME(@tableName)
    EXEC(@sql) -- Or EXEC sp_executesql @sql
END
gofr1
  • 15,741
  • 11
  • 42
  • 52
1

here is one way . . 

IF @tableName = 't111'
    SELECT * FROM t111
ELSE
    SELECT * FROM t222
Squirrel
  • 23,507
  • 4
  • 34
  • 32