存储过程在sql server中返回值，但在asp.net中不返回值

本文关键字：返回值 asp net 但在 server 存储过程 sql | 更新日期: 2023-09-27 18:14:39

我有一个存储过程返回值(letterNo)而不提供任何参数，它工作，但相同的过程在asp.net代码中给出错误的值，即它只返回0，但在SP中它返回预期值，如1,2等

为什么?

SP: 

ALTER PROCEDURE [dbo].[SelectLetterNoFromComposedLetter]
 @LetterNo bigint output
AS
BEGIN
    Declare @Date varchar(5)
    Set @Date =(Select convert(Varchar(5),getdate(),110))
    Declare @MaxComposeLetterID bigint
    Set @MaxComposeLetterID = (Select MAX(ComposedLetterID) from ComposedLetter)
    Set @LetterNo= (Select Substring(ComposedLetter.LetterNo, 15,20) from ComposedLetter
    where ComposedLetterID= @MaxComposeLetterID)
    If (@Date !='01-01') --Check if it's first day of new year or not
    Begin
     Select @LetterNo + 1 as LetterNo  -- if not then it returns existing letterno + 1; incrementing old value
    End
    Else If(@Date= '01-01') -- if current date if 1st January then firstly it checks whether any other entry has been made or not, if made then existing value + 1
     Begin
      If(@LetterNo > 0) --if made then existing value + 1
         Begin
          Select @LetterNo + 1
         End
      Else
      Begin
       Set @LetterNo = 1  -- else if no new record has been inserted on 1st January then return intial value i.e. 1
       Select @LetterNo
      End
    End
END

cs://Page_Load事件

if (!IsPostBack)
        {
            ManageComposedLetter mngCompLetters = new ManageComposedLetter();
            Int64 Letter_No = mngCompLetters.SelectLetterNoFromComposedLetter();
            txtLetterNo.Text = "PPO-CC/" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/"+Letter_No;
}

业务层代码:

public Int64 SelectLetterNoFromComposedLetter() 
        {
            SqlCommand cmd = new SqlCommand("SelectLetterNoFromComposedLetter", DataBaseConnection.OpenConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter pLetterNo = new SqlParameter("@LetterNo", SqlDbType.BigInt);
            cmd.Parameters.Add(pLetterNo);
            pLetterNo.Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            Int64 Result = Convert.ToInt64(pLetterNo.Value);
            return Result;
        }

存储过程在sql server中返回值，但在asp.net中不返回值

存储过程返回多个结果。

Select @LetterNo + 1 as LetterNo  -- if not then it returns existing letterno + 1; incrementing old value
...
Select @LetterNo + 1
...
Select @LetterNo

您应该将它们更改为

Select @LetterNo += 1 as LetterNo  -- if not then it returns existing letterno + 1; incrementing old value
...
Select @LetterNo += 1
...
Select @LetterNo = @LetterNo

或使用set操作符

SET @LetterNo = @LetterNo + 1  
...
SET @LetterNo = @LetterNo + 1
...
SET @LetterNo = @LetterNo

最后一个赋值没有意义(@LetterNo = @LetterNo)，但它是从你的代码中获取的。

您可以尝试在语句中设置变量，并在过程结束时选择它。我认为问题就在那里。我对EF也有类似的问题。

try:

pLetterNo.Direction = ParameterDirection.ReturnValue;
Int64 Result = Convert.ToInt64(cmd.Parameters["@LetterNo"].Value);

编辑

ALTER PROCEDURE [dbo].[SelectLetterNoFromComposedLetter]
AS
BEGIN
Declare @Date varchar(5)
Set @Date =(Select convert(Varchar(5),getdate(),110))
Declare @MaxComposeLetterID bigint
Set @MaxComposeLetterID = (Select MAX(ComposedLetterID) from ComposedLetter)
Declare @LetterNo bigint
Set @LetterNo= (Select Substring(ComposedLetter.LetterNo, 15,20) from ComposedLetter
where ComposedLetterID= @MaxComposeLetterID)
If (@Date !='01-01') --Check if it's first day of new year or not
Begin
 Select @LetterNo = @LetterNo + 1
 reutrn @LetterNo  -- if not then it returns existing letterno + 1; incrementing old value
End
Else If(@Date= '01-01') -- if current date if 1st January then firstly it checks whether any other entry has been made or not, if made then existing value + 1
 Begin
  If(@LetterNo > 0) --if made then existing value + 1
     Begin
      Select @LetterNo = @LetterNo + 1
      return @LetterNo
     End
  Else
  Begin
   Select @LetterNo = 1  -- else if no new record has been inserted on 1st January then return intial value i.e. 1
   return @LetterNo
  End
End
END

地点:

public Int64 SelectLetterNoFromComposedLetter() 
        {
            SqlCommand cmd = new SqlCommand("SelectLetterNoFromComposedLetter", DataBaseConnection.OpenConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter pLetterNo = new SqlParameter("@LetterNo", SqlDbType.BigInt);
            cmd.Parameters.Add(pLetterNo);
            pLetterNo.Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            Int64 Result = Convert.ToInt64(cmd.Parameters["@LetterNo"].Value);
            return Result;
        }

相关文章：