Changing a procedure to return multiple records

[luisdev]

The stored procedure below gives me the user id from a table called
user_table. When I EXEC it using

EXEC dbo.spGetAppID @UserID = '586221'

it works perfectly if there is just one record matching the "WHERE
(user_table.data LIKE @UserID)" criteria.

What do I have to do to get it to return all the user_table.id values
if there is more than one record matching the "user_table.data LIKE
@UserID" criteria?


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.spGetAppID
@UserID nvarchar(10),
@AppID bigint OUTPUT
AS
BEGIN
SET NOCOUNT ON

SELECT @AppID = user_table.id
FROM user_table
WHERE (user_table.data LIKE @UserID)
END

[Bob Barrows]

You're using a scalar output variable in this procedure. This is a great
technique to use to retrieve a single data value. By definition, a scalar
variable can only contain a single value. If you want to see all the
results, you have to change the procedure to return a resultset rather than
an output variable:

CREATE PROCEDURE dbo.spGetAppIDs
@UserID nvarchar(10)
AS
BEGIN
SET NOCOUNT ON

SELECT user_table.id AS AppID
FROM user_table
WHERE (user_table.data LIKE @UserID)
END

Post by luisdev
The stored procedure below gives me the user id from a table called
user_table. When I EXEC it using
it works perfectly if there is just one record matching the "WHERE
What do I have to do to get it to return all the user_table.id values
if there is more than one record matching the "user_table.data LIKE
@UserID" criteria?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.spGetAppID
@UserID nvarchar(10),
@AppID bigint OUTPUT
AS
BEGIN
SET NOCOUNT ON
FROM user_table
END