从存储过程中检索返回值
本文关键字:检索 返回值 过程中 存储过程 存储 | 更新日期: 2023-09-27 17:56:44
>我写了一个表值参数存储过程,它返回插入的 Id。如果我从 SSMS 中的查询运行它,它会正常工作并正确返回值。
当我从代码调用该过程时,它会运行并插入数据,但不设置我添加并标记为返回参数的返回参数。
我在这里做错了什么?
SP:
ALTER PROCEDURE [dbo].[InsertUpdateMeeting]
-- Add the parameters for the stored procedure here
@Meeting MeetingsTableType READONLY,
@Area AreasTableType READONLY,
@MeetingLocation MeetingLocationsTableType READONLY
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @MeetingId int = 0;
DECLARE @AreaId int;
DECLARE @MeetingLocationId int;
SELECT TOP 1 @MeetingId=m.MeetingId FROM @Meeting AS m;
SELECT TOP 1 @MeetingLocationId = ml.MeetingLocationId FROM @MeetingLocation AS ml;
SELECT TOP 1 @AreaId = a.AreaId FROM @Area AS a;
UPDATE Areas SET
Hotline=areaParam.Hotline,
Name=areaParam.Name
FROM Areas INNER JOIN @Area AS areaParam
ON Areas.AreaId=areaParam.AreaId;
IF(@@ROWCOUNT=0)
BEGIN;
INSERT INTO Areas
(Hotline,Name)
SELECT areaParam.Hotline,areaParam.Name FROM @Area AS areaParam;
SET @AreaId=SCOPE_IDENTITY();
END;
UPDATE MeetingLocations SET
Address1=ml.Address1,
Address2=ml.Address2,
Address3=ml.Address3,
City=ml.City,
[State]=ml.[State],
Zip=ml.Zip,
ClubName=ml.ClubName,
ClubDescription=ml.ClubDescription,
MapLink=ml.MapLink
FROM MeetingLocations INNER JOIN @MeetingLocation AS ml
ON MeetingLocations.MeetingLocationId=ml.MeetingLocationId;
IF(@@ROWCOUNT=0)
BEGIN;
INSERT INTO MeetingLocations
(Address1,Address2,Address3,City,[State],Zip,ClubName,ClubDescription,MapLink)
SELECT ml.Address1,ml.Address2,ml.Address3,ml.City,ml.[State],ml.Zip,ml.ClubName,ml.ClubDescription,ml.MapLink
FROM @MeetingLocation AS ml;
SET @MeetingLocationId=SCOPE_IDENTITY();
END;
UPDATE Meetings SET
AreaId = @AreaId,
MeetingLocationId=@MeetingLocationId,
Name = meetingParam.Name
FROM Meetings INNER JOIN @Meeting AS meetingParam
ON Meetings.MeetingId=meetingParam.MeetingId;
IF(@@ROWCOUNT=0)
BEGIN;
INSERT INTO Meetings
(MeetingLocationId,AreaId,Name)
SELECT @MeetingLocationId,@AreaId,meetingParam.Name FROM @Meeting AS meetingParam;
SET @MeetingId=SCOPE_IDENTITY();
END;
RETURN @MeetingId;
END
C# 代码:
...create tables...
var meetingLocationParam = new SqlParameter("@MeetingLocation", meetingLocationTable);
meetingLocationParam.SqlDbType = SqlDbType.Structured;
meetingLocationParam.TypeName = "MeetingLocationsTableType";
var meetingParam = new SqlParameter("@Meeting", meetingTable);
meetingParam.SqlDbType = SqlDbType.Structured;
meetingParam.TypeName = "MeetingsTableType";
var areaParam = new SqlParameter("@Area", areaTable);
areaParam.TypeName = "AreasTableType";
areaParam.SqlDbType = SqlDbType.Structured;
using (var connection = new SqlConnection(conn.ConnectionString))
using (var command = new SqlCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = "EXEC dbo.InsertUpdateMeeting";
command.Parameters.AddRange(new SqlParameter[]
{meetingParam, areaParam, meetingLocationParam, new SqlParameter()
{
Value=0,
Direction=ParameterDirection.ReturnValue,
SqlDbType=SqlDbType.Int,
ParameterName="@MeetingId"
} });
var otherRet = command.ExecuteNonQuery();
//Assert.IsTrue(ret > 0);
}
该输出值不会从ExecuteNonQuery
返回。尝试创建对返回参数的引用,如下所示:
var returnParameter = new SqlParameter[]
{meetingParam, areaParam, meetingLocationParam, new SqlParameter()
{
Value=0,
Direction=ParameterDirection.ReturnValue,
SqlDbType=SqlDbType.Int,
ParameterName="@MeetingId"
} }
command.Parameters.Add(returnParameter);
然后在执行后,
var myReturnedValue = returnParameter.Value;
您也可以更改
RETURN @MeetingId;
自
SELECT @MeetingId;
,然后使用
var myReturnedValue = (int)command.ExecuteScalar();
您需要
使用 ExecuteScalar
来 catpure 返回值,或使用 output
参数。
这篇文章回答了一个简单的问题。使用返回值调用存储过程
有多种方法可以使用 ADO.NET 从存储过程获取返回值。
我的示例是我以前使用过的通用方法。可以使用存储过程或文本传递 SQL 命令。
SqlAdapter
/// <summary> /// Returns a DataSet given an Sql Command /// </summary> /// <param name="cmd">The SqlCommand object</param> /// <returns>DataSet</returns> public static DataSet GetDataSet(SqlCommand cmd) { using (SqlConnection con = DbConnect()) // DbConnect() returns SqlConnection instance { DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); cmd.Connection = con; da.Fill(ds, "my_dataset"); return ds; } }
执行标量
/// <summary> /// Returns a single value from the database given an Sql Command object /// </summary> /// <param name="cmd">The Sql Command object</param> /// <returns>string</returns> public static string GetSingleFromDatabase(SqlCommand cmd) { using (SqlConnection con = DbConnect()) // DbConnect() returns SqlConnection instance { cmd.Connection = con; try { object returnedValue = cmd.ExecuteScalar(); if (returnedValue != null) { return returnedValue.ToString(); } else return ""; } catch (Exception) { throw; } } }
我刚刚注意到您没有在 SP 定义中声明您的 @MeetingId 参数(您在正文中声明了,但我认为不能使用)。
ALTER PROCEDURE [dbo].[InsertUpdateMeeting]
-- Add the parameters for the stored procedure here
@Meeting MeetingsTableType READONLY,
@Area AreasTableType READONLY,
@MeetingLocation MeetingLocationsTableType READONLY,
@MeetingId <datatype> OUTPUT -- ADD THIS
AS
然后在你的代码中,你可以用这个来获取它:
command.Parameters["@MeetingId"].Value