How to get list of values from SQL stored procedure using ExecuteSqlCommand

Question

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.

How I can resolve the query error so that I can get the list of first names?
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.

You sure that SELECT FirstName FROM NamesTable WHERE LastName = @LastName returns only 1 FirstName? — Elliot Rodriguez
– Elliot Rodriguez, Commented Nov 17, 2016 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
– mtcup, Commented Nov 17, 2016 at 16:39
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
– Elliot Rodriguez, Commented Nov 17, 2016 at 16:40
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
– Pikoh, Commented Nov 17, 2016 at 16:42
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
– Hogan, Commented Nov 17, 2016 at 16:44

Elliot Rodriguez · Accepted Answer · 2016-11-17 16:56:05Z

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.

answered Nov 17, 2016 at 16:56

Elliot Rodriguez

6181 gold badge7 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

mtcup Over a year ago

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?

Elliot Rodriguez Over a year ago

msdn.microsoft.com/en-us/library/…

Hogan Over a year ago

@mtcup you don't want ExecuteSqlCommand if you are getting a result set.

Hogan Over a year ago

stackoverflow.com/a/1260998/215752 shows you how to run the SP and use a reader afterwards.

Hogan Over a year ago

Enjoy. If you want a simple way to return objects you can check out this 150 line framework I wrote -- gist.github.com/hoganlong/b7f5c5e8dde61ae3cd6f

|

Collectives™ on Stack Overflow

How to get list of values from SQL stored procedure using ExecuteSqlCommand

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related