Stored procedure ,return value, Display,C#

Posted by Raja_89 under Sql Server on 4/4/2016 | Points: 10 | Views : 487 | Status : [Member] | Replies : 3
Hai
I am creating a stored procedure with return value to display values returned from stored procedure to UI

Sample code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE InsertRecords
-- Add the parameters for the stored procedure here
@id varchar(100),
@psw varchar(100),
@MsgtoUser varchar(100) output
AS
begin try
BEGIN Transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT off;
insert into my_table(id,psw)values(@id,@psw)
-- Insert statements for procedure here
Commit Transaction
set @MsgtoUser= @tc_usernameToDb+'registered sucessfully'
return @MsgtoUser
end try
begin catch
set @MsgtoUser='From catch block'
return @MsgtoUser
Rollback Transaction
end catch


using (objsqlcmd = new SqlCommand(InsertRecords, objsqlcon))
{
try
{


objsqlcmd.CommandType = CommandType.StoredProcedure;


objsqlcmd.Parameters.AddWithValue("@id", 123);
objsqlcmd.Parameters.AddWithValue("@psw", Psw@123);
objsqlcmd.Parameters.Add("@MsgtoUser", SqlDbType.Char, 500);
objsqlcmd.Parameters["@MsgtoUser"].Direction = ParameterDirection.Output;
objsqlcmd.ExecuteNonQuery();
message = (string)objsqlcmd.Parameters["@MsgtoUser"].Value;
}

Is this code is fine or kindly share your thoughts to proceed.

Regards




Responses

Posted by: Rajnilari2015 on: 4/4/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
OK.This should work. (:

--
Thanks & Regards,
RNA Team

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Poster on: 4/4/2016 [Member] Starter | Points: 25

Up
0
Down
What should work Rajnilari2015?

@Raja_89,

Have you tried your code? If not, please try as this should work. If not, declare the SqlParameter variable and add it to the parameters collection and then execute the query and then try to get the sqlparameter variable value.

Thanks

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 4/4/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
@Poster Sir, thanks for the info. But from the review perspective, it is understandable..If a person can write the SP and know how to pass params from ADo.net, it is understandable that he should know how to accept the parameters in SP (:

--
Thanks & Regards,
RNA Team

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response