CODEWITHSUNDEEP
sql-serverstored-procedures
Batsheva
Batsheva

Reputation: 669

How to return a user defined table type from a stored procedure

I created a user defined table type in SQL Server:

Create Type dbo.Car AS Table 
(
 CarNumber varchar(20) not null,
 Model varchar(20) null
)

There are many other fields there, this is only an example.

Now I have a stored procedure that I need it to return a list of Cars from this type. Here is the problem: I can't find a way to return user defined table type from a stored procedure.

If I declare it as a parameter in the procedure it must be readonly, so I can't assign a value to it. And if I declare a var from this type and try to return it from the procedure it doesn't allow.

Here is what I tried:

Create procedure getCarDetails
   @carNumber varchar(20)
AS
BEGIN
SET NOCOUNT ON;

   Declare @CarDetails AS dbo.Car

   insert into @CarDetails (CarNumber,Model)
   select CarNumber, Model
   from cars
   where cars.CarNumber = @carNumber

   -- here I need to return the @CarDetails
   -- if I try 
   --  return @CarDetails
   -- It says 'must declare the scalar variable "@CarDetails"'

What is the right way of doing it?

Upvotes: 3

Views: 4239

Answers (1)

sepupic
sepupic

Reputation: 8687

You just CANNOT return table-valued variable from stored procedure.

As you said, you cannot use table-valued parameter if it's not declared as readonly.

In your simple case you can use an inline table-valued function instead:

create function  getCarDetails( @carNumber varchar(20))
returns table
as
  return 
       select CarNumber, Model
       from dbo.cars
       where CarNumber = @carNumber;

Upvotes: 1

Related Questions