Stored procedure output value to variable

Question

I'm new to stored procedure world.

We have existing system with existing stored procedure, which checks username and url path. Stored procedure will check whether user details exist. If exists return value groupname where user name belong to.

All I want is writing asp.net c# code for this stored procedure. Passing user details and path and returning stored procedure return value into string variable.

Below link for SQL Server execute stored procedure

http://www.natboxservices.com/helmshore/img/pic.jpg

Below link for SQL Server output value look like

http://www.natboxservices.com/helmshore/img/2012-07-03%2009.35.13.jpg

note that above picture groupname is empty cause i executed stored procedure with wrong input and there is no groupname exists for that data in sqlserver. if exists it will return a value for group name such as "administrator " or "Manager"

my question is "if data exists and return group name i want to pass that value into string variable" for that assigning the parameter and pass return value into string variable

This is my storedprocedure

USE [IRC_siteadmin]
GO
/****** Object:  StoredProcedure [dbo].[sp_intranet_GetSecurity]    Script Date: 07/03/2012 10:05:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_intranet_GetSecurity]

@username VARCHAR(50),
@path VARCHAR(200),
@errorID INT OUTPUT

AS

BEGIN

SELECT
    G.name as groupname
FROM
    tbl_site_link L WITH(NOLOCK)
INNER JOIN
    tbl_site_link_group LG WITH(NOLOCK)
ON
    L.link_id = LG.link_id
INNER JOIN
    tbl_site_group G WITH(NOLOCK)
ON
    LG.group_id = G.group_id
INNER JOIN
    (       SELECT 
                group_id
            FROM 
                tbl_site_user U WITH(NOLOCK) 
            INNER JOIN
                tbl_site_user_group UG WITH(NOLOCK)
            ON
                U.user_id = UG.user_id
            WHERE 
                [name] = @Username ) U
ON
    G.group_id = U.group_id
WHERE
    L.url LIKE '%'+@path+'%'
    AND
    (U.group_id IS NOT NULL)

UNION

SELECT
    'Uber' as groupname
FROM
    tbl_site_link L WITH(NOLOCK)
INNER JOIN
    tbl_site_link_group LG WITH(NOLOCK)
ON
    L.link_id = LG.link_id
CROSS JOIN
    (       SELECT 
                group_id
            FROM 
                tbl_site_user U WITH(NOLOCK) 
            INNER JOIN
                tbl_site_user_group UG WITH(NOLOCK)
            ON
                U.user_id = UG.user_id
            WHERE 
                [name] = @Username
            AND
                group_id = 1 ) U
WHERE
    L.url LIKE '%'+@path+'%'
    AND
    (U.group_id IS NOT NULL)

END

any help appreciated

thanks

Answer 1

try with this code

             string stringConnection = "Your connection to your database";
            using(var connection = new SqlConnection(stringConnection)
            {
                connection.Open();

                using (var command = new SqlCommand("sp_intranet_GetSecurity", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar));
                    command.Parameters["@username"].Value = "Your value";

                    command.Parameters.Add(new SqlParameter("@path", SqlDbType.VarChar, 8));
                    command.Parameters["@path"].Value = "Your value";

                    command.Parameters.Add(new SqlParameter("@errorID", SqlDbType.VarChar, 8));
                    command.Parameters["@errorID"].Value = "Your value";
                    command.Parameters["@errorID"].Direction = ParameterDirection.Output;

                    // Objet DataReader
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    Object[] row = null;
                    while (reader.Read())
                    {
                        if (row == null)
                        {
                            row = new Object[reader.FieldCount];
                        }
                        reader.GetValues(row);
                        for (int i = 0; i < row.GetLength(0); i++)
                        {
                            if (row[i] != DBNull.Value)
                            {
                                Console.Write(row[i]);
                            }
                            else
                            {
                                Console.Write("NULL");
                            }
                            if (i < row.GetUpperBound(0))
                            {
                                Console.Write("|");
                            }
                        }
                    }


               }
            }

Answer 2

How to: Execute a Stored Procedure that Returns a Single Value

Getting return value from stored procedure in C#