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

Viewed 2805

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?

1 Answers
Related