Reuse t-sql table variable or clone its structure

Question

I'm writing stored procedure to paginate results of a different stored procedure. I do it by executing the stored procedure and inserting results into the table variable:

DECLARE @allResults table
(
    [ID] [int] NOT NULL,
    [DESCRIPTION] [varchar](MAX) NULL,
    [COL1] [VARCHAR],
    [COL2] [VARCHAR],
    ...
);

INSERT @allResults Exec [dbo].[GetResults];

I need to filter the results and store them somewhere because I will use filtered results in at least two places: to count all the records and to actually paginate. Ideally I'd like to reuse the @allResults table as I won't need its content anymore after filtering. Something similar to:

@allresults = @allresults where [DESCRIPTION] like '%keyword%'

I'm not exactly sure how can I truncate table in the same moment as I filter it. That's why I created second table variable with the same structure:

DECLARE @filteredResults table
(
    [ID] [int] NOT NULL,
    [DESCRIPTION] [varchar](MAX) NULL,
    [COL1] [VARCHAR],
    [COL2] [VARCHAR],
    ...
);

It's not a bad solution, and it works. But I wonder could I reuse the definition of a table variable? Something that would look like:

DECLARE @filteredResults, @allResults table
(
    [ID] [int] NOT NULL,
    [DESCRIPTION] [varchar](MAX) NULL,
    [COL1] [VARCHAR],
    [COL2] [VARCHAR],
    ...
);

Is there a way to do it? Maybe there's a way to clone table variable? I guess simultaneous delete and filtering could be achieved using delete with output clause but I'm not exactly sure how should I write it.

I hate to repeat code. Maybe there's a simple solution you know of :)

Answer 1

You can create your own user table type:

CREATE TYPE dbo._t_test AS TABLE(
    [ID] [int] NOT NULL,
    [DESCRIPTION] [varchar](MAX) NULL,
    [COL1] [VARCHAR],
    [COL2] [VARCHAR]
)
GO

And then create table variables like this:

DECLARE @filteredResults dbo._t_test
    , @allResults dbo._t_test