CODEWITHSUNDEEP
sqlsql-serversql-server-2008
J. Davidson
J. Davidson

Reputation: 3307

Returning multiple values in stored procedure

I have a simple stored procedure. In SalesTable the ItemId i.e 1001 is repeated 10 times with different values. So this procedure should select 10 values of Amount and EmployeeName. However it is just returning the one value the first occurrence of ItemId. What is wrong here?

I want the procedure to select and return 10 values of Amount and EmployeeName. When I run the query separately in SQL Server Management Studio, it returns all values but in stored procedure only returns the first value. Please help. 

ALTER procedure entitiesRead
   @ItemId               integer
   ,@SessionId           integer      
as
begin
   select
       a.amount as Amount
       b.EmployeeName               
   from 
       salesTable a
   left outer join 
       nameEmployees b on (b.id = a.employeeId)
   where 
       ItemId = @ItemId
end

Upvotes: 0

Views: 195

Answers (1)

Facio Ratio
Facio Ratio

Reputation: 3393

Your query did not work for me; it contained an error. Add a comma after 'Amount' in your select:

select
a.amount as Amount,
b.EmployeeName               
from salesTable a
left outer join nameEmployees b on(b.id=a.employeeId)
where ItemId=@ItemId

By the way, here was my test, which worked fine. You may want to double check the data in your tables as well:

CREATE TABLE #salesTable ( ItemId int, amount money, employeeId int )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 100.00, 1 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 101.00, 1 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 102.00, 1 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 103.00, 1 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 104.00, 1 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 105.00, 2 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 106.00, 2 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 107.00, 2 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 108.00, 2 )
INSERT INTO #salesTable ( ItemId, amount, employeeId ) VALUES ( 1001, 109.00, 2 )

CREATE TABLE #nameEmployees ( id int, employeeName varchar(50) )
INSERT INTO #nameEmployees ( id, employeeName ) VALUES ( 1, 'John Smith' )
INSERT INTO #nameEmployees ( id, employeeName ) VALUES ( 2, 'Jane Doe' )

select
a.amount as Amount,
b.EmployeeName               
from #salesTable a
left outer join #nameEmployees b on(b.id=a.employeeId)
where ItemId=1001

Upvotes: 2

Related Questions