Inside stored procedure, set column names of returned query according to variable I have a query which I'd like to reuse, instead of having many different copies of it just for the sake of changing the column name.

The report (for example) uses the same query, but there are either dogs or cats in
the table, and if there are cats then it returns 'Nr of Cats', and if there are dogs then 'Nr of Dogs' as heading for the returned table.

declare @Animals table (
  AnimalType varchar(20), Name varchar(20)
  )

insert into @Animals(Type, Name) values('Cat', 'Garfield')

declare @AnimalType varchar(20)

set @AnimalType = (Select Top 1  AnimalType from @Animals )

select 
    Case when @AnimalType = 'Cat' 
           then 'Nr of Cats' 
           else 'Nr of Dogs' = count(*)  from @Animals
You'll need to use dynamic SQL for this, if you truly want SQL Server to pass back results with variable column names.

By way of an example, consider the following:

Use tempdb to avoid any nasty surprises to existing objects:

USE tempdb;


Create a table to hold the sample data.  Since we are using dynamic SQL, we cannot use a table variable for this.

IF OBJECT_ID('dbo.Animals') IS NOT NULL
DROP TABLE dbo.Animals;

CREATE TABLE dbo.Animals 
(
    AnimalType varchar(20) NOT NULL
    , AnimalName varchar(20) NOT NULL
);

INSERT INTO dbo.Animals (AnimalType, AnimalName)
VALUES ('Cat', 'Garfield')
    , ('Dog', 'Lucy');

GO


Create a stored procedure using dynamic T-SQL:

IF OBJECT_ID('dbo.DynamicResults') IS NOT NULL
DROP PROCEDURE dbo.DynamicResults;
GO
CREATE PROCEDURE dbo.DynamicResults
(
    @DogsOrCats varchar(3)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @msg nvarchar(1000);
    IF (@DogsOrCats = 'Dog' OR @DogsOrCats = 'Cat')
    BEGIN
        DECLARE @cmd nvarchar(max);
        DECLARE @vars nvarchar(max);
        SET @vars = '@Type varchar(20)';
        SET @cmd = 'SELECT a.AnimalType
            , [NumberOf' + @DogsOrCats + 's] = COUNT(1)
        FROM dbo.Animals a
        WHERE a.AnimalType = @Type
        GROUP BY a.AnimalType;';
        EXEC sys.sp_executesql @cmd, @vars, @Type = @DogsOrCats;
    END
    ELSE
    BEGIN
        SET @msg = '@DogsOrCats must be either ''Dog'' or ''Cat''';
        RAISERROR (@msg, 14, 1) WITH NOWAIT;
    END
END
GO


Running the stored proc returns varying result sets, based on the value passed in as @DogsOrCats

EXEC dbo.DynamicResults 'Cat';


Returns:


+------------+--------------+
| AnimalType | NumberOfCats |
+------------+--------------+
| Cat        |            1 |
+------------+--------------+


EXEC dbo.DynamicResults 'Dog';


Returns:


+------------+--------------+
| AnimalType | NumberOfDogs |
+------------+--------------+
| Dog        |            1 |
+------------+--------------+