Return a resultset from a Stored Pro

Hello all,
I want to be able to be able to return a resultset from a Stored Procedure.

Something like :

CREATE PROCEDURE LSNOnAJob
@.MyJobNo AS INT,@.MyLsn VarChar(10) OUTPUT

AS

SELECT @.MyLsn = dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE (dbo.TJob.IJobN = @.MyJobNo)
GO

I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.

I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.would this work for you or do you need the results returned in an output variable?

CREATE PROCEDURE LSNOnAJob
@.MyJobNo AS INT
--,@.MyLsn VarChar(10) OUTPUT

AS

SELECT dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE dbo.TJob.IJobN = @.MyJobNo
GO|||I need the results returned back to an Access DB|||Just use a pass through query and EXEC the sproc...