Question Output variable stored procedure

[adarsh_fj]

Hi all, this is my first post to this forum and my first experience with SQL Server and Visual Basic.net programming. I am really new to this so please bear with me. i have written a stored procedure as below and i want to retrieve the value, but i am confused as to how to do this. I have sql server express edition.

create procedure [dbo].[Access_Level]
@Username nchar(10),
@Password nchar(10), @Access_Level int output
as
begin
select @Access_Level= Access
from User_Control
where Username=@Username and [Password]=@Password
end

can some1 please guide me from here, i need this stored procedure to return the value of @Access_Level to my .net program. please also find the .net coding as below.


sqlcomm.CommandType = CommandType.StoredProcedure
sqlcomm.CommandText = "Access_Level"
sqlcomm.Connection = sqlconn

sqlcomm.Parameters.Clear()

sqlcomm.Parameters.Add("@Username", SqlDbType.NChar, 10).Value = username
sqlcomm.Parameters.Add("@Password", SqlDbType.NChar, 10).Value = password
sqlcomm.Parameters.Add("Access_Level", SqlDbType.Int).Direction = ParameterDirection.Output

level = sqlcomm.ExecuteScalar
sqlconn.Close()


please also tell me if i am doing smthing wrong here too. thanks

 

[jmcilhinney]

There's no point using an output parameter in that sproc. Just change this:

select @Access_Level= Access

to this:

select Access

and ExecuteScalar will return the value you want.

 

[adarsh_fj]

thank you very much sir, it works now. Sir, just a request, if you would tell me, if i want to use a output variable then how do i use it, just for educational purpose. thanks once again

 

[jmcilhinney]

You would call ExecuteNonQuery rather than ExecuteScalar if your command will not produce a result set. You then get the output parameter from the command's Parameters collection by name or by index, or you might already have a reference to it from when you created it. You then get the Value property of that parameter. It's basically the same as an input parameter but in reverse, i.e. you get the value instead of setting it.