Msg 512, Level 16, State 1, Procedure test, Line 21 Subquery returned more than 1 value

ALTER PROCEDURE [dbo].[test]
@tour int,
@tourname varchar(50) OUTPUT,  
@tourdepartures varchar(50) OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
SET @tourname = (select [tour name] from dbo.products1 where tour = @tour)

SET @tourdepartures = (select ddate7 from dbo.TDEPART1 where tour = @tour and depart > convert(int,getdate()))

END

I want to use a stored procedure to populate a label on my asp.net page and a dropdownlist

@tourname will be populated into a single label

while @tourdepartures will be multiple dates, that i want in a dropdownlist

however when i run my sp i get this error

Msg 512, Level 16, State 1, Procedure test, Line 21
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

(1 row(s) affected)

and it works when i do

ALTER PROCEDURE [dbo].[test]
@tour int,
@tourname varchar(50) OUTPUT,  
@tourdepartures varchar(50) OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
SET @tourname = (select [tour name] from dbo.product where tour = @tour)

SET @tourdepartures = (select top 1 ddate7 from abcfilestest.dbo.TDEPART where tour = @tour and depart > convert(int,getda开发者_运维知识库te()))

END

but it only gives me the first departure date

Get rid of the second SET and just return a resultset that you can use a reader with:

ALTER PROCEDURE [dbo].[test]
@tour int,
@tourname varchar(50) OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
SET @tourname = (select [tour name] from dbo.products where tour = @tour)

select ddate7 from abcfilestest.dbo.TDEPART where tour = @tour and depart > convert(int,getdate())

END

You can try this to remove the error, but I don't think it's really what you want:

    -- Insert statements for procedure here
SET @tourname = (select TOP 1 [tour name] from dbo.products where tour = @tour)

SET @tourdepartures = (select TOP 1 ddate7 from abcfilestest.dbo.TDEPART where tour = @tour and depart > convert(int,getdate()))

If you really want a list of all of the tour dates, change the last statement to

select ddate7 from abcfilestest.dbo.TDEPART where tour = @tour and depart > convert(int,getdate())

and parse the result set on the client as you usually would.

Also, for the love of all that's holy, rename that ddate7 column to something meaningful.

A scalar variable can only hold one value. The query you are using returns more than one value. YOu have several choices and the right one depends on your situation.

If you only want the latest date, use top 1 and order by date desc in your select.

If you want all the dates, then use a table variable not a scalar variable. I know I can use table variable in SQL Server 2008 as stored procedure parameter, not sure about 2005. You could just return 2 recordsets (so that you are consistent in what you are returning) or one output variable and one select instead of using output variables.