如何从存储过程中获取返回值？ [英] How to get return value from stored procedure?

问题描述

你好亲爱的，



我创建一个存储过程如下，

在这个存储过程中我想插入用户注册详细信息tblUserInfo。

如果数据库中不存在电子邮件ID，那么我想从存储过程返回1，并将数据保存到我已传递给存储过程的数据库中。

if email已经存在于数据库中..我想从存储过程返回2并且数据不想保存。



我尝试了以下存储过程，但它产生了意想不到的结果。我怎么能这样做。

hello dears,

I create a stored procedure as follow,
In this store procedure I want to insert user registration details in tblUserInfo.
if email Id is not exist in database then I want to return 1 from stored procedure and save data in database that I have passed to stored procedure.
if email is already exist in database.. I want to return 2 from stored procedure and data dont want to save.

I tried following stored procedure but it produce unexpected result. How can I do it.

ALTER PROCEDURE [dbo].[userRegistration] 

	@userId VARCHAR(12),
	@emailadr VARCHAR(50),
	@password VARCHAR(12)
	

	AS
	BEGIN

	DECLARE @userAutoId VARCHAR(64)=NEWID();
	DECLARE @firstName VARCHAR(15)='';
	DECLARE @lastName VARCHAR(15)='';
	DECLARE @gender BIT=1;
	DECLARE @isDeleted BIT= 0;
	DECLARE @Status INTEGER;
	
	IF NOT EXISTS(SELECT * FROM tblUserInfo WHERE email=@emailadr)
	BEGIN
	
	INSERT INTO tblUserInfo VALUES(
							@userAutoId,
							@firstName,
							@lastName,
							@emailadr,
							@userid,
							@password,
							@gender,
							@isDeleted

	)
	SET @Status=1
	END
	
	ELSE
	BEGIN
	
	SET @Status=2
	
	END
	
	SELECT @Status 
	
END

和我的c＃代码是

and my c# code is

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);

      con.Open();
      SqlCommand cmd = new SqlCommand("userRegistration ", con);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add("@userId", SqlDbType.VarChar).Value = userId;
      cmd.Parameters.Add("@emailadr", SqlDbType.VarChar).Value = emailadr;
      cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = password;


       SqlDataReader rdrStatus = cmd.ExecuteReader();
      if (rdrStatus[0].ToString() == "1")
      {
          string result = "1";
          json = new JavaScriptSerializer().Serialize(result);

      }
      else if (rdrStatus[0].ToString() == "1")
      {
          string result = "2";
          json = new JavaScriptSerializer().Serialize(result);
      }
      con.Close();
      return json;
  }

我尝试过：



我正在尝试新用户注册。不能使用已经使用的电子邮件ID

What I have tried:

I am trying to new user registration. don't able to use already used email id

推荐答案

检查出来：如何从SQL存储过程中获取返回值？ [ ^ ]

这篇关于如何从存储过程中获取返回值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！