How can I SELECT FROM a @TableVariable

Question

I have the following...

DECLARE @NDCs TABLE
(
[NDC] VARCHAR(11),
[Valid] BIT
)
;

INSERT INTO @NDCs
(
[NDC],
[Valid]
)
SELECT *
FROM [dbo].[LookupNDC] (@New Direction Church _In, @NDC9_In) AS [LN]
;

How can I see what is created in the Table Variable @NDCs?

I tried...

DECLARE @TableName VARCHAR(25)
;
SET @TableName = '@NDCs'
;

EXECUTE('SELECT * FROM ' + @TableName + '')
;

But that didn't seem to work.

Any help and/or guidance would be GREATLY appreciated.

And Thanks in advance for your review and hopeful for a reply.

Answer 1

Hi @Bobby P ,

The syntax in your INSERT INTO statement is incorrect. It fails when inserting data, so you can't see anything.The value of VARCHAR type must use ”, and the value of BIT type can only take the value 1, 0 or NULL.Please try:

    DECLARE @NDCs TABLE  
    (  
    [NDC] VARCHAR(11),  
    [Valid] BIT  
    )  
    ;  
      
    INSERT INTO @NDCs  
    VALUES('[NDC]',  
    1)  
      
    SELECT *  
    FROM @NDCs  
    ;  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As Viroel said, it is as simple as

SELECT * FROM @NDCs

I don't know why you dabble with dynamic SQL, but what you try cannot work out. A table variable - like any other variable - is only visible in the scope where you created it. A block of a dynamic SQL is a nameless stored procedure and a scope of its own. Therefore it cannot see the table variable.

Answer 3

Boy. I swear I had tried to SELECT FROM @TableVariable and it didn't work.

Thanks for your help!