0

I am trying to get a list of values in a SQL table as output based on an input parameter using the following SQL stored procedure.

CREATE PROCEDURE GetFirstNames
  @LastName nvarchar(128),
  @FirstNames nvarchar(128) OUTPUT
  AS
  SET @FirstNames = (SELECT FirstName FROM NamesTable WHERE LastName = @LastName)
GO

I am using the following code to get the list of first names from the table.

SqlParameter lastNameParam = new SqlParameter("@LastName", "Smith");
SqlParameter firstNamesParameter = new SqlParameter("@FirstNames", SqlDbType.NVarChar, 128);
firstNamesParameter.Direction = ParameterDirection.Output;
string sql = String.Format("EXEC dbo.GetFirstNames {0}, {1};",
    lastNameParam.ParameterName,
    firstNamesParameter.ParameterName);
context.Database.ExecuteSqlCommand(sql, lastNameParam, firstNamesParameter);

When I call the ExecuteSqlCommand method I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
  1. How I can resolve the query error so that I can get the list of first names?
  2. How can I return this list and use it in my c# code?

I would really appreciate if someone can help me with this. Thanks.

mtcup
  • 167
  • 1
  • 1
  • 12
  • 2
    You sure that `SELECT FirstName FROM NamesTable WHERE LastName = @LastName` returns only 1 `FirstName`? – Elliot Rodriguez Nov 17 '16 at 16:37
  • It does not return any value, I get an exception with the given error message. The table has more values and I can see multiple value by running the same query in SSMS. – mtcup Nov 17 '16 at 16:39
  • 1
    Run the query in a query window. Confirm it returns only 1 `FirstName` with whatever argument you are using for `@LastName`. For example, if you are passing in `Smith` for the argument value, what does `SELECT FirstName from NamesTable WHERE LastName = 'Smith'` return? It probably returns more than 1 row, which is the reason for the exception. – Elliot Rodriguez Nov 17 '16 at 16:40
  • 1
    What @ElliotRodriguez means is that probably the error has nothing to do with your c# app. There must be in your `NamesTable` more than one row with the `LastName=Smith` – Pikoh Nov 17 '16 at 16:42
  • 1
    Why do you have the SET? What you want is just the select -- then have the C# deal with the returned table of names – Hogan Nov 17 '16 at 16:44
  • @ElliotRodriguez, you are correct. The table has multiple rows where LastName = 'Smith' and the console query does return multiple rows. My objective is to get a list of FirstNames whose LastName matches 'Smith'. – mtcup Nov 17 '16 at 16:48
  • @Hogan, I am not a SQL expert. I just found this query in one of the other post and using that. – mtcup Nov 17 '16 at 16:52
  • @mtcup -- as an non-expert it should be clear this is not the query you wanted. – Hogan Nov 17 '16 at 17:06
  • U can use SELECT @FirstNames = FirstName FROM NamesTable WHERE LastName = – Vivek Nuna Nov 17 '16 at 17:21
  • @viveknuna -- that is totally wrong and won't fix this problem. – Hogan Nov 17 '16 at 19:06

1 Answers1

2
CREATE PROCEDURE GetFirstNames
  @LastName nvarchar(128)
  AS
SET NOCOUNT ON
SELECT FirstName FROM NamesTable WHERE LastName = @LastName
GO

Will fix the query portion and give you an enumerable result set. The rest is an exercise for the OP.

Elliot Rodriguez
  • 608
  • 6
  • 25
  • Thanks @Elliot. I see all the values getting returned in SSMS. Could you please tell me how to get access to this in my c# code `context.Database.ExecuteSqlCommand(sql, lastNameParam, firstNamesParameter);`? I don't see any value in the output parameter. I changed the query as `SELECT @FirstNames = FirstName FROM NamesTable WHERE LastName = @LastName`. Now I see only the first value. Can I get all the values? – mtcup Nov 17 '16 at 17:01
  • https://msdn.microsoft.com/en-us/library/system.data.entity.database.sqlquery(v=vs.113).aspx#M:System.Data.Entity.Database.SqlQuery(System.Type,System.String,System.Object[]) – Elliot Rodriguez Nov 17 '16 at 17:04
  • 2
    @mtcup you don't want ExecuteSqlCommand if you are getting a result set. – Hogan Nov 17 '16 at 17:05
  • 1
    http://stackoverflow.com/a/1260998/215752 shows you how to run the SP and use a reader afterwards. – Hogan Nov 17 '16 at 17:07
  • @Hogan, I am using the ExecuteSqlCommand, because this is being used a lot in our project, so I followed the same pattern. Thanks for the pointer to use ExecuteReader method. I will try this out. – mtcup Nov 17 '16 at 17:09
  • 1
    Enjoy. If you want a simple way to return objects you can check out this 150 line framework I wrote -- https://gist.github.com/hoganlong/b7f5c5e8dde61ae3cd6f – Hogan Nov 17 '16 at 17:13
  • @Hogan, Thanks again for the recommendation to use ExecuteReader method. I was able to get all the values from the table as expected. The GitHub link was very good reference.I really appreciate your help. – mtcup Nov 17 '16 at 18:09