Create a stored procedure which includes return records

I am trying to create a stored procedure which has 3 parameters. Another software will call my procedure with parameters in order to query a bunch of records.

The stored procedure includes only a select statement with a where clause filtering according to parameters.

How can I return these records to the “caller”?
I have tried to insert these records in a temp table as follows:

create procedure xxx
drop table table_temp
select * into table_temp from table where parameters

The thing is, if two users are calling the stored procedure at the same time, they will get mixed up results.

Any suggestion to this dilemma is highly appreciated.

Answer

You could either use a temporary table as stated above, or use the table type (I use the AdventureWorks2014 demo database for these examples):

CREATE PROCEDURE TableVariableDemo(
   @Color nvarchar(15),
   @Size nvarchar(5))
AS
BEGIN
   DECLARE @Results TABLE(ProductID int, [Name] nvarchar(255), ProductNumber nvarchar(25), Color nvarchar(15), Size nvarchar(5))

   INSERT INTO @Results (ProductID, [Name], ProductNumber, Color, Size)
   SELECT
      ProductID,
      [Name],
      ProductNumber,
      Color,
      Size
   FROM
      Production.Product
   WHERE
      Color = @Color AND
      Size = @Size

   SELECT
      ProductID,
      [Name],
      ProductNumber,
      Color,
      Size
   FROM
      @Results
END

The solution is good when you expect not too much rows being returned. When you expect a lot of rows returned by the query, using a temporary table could be better. The same with using a temporary table:

 CREATE PROCEDURE TempTableDemo(
    @Color nvarchar(15),
    @Size nvarchar(5))
 AS
 BEGIN
   CREATE TABLE #Results(ProductID int, [Name] nvarchar(255), ProductNumber nvarchar(25), Color nvarchar(15), Size nvarchar(5))

   INSERT INTO #Results (ProductID, [Name], ProductNumber, Color, Size)
   SELECT
      ProductID,
      [Name],
      ProductNumber,
      Color,
      Size
   FROM
      Production.Product
   WHERE
      Color = @Color AND
      Size = @Size

   SELECT
      ProductID,
      [Name],
      ProductNumber,
      Color,
      Size
   FROM
      #Results

   DROP TABLE #Results
END

But I wonder why you don’t use something like this:

 CREATE PROCEDURE SelectDemo(
    @Color nvarchar(15),
    @Size nvarchar(5))
 AS
 BEGIN
   SELECT
      ProductID,
      [Name],
      ProductNumber,
      Color,
      Size
   FROM
      Production.Product
   WHERE
      Color = @Color AND
      Size = @Size
END

Best wishes
Michael