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

Question

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 ?

You have two selects. Any different approach in one of them? — jarlh, Nov 22 '16 at 09:57

score 5 · Accepted Answer · answered Nov 22 '16 at 09:58

5

   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)

answered Nov 22 '16 at 09:58

ChrisBint

12,773
6
40
62

Thanks mate. My bad i didn't see this – Nilaksha Perera Nov 22 '16 at 10:04
1

@chrisBint Count(*) is taking more time rather than Count(1) why you not use the Count(1) in this query – A.Goutam Nov 22 '16 at 10:06
1

@A.Goutam - this has not been true since at least SQL Server 2005 - see http://stackoverflow.com/questions/1221559/count-vs-count1 – Ed Harper Nov 22 '16 at 10:16

score 0 · Answer 2 · answered Nov 22 '16 at 10:04

Please try and give you feedback

IF EXISTS((Select COUNT(1) as Count FROM SimpleSite.dbo.Users WHERE UserName = @userName AND [Password] = @password)=1)
      BEGIN
       IF((SELECT COUNT(1) 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

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

2 Answers2