Stored procedure not returning results from table variable?
<p>I have a stored procedure that I'm working on and I'm trying to get it to return the results that are stored into a table variable that I created within the procedure. For some reason, its not returning anything when I execute the procedure. However, if I take the piece of code used to populate the table variable and take it out of the procedure it works...</p>

<p>The code is as follows:</p>

<p><strong>Test Code</strong></p>

<pre><code>DECLARE @RC int
DECLARE @constraint_names nvarchar(max)
DECLARE @column_names nvarchar(max)
DECLARE @table_name nvarchar(max)
DECLARE @table_schema nvarchar(max)
DECLARE @database_name nvarchar(max)
DECLARE @debug int
DECLARE @ExistFlag int;

-- TODO: Set parameter values here.
SET @column_names = 'M2016_Object_ID int, Name varchar(50), a int, b'
SET @table_name = 'tblM2016_Objects';
SET @debug = 0;
SET @ExistFlag = 1;

DECLARE @existing_cols TABLE (Value nvarchar(max));
INSERT INTO @existing_cols EXEC spM2016_CheckColumnExistence_tblM2016 
@column_names, @table_name,  @database_name, @table_schema, 1, 0;
select * from @existing_cols
</code></pre>

<p><strong>Results</strong></p>

<pre><code>Value
M2016_Object_ID int
Name varchar(50)
</code></pre>

<p>This is the expected result since I'm testing to see if M2016_Object_ID and Name column exist in the tblM2016_Objects table.</p>

<p>However, when I execute this inside the stored procedure I'm working on, it returns nothing.</p>

<p><strong>Stored Procedure</strong></p>

<pre><code>ALTER PROCEDURE spM2016_ChangePKConstraints_tblM2016 
-- Add the parameters for the stored procedure here
    @constraint_names   nvarchar(max) = 
N'PK_tblM2016_Fields_M2016_Field_ID',
    @column_names       nvarchar(max) = N'M2016_Field_ID',
    @table_name         nvarchar(max) = N'tblM2016_Fields', 
    @table_schema       nvarchar(max) = N'dbo',
    @database_name      nvarchar(max) = N'MET',
    @debug              int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @tbl_name       nvarchar(max) = N'tblM2016_Fields',
        @col_names      nvarchar(max) = N'M2016_Field_ID',
        @tbl_schema     nvarchar(max) = N'dbo',
        @db_name        nvarchar(max) = N'MET',
        @tbl_full_name  nvarchar(max),
        @tbl_short_name nvarchar(max),
        @sql            nvarchar(max),
        @params         nvarchar(max)

-- Insert statements for procedure here

SET @col_names = ISNULL(NULLIF(@column_names, ''), @col_names);
SET @tbl_name = quotename(ISNULL(NULLIF(@table_name, ''), @tbl_name));
SET @tbl_schema = quotename(ISNULL(NULLIF(@table_schema, ''), @tbl_schema));
SET @db_name = quotename(ISNULL(NULLIF(@database_name, ''), @db_name));

SET @tbl_full_name =  @db_name + '.' + @tbl_schema + '.' + @tbl_name;
SET @tbl_short_name = @tbl_schema + '.' + @tbl_name;

DECLARE @existing_cols TABLE (Value nvarchar(max));
DECLARE @nonexisting_cols TABLE (Value nvarchar(max));

--INSERT INTO @split_columns
--  SELECT *
--  FROM 
--  fnM2016_Split_String_Inline(@col_names, ',');

--IF (@debug = 1)
--  SELECT * FROM @split_columns;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'PK' AND parent_object_id = OBJECT_ID(@tbl_full_name))
BEGIN
    -- No PK constraint, check col_names to see which exist and don't exist
    INSERT INTO @existing_cols EXEC spM2016_CheckColumnExistence_tblM2016 @col_names, @tbl_name,  @db_name, @tbl_schema, 1, 0;

    INSERT INTO @nonexisting_cols EXEC spM2016_CheckColumnExistence_tblM2016 @col_names, @tbl_name,  @db_name, @tbl_schema, 0, 0;

    SELECT * FROM @existing_cols;

    SELECT * FROM @nonexisting_cols;

END
--ELSE
--BEGIN

--END

END
</code></pre>

<p><strong>Results</strong>
These are the results after executing the procedure with the same parameter values in the test above:</p>

<pre><code>EXECUTE @RC = [dbo].[spM2016_ChangePKConstraints_tblM2016] 
 @constraint_names
,@column_names
,@table_name
,@table_schema
,@database_name
,@debug;
</code></pre>

<p>Result</p>

<pre><code>Value
</code></pre>

<p>The table purposely does not have a primary key. Proof:
<a href="https://i.stack.imgur.com/sYqyF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sYqyF.png" alt="enter image description here"></a> </p>