Nilaksha Perera Nilaksha Perera - 16 days ago 5
SQL Question

Cannot create stored procedure. Only one expression can be specified in the select list

I want to create a stored procedure to return specific integer values depending on user login credentials. The following error occurs:


Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


This is my query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Login
-- Add the parameters for the stored procedure here
@userName nvarchar(100),
@password nvarchar(100)
AS
BEGIN

SET NOCOUNT ON;

IF((Select COUNT(*) as Count FROM SimpleSite.dbo.Users WHERE UserName = @userName)=1)
BEGIN
IF((SELECT * FROM SimpleSite.dbo.Users WHERE UserName = @userName AND [Password] = @password)=1)
-- valid login
RETURN 1
ELSE
-- wrong password
RETURN 2
END
ELSE
-- No account associated with the username
RETURN 3
END
GO


How can I overcome this problem ?

Answer
   IF((SELECT * FROM SimpleSite.dbo.Users WHERE UserName = @userName AND [Password] = @password)=1)

This will not equal 1, it will return all the fields for the row.

Change it to this...

   IF((SELECT count(*) FROM SimpleSite.dbo.Users WHERE UserName = @userName AND [Password] = @password)=1)