how to read the parameters defined in declare statement in c#

Question

In one of my c# projects I want to read the parameters defined in declare statement from the following procedure.

create procedure [dbo].[TestRecordSelectMInfo2]
@GRNo varchar(4)
as 
declare 
@SName varchar(35),
@Class Varchar(15)
begin
Select @SName = Students.SName, @Class = Class.Class from Students inner join Class on Students.ClassId = Class.ClassId where Students.GRNo = @GRNo
end

How can I read The values of @SName and @Class using c#?

score 2 · Answer 1 · edited May 23 '17 at 11:49

2

The use of variables in that stored procedure doesn't make sense. I think you are over-complicating this.

Just change your select statement to this and they will be returned from the stored procedure to the calling code in the resulting recordset.

Select Students.SName, Class.Class 
FROM Students inner join Class on Students.ClassId = Class.ClassId 
WHERE Students.GRNo = @GRNo

If you really must return a parameter (and I don't think you do). You can define the variable as an output parameter from the stored procedure as described here.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 22 '12 at 06:34

JohnFx

34,542
18
104
162

I know how to do that... the given procedure is only for an example. The actual pro is a little lengthy that's why a changed it to a simple one. I must set Students.SName and Class.Class in a veriable and that read it. would you like me to post the actual coding in my question????? – Apr 22 '12 at 06:38
Actually, you probably don't need to do that. It is very uncommon. In any case, I edited my answer in case you insist on doing it that way, which I don't advise. – JohnFx Apr 22 '12 at 06:42
1

The whole part about the output parameter.... Please at least put some effort into this if you expect people to help you. – JohnFx Apr 22 '12 at 06:51

kashif · Accepted Answer · 2012-04-22T16:31:53.107

alter your procedure like this

create procedure [dbo].[TestRecordSelectMInfo3]
@GRNo varchar(4),
@SName varchar(35) output,
@Class Varchar(15) output
as 
begin
Select @SName = Students.SName, @Class = Class.Class from Students inner join Class on Students.ClassId = Class.ClassId where Students.GRNo = @GRNo
end

USAGE:

private void GetData()
        {
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@GRNo", SqlDbType.VarChar).Value = tbgrno.Text;
            SqlParameter SName = new SqlParameter("@SName", SqlDbType.VarChar);
            SqlParameter Class = new SqlParameter("@Class", SqlDbType.VarChar);
            SName.Direction = ParameterDirection.Output;
            Class.Direction = ParameterDirection.Output;
            SName.Size = 35;
            Class.Size = 15;
            cmd.Parameters.Add(SName);
            cmd.Parameters.Add(Class);
            dm.ExectNonActQuery("TestRecordSelectMInfo", cmd);
            tbsname.Text = cmd.Parameters["@SName"].Value.ToString();
            tbclass.Text = cmd.Parameters["@Class"].Value.ToString();
        }
        public void ExectNonActQuery(string spname, SqlCommand command)
        {
            SqlConnection cn = new SqlConnection("connection string");
            cmd = command;
            cmd.Connection = cn;
            cmd.CommandText = spname;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
        }

how to read the parameters defined in declare statement in c#

2 Answers2

Linked