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

Ever heard of screen-grabs?! You're working with computers, and you take a photo of the screen!?! — freefaller, Jul 03 '12 at 08:55
where is your stored procedure? share the code. not a screen shot — Prashanth Thurairatnam, Jul 03 '12 at 08:56
Why are you using table hints (`WITH(NOLOCK)`)? This is [not recommended unless debugging specific performance issues](http://msdn.microsoft.com/en-us/library/ms187373.aspx). — CodeCaster, Jul 03 '12 at 09:11
Hey i don't have any clue... this is existing stored procedure at my work place to check access level of user.. they want me to connect with an asp.net application which i developed... — mshiyam, Jul 03 '12 at 09:21

score 1 · Accepted Answer · edited May 23 '17 at 11:44

1

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

Getting return value from stored procedure in C#

edited May 23 '17 at 11:44

Community

1
1

answered Jul 03 '12 at 09:04

Geek

429
2
5

1

You read those articles and try to solve. This is the best way to learn. – Geek Jul 03 '12 at 09:11

score 0 · Answer 2 · answered Jul 03 '12 at 09:23

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("|");
                            }
                        }
                    }


               }
            }

Stored procedure output value to variable

2 Answers2