Returning multiple values from a SQL Server stored procedure

Question

What I would like to do is use my spLogin stored procedure to return two values from the same table, both of which I would like to save in sessions in C#.

Here is the table

create table tbClients
(
    ClientID int primary key identity(0,1),
    ClientFirstName varchar(20),
    ClientLastName varchar(20),
    ClientAddress varchar(60),
    ClientOrigin varchar(20),
    ClientUsername varchar(20),
    ClientPassword int,
    ClientSecurity int
)

When the client clicks the login button I want to code a procedure that will check to see if the user is valid, what their security level is, and that their first name is.

Here is what I have so far

create procedure spLogin(
    @ClientUsername varchar(20),
    @ClientPassword int
)
AS BEGIN
    DECLARE @Security int
    DECLARE @ClientFirstName varchar(20)        

    IF EXISTS (SELECT * FROM tbClients 
               WHERE ClientUsername = @ClientUsername 
                 AND ClientPassword = @ClientPassword)
    BEGIN               
        SELECT 
            @Security = ClientSecurity, 
            @ClientFirstName = ClientFirstName 
        FROM tbClients 
        WHERE
            ClientUsername = @ClientUsername 
            AND ClientPassword = @ClientPassword

        IF(@Security = 1)
        BEGIN
            SELECT 'Admin' as Security, @ClientFirstName
        END
        ELSE
        BEGIN
            SELECT 'Customer' as Security, @ClientFirstName
        END
    END
    ELSE
    BEGIN
        SELECT 'INVALID'
    END
END
GO

Don't know if this will work because I am not sure how to store these values in C# without using a dataset, which doesn't seem to be working so far?

Why can't you fill a dataset with the values that get returned? Well, first of all your "INVALID" needs to return 2 values, not just one. But once that's fixed, you should be able to fill a dataset with the stored procedure results, and then read the dataset and process it on the C# side. I've done this many times, myself. — Johnny Bones, Jul 16 '15 at 14:28

M.Ali · Answer 1 · 2015-07-16T15:19:07.120

I would write this procedure a bit differently something like this.....

create procedure spLogin
    @ClientUsername     varchar(20)
   ,@ClientPassword     int
   ,@Security           VARCHAR(10)     OUTPUT
   ,@ClientFirstName    varchar(20)     OUTPUT
   ,@ValidLogin         INT             OUTPUT
AS 
BEGIN
  SET NOCOUNT ON;        

    IF EXISTS (SELECT * FROM tbClients 
               WHERE ClientUsername = @ClientUsername 
                 AND ClientPassword = @ClientPassword)
      BEGIN               
            SELECT @ValidLogin = 1 
                  ,@Security = CASE WHEN ClientSecurity = 1 
                                 THEN 'Admin' ELSE 'Customer' END
                  ,@ClientFirstName = ClientFirstName 
            FROM tbClients 
            WHERE ClientUsername = @ClientUsername 
              AND ClientPassword = @ClientPassword
      END
    ELSE
      BEGIN
            SET @ValidLogin = 0;
      END
END
GO

Not an expert of C# but you would handle the output parameters in C# something like....

// define connection and command, in using blocks to ensure disposal
using(SqlConnection conn = new SqlConnection(pvConnectionString ))
using(SqlCommand cmd = new SqlCommand("dbo.spLogin", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    // set up the input parameters
    cmd.Parameters.Add("@ClientUsername", SqlDbType.VarChar, 20);
    cmd.Parameters.Add("@ClientPassword", SqlDbType.Int);
    cmd.Parameters.Add("@Security", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@ClientFirstName", SqlDbType.VarChar, 20).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@Success", SqlDbType.Int).Direction = ParameterDirection.Output;

    // set parameter values
    cmd.Parameters["@ClientUsername"].Value = UserNamTextbox.Text;

    // open connection and execute stored procedure
    conn.Open();
    cmd.ExecuteNonQuery();

    // read output value from @Security
    int Security = Convert.ToInt32(cmd.Parameters["@Security"].Value);

    if Security == 1 ....... and so on.......

    conn.Close();
}

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

so I am not 100% sure how much of the C# you have on your end but here is the basics:

Use the code in this SO question to get your data from SQL Server: Fill DataTable from SQL Server database

Then once you have your datatable you can loop through it with a for loop like so (assuming you defined your Datatable as 'dt'):

foreach (DataRow row in dt.Rows) 
{
    //do what you need with your data.
    var value = row["ColumnName"];
    Console.WriteLine(value.ToString());
}

Hope that helps.

edited May 23 '17 at 12:06

Community

1
1

answered Jul 16 '15 at 15:28

Mark Atkinson

458
8
14

His output should only return 1 record, so this answer is useless for the OP's application. – Johnny Bones Jul 16 '15 at 15:54

score 0 · Answer 3 · answered Jul 17 '15 at 07:45

0

The result your store procedure only return one row, you can check if column exists in datatable :

if (dt.columns.Contains("Security"))
{
   ....
}else{
   // Show error in fist row, fist column  
   return dt.Rows[0][0].ToString();
}

answered Jul 17 '15 at 07:45

Giau Huynh

300
3
15

score 0 · Answer 4 · answered Jul 17 '15 at 07:54

0

You can execute the storedprocedure and fill the result into dataset.

Each select statement will be stored as a datatable and you can access using the index to access corresponding table.

answered Jul 17 '15 at 07:54

Mallikarjuna

130
9

how do? you have a any example? please. – GeoKlar Apr 10 '18 at 22:05

Returning multiple values from a SQL Server stored procedure

4 Answers4