How to select * from variable(Table) in IF statement in Stored Procedure

Question

I'm trying to select from a table that is variable in the IF statement. Here is my question.

--ItemProcedure (stored procedure name)
--Item (table name)
Itemcode ItemName    qty
--------------------------
item123  Monitor     100
item456  Laptop      50
item789  Keyboard    10

Exec ItemProcedure 'Item'

Declare @Msg as varchar(50)

IF Exists (Select * from @TableName where Itemcode = 'item123')
BEGIN
    SET @Msg = 'Item exists'
END

ELSE

BEGIN
    SET @Msg = 'Item not exists'
END

Select @Msg

is there any method that can do this? please advise.

Is the `SELECT * FROM [table]` just an example? Because having that as a stored procedure adds nothing in terms of expressiveness or security; the client may as well submit that statement itself. This sort of thing requires dynamic SQL, which is never convenient to work with on the database side. — Jeroen Mostert, Jul 01 '21 at 10:07
I'm sorry, your question doesn't make sense. What is the actual code you are running and what is the error/problem that you have? To "select * from variabletable in stored procedure", you do exactly what you've written: `Select * from @TableName` are you trying to capture stored procedure output into a table variable? That is a common question. — Nick.Mc, Jul 02 '21 at 02:06
@Nick.McDermaid Hi Nick, I got 5 projects that using the same method, I do not want to create 5 procedures, I decided to use a variable instead of the actual table name, so this can be done in 1 procedure. and my intention is to assign the table name to ```@TableName``` then I can ```Select * from @TableName```. — user16357024, Jul 02 '21 at 02:29
Oh. You can't do that. You'll need to do use dynamic SQL https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable — Nick.Mc, Jul 02 '21 at 02:48
Does this answer your question? [A table name as a variable](https://stackoverflow.com/questions/2838490/a-table-name-as-a-variable) — Nick.Mc, Jul 02 '21 at 02:48
Keep in mind that SQL is not like normal programming. If you're finding you have lots of duplicate code, there may be some other design issue. As per @JeroenMostert comment, "having that as a stored procedure adds nothing in terms of expressiveness or security" — Nick.Mc, Jul 02 '21 at 02:49
@Nick.McDermaid Thanks, Nick. it helps to solve the table name as a variable and works fine. ```Declare @TableName, SET @TableName = 'TEST' EXEC('Select * FROM ' + @TableName')``` but I cannot put this into IF statement, for example: ```IF Exists (EXEC('Select * FROM ' + @TableName')```, it appear an error(Incorrect syntax near 'EXEC'. Expecting '(', or SELECT ). — user16357024, Jul 08 '21 at 09:07
Please edit your question and explain what you are trying to do with the `if`. You'll need to provide details if you want assistance — Nick.Mc, Jul 09 '21 at 00:38
@Nick.McDermaid Hi Nick, I already edited my question, please have a look, thanks. — user16357024, Jul 09 '21 at 01:44
How many different `@TableName` are there? Will they all have the same column or different columns? If there are less than, say, ten, you are probably better off just hard coding them all. You can do this with dynamic SQL, but dynamic SQL is to be avoided — Nick.Mc, Jul 09 '21 at 04:44
@Nick.McDermaid Yes, they have the same columns. why I want to do this because I want to separate the project don't want to mix up. — user16357024, Jul 09 '21 at 06:45

score 0 · Answer 1 · answered Jul 02 '21 at 20:21

If you have two tables, ItemsA and ItemsB, you can create a view that combines them:

CREATE VIEW vw_ItemsAll
AS
    SELECT ItemCode, ItemName, Qty, 'ItemsA' as TblName FROM ItemsA
    UNION ALL
    SELECT ItemCode, ItemName, Qty, 'ItemsB' as TblName FROM ItemsB
GO

SELECT ItemCode, ItemName, Qty
FROM vw_ItemsAll
WHERE TblName = @TableName

I've done this and it seems to work pretty well.

How to select * from variable(Table) in IF statement in Stored Procedure

1 Answers1