1,-- Copy ContractInfoCREATE PROC CopyContractInfo @ContractID UNIQUEIDENTIFIER,@Out_ContractID UNIQUEIDENTIFIER OUTPUT
AS
DECLARE @NewContractID UNIQUEIDENTIFIER
SET @NewContractID=NEWID()
SET @Out_ContractID=@NewContractIDprint 'New ContractID' + ': ' + convert(varchar(100),@NewContractID)
BEGIN TRANSACTION--1,Copy ContractInfo Begin
INSERT ContractInfo(
ContractID,
ContractName,
ReleaseFlag)
SELECT ContractID=@NewContractID,
ContractName,
ReleaseFlag
FROM ContractInfo where ContractID=@ContractIDif @@error<>0 goto err_handle
--1,Copy ContractInfo End-- Copy DistributionPolicy/UsedCreditCard Begin
DECLARE @DistributionPolicyID UNIQUEIDENTIFIER
DECLARE @RowCount INTDECLARE DistributionPolicy_cursor CURSOR FOR
SELECT DistributionPolicyID FROM DistributionPolicy WHERE ContractID = @ContractID
ORDER BY DistributionPolicyIDOpen DistributionPolicy_cursorFETCH NEXT FROM DistributionPolicy_cursor
INTO @DistributionPolicyIDprint 'OLD 1 DistributionPolicyID' + ': ' + convert(varchar(100),@DistributionPolicyID)
SET @RowCount=0WHILE @@FETCH_STATUS = 0BEGIN
print @RowCountDECLARE @NewDistributionPolicyID UNIQUEIDENTIFIER
SET @NewDistributionPolicyID = NEWID()print 'New DistributionPolicyID' + ': ' + convert(varchar(100),@NewDistributionPolicyID)--2,Copy DistributionPolicy Beginprint 'DistributionPolicy insert begin num: ' + convert(varchar(100),@RowCount)INSERT DistributionPolicy(
DistributionPolicyID,
ContractID,
UseCreditCardInfo)
SELECT DistributionPolicyID=@NewDistributionPolicyID,
ContractID=@NewContractID,
DefaultRestrictionsID,
UseCreditCardInfo
FROM DistributionPolicy WHERE DistributionPolicyID=@DistributionPolicyIDprint 'DistributionPolicy insert End num: ' + convert(varchar(100),@RowCount)--2,Copy DistributionPolicy End
print 'UsedCrditCard insert begin num: ' + convert(varchar(100),@RowCount)INSERT UsedCreditCard(DistributionPolicyID,
CreditCardID)
SELECT DistributionPolicyID=@NewDistributionPolicyID,
CreditCardID
FROM UsedCreditCard WHERE DistributionPolicyID=@DistributionPolicyIDprint 'UsedCrditCard insert End num: ' + convert(varchar(100),@RowCount)FETCH NEXT FROM DistributionPolicy_cursor
INTO @DistributionPolicyIDprint 'Next DistributionPolicyID' + ': ' + convert(varchar(100),@DistributionPolicyID)SET @RowCount=@RowCount + 1ENDCLOSE DistributionPolicy_cursor
DEALLOCATE DistributionPolicy_cursor
-- Copy DistributionPolicy End
COMMIT TRANSACTION
return 0err_handle:
ROLLBACK TRANSACTION
return 1GO2,在NET端的调用是/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="p_ProcedureName">存储过程脕E/param>
/// <param name="p_SqlParameterArray">存储过程参数</param>
public int ExeProcedure(string p_ProcedureName, IDataParameter[] p_SqlParameterArray)
{
int vResult = -1; this.m_SqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = p_ProcedureName;
cmd.Connection = this.m_SqlConn;
cmd.CommandType = CommandType.StoredProcedure;
if (p_SqlParameterArray != null)
{
foreach (SqlParameter Sq in p_SqlParameterArray)
{
cmd.Parameters.Add( Sq);
}
}
try
{
vResult = cmd.ExecuteNonQuery();
}
catch {
}
return vResult;
}public int CopyContractInfo()
{
SqlParameter[] arrParam = new System.Data.SqlClient.SqlParameter[2];
arrParam[0] = new SqlParameter("@ContractID", SqlDbType.UniqueIdentifier);
arrParam[0].Value = new Guid(CONTRACT_ID);
arrParam[0].Direction = ParameterDirection.Input; arrParam[1] = new SqlParameter("@Out_ContractID", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.Output; int intInfo = this.DataAccess.ExeProcedure("CopyContractInfo", arrParam); // (调用上面的ExeProcedure)
string strContractID = arrParam[1].Value.ToString(); return intInfo;
}3,因为在存储过程中要返回一个参数,这个写法是否合适?
arrParam[1] = new SqlParameter("@Out_ContractID", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.Output;
4,vResult = cmd.ExecuteNonQuery();的返回结果是执行 SQL 语句并返回受影响的行数。
我在存储过程中定义的返回值被【受影响的行数】覆盖掉了,我要怎样写调用才能执行我存储过程中的返回值 0或1呢?
CREATE PROC CopyContractInfo @ContractID UNIQUEIDENTIFIER,@Out_ContractID UNIQUEIDENTIFIER OUTPUT
AS-----
COMMIT TRANSACTION
return 0err_handle:
ROLLBACK TRANSACTION
return 1GO谢谢!
AS
DECLARE @NewContractID UNIQUEIDENTIFIER
SET @NewContractID=NEWID()
SET @Out_ContractID=@NewContractIDprint 'New ContractID' + ': ' + convert(varchar(100),@NewContractID)
BEGIN TRANSACTION--1,Copy ContractInfo Begin
INSERT ContractInfo(
ContractID,
ContractName,
ReleaseFlag)
SELECT ContractID=@NewContractID,
ContractName,
ReleaseFlag
FROM ContractInfo where ContractID=@ContractIDif @@error<>0 goto err_handle
--1,Copy ContractInfo End-- Copy DistributionPolicy/UsedCreditCard Begin
DECLARE @DistributionPolicyID UNIQUEIDENTIFIER
DECLARE @RowCount INTDECLARE DistributionPolicy_cursor CURSOR FOR
SELECT DistributionPolicyID FROM DistributionPolicy WHERE ContractID = @ContractID
ORDER BY DistributionPolicyIDOpen DistributionPolicy_cursorFETCH NEXT FROM DistributionPolicy_cursor
INTO @DistributionPolicyIDprint 'OLD 1 DistributionPolicyID' + ': ' + convert(varchar(100),@DistributionPolicyID)
SET @RowCount=0WHILE @@FETCH_STATUS = 0BEGIN
print @RowCountDECLARE @NewDistributionPolicyID UNIQUEIDENTIFIER
SET @NewDistributionPolicyID = NEWID()print 'New DistributionPolicyID' + ': ' + convert(varchar(100),@NewDistributionPolicyID)--2,Copy DistributionPolicy Beginprint 'DistributionPolicy insert begin num: ' + convert(varchar(100),@RowCount)INSERT DistributionPolicy(
DistributionPolicyID,
ContractID,
UseCreditCardInfo)
SELECT DistributionPolicyID=@NewDistributionPolicyID,
ContractID=@NewContractID,
DefaultRestrictionsID,
UseCreditCardInfo
FROM DistributionPolicy WHERE DistributionPolicyID=@DistributionPolicyIDprint 'DistributionPolicy insert End num: ' + convert(varchar(100),@RowCount)--2,Copy DistributionPolicy End
print 'UsedCrditCard insert begin num: ' + convert(varchar(100),@RowCount)INSERT UsedCreditCard(DistributionPolicyID,
CreditCardID)
SELECT DistributionPolicyID=@NewDistributionPolicyID,
CreditCardID
FROM UsedCreditCard WHERE DistributionPolicyID=@DistributionPolicyIDprint 'UsedCrditCard insert End num: ' + convert(varchar(100),@RowCount)FETCH NEXT FROM DistributionPolicy_cursor
INTO @DistributionPolicyIDprint 'Next DistributionPolicyID' + ': ' + convert(varchar(100),@DistributionPolicyID)SET @RowCount=@RowCount + 1ENDCLOSE DistributionPolicy_cursor
DEALLOCATE DistributionPolicy_cursor
-- Copy DistributionPolicy End
COMMIT TRANSACTION
return 0err_handle:
ROLLBACK TRANSACTION
return 1GO2,在NET端的调用是/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="p_ProcedureName">存储过程脕E/param>
/// <param name="p_SqlParameterArray">存储过程参数</param>
public int ExeProcedure(string p_ProcedureName, IDataParameter[] p_SqlParameterArray)
{
int vResult = -1; this.m_SqlConn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = p_ProcedureName;
cmd.Connection = this.m_SqlConn;
cmd.CommandType = CommandType.StoredProcedure;
if (p_SqlParameterArray != null)
{
foreach (SqlParameter Sq in p_SqlParameterArray)
{
cmd.Parameters.Add( Sq);
}
}
try
{
vResult = cmd.ExecuteNonQuery();
}
catch {
}
return vResult;
}public int CopyContractInfo()
{
SqlParameter[] arrParam = new System.Data.SqlClient.SqlParameter[2];
arrParam[0] = new SqlParameter("@ContractID", SqlDbType.UniqueIdentifier);
arrParam[0].Value = new Guid(CONTRACT_ID);
arrParam[0].Direction = ParameterDirection.Input; arrParam[1] = new SqlParameter("@Out_ContractID", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.Output; int intInfo = this.DataAccess.ExeProcedure("CopyContractInfo", arrParam); // (调用上面的ExeProcedure)
string strContractID = arrParam[1].Value.ToString(); return intInfo;
}3,因为在存储过程中要返回一个参数,这个写法是否合适?
arrParam[1] = new SqlParameter("@Out_ContractID", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.Output;
4,vResult = cmd.ExecuteNonQuery();的返回结果是执行 SQL 语句并返回受影响的行数。
我在存储过程中定义的返回值被【受影响的行数】覆盖掉了,我要怎样写调用才能执行我存储过程中的返回值 0或1呢?
CREATE PROC CopyContractInfo @ContractID UNIQUEIDENTIFIER,@Out_ContractID UNIQUEIDENTIFIER OUTPUT
AS-----
COMMIT TRANSACTION
return 0err_handle:
ROLLBACK TRANSACTION
return 1GO谢谢!
string strContractID = arrParam[1].Value.ToString();我要改变返回值不是执行 SQL 语句并返回受影响的行数。因为vResult = cmd.ExecuteNonQuery();我要返回的值是我存储过程中的返回值1和0CREATE PROC CopyContractInfo @ContractID UNIQUEIDENTIFIER,@Out_ContractID UNIQUEIDENTIFIER OUTPUT
AS-----
COMMIT TRANSACTION
return 0err_handle:
ROLLBACK TRANSACTION
return 1GO谢谢
然后执行完成后,读取该参数的值即可。
arrParam[1].Direction = ParameterDirection.ReturnValue;int intInfo = this.DataAccess.ExeProcedure("CopyContractInfo", arrParam); // (调用上面的ExeProcedure)
string strContractID = arrParam[1].Value.ToString();
此时,strContractID就是你要返回的0或者1
To,greennetboy(我的老婆叫静静) 你们误会我得意思了,存储过程中的out的值@Out_ContractID,我也要,返回的存储过程的值0和1我也要,据此我才可以知道存储执行的是否成功,虽然ExecuteNonQuery();执行 SQL 语句并返回受影响的行数,我可以判断是否成功,但是我的意思,就是象在C#中一样,能否也能象OUT参数一样的使用,又能返回执行的retun Value,又把存储中的out的值返回public int GetXXX(out string out_ContractID)
{out_ContractID =BBB;return 0;}
arrParam[1] = new SqlParameter("@ReturnValue", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.ReturnValue;
//输出
arrParam[1] = new SqlParameter("@Out_ContractID", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.Output;int intInfo = this.DataAccess.ExeProcedure("CopyContractInfo", arrParam); // (调用上面的ExeProcedure)
string strContractID = arrParam[1].Value.ToString();
此时,strContractID就是你要返回的0或者1
{
...
arrParam[0].Value = new Guid(CONTRACT_ID);
arrParam[0].Direction = ParameterDirection.Input;
strContractID = arrParam[1].Value.ToString();
...
return intInfo;
}
2,存储过程执行返回影响的行数。
3,返回存储过程的out的参数值。但这里有误:下标错了。
//返回return,@ReturnValue 不用你在存储过程中声明。但必须是@ReturnValue
arrParam[1] = new SqlParameter("@ReturnValue", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.ReturnValue;
//输出
arrParam[2] = new SqlParameter("@Out_ContractID", SqlDbType.UniqueIdentifier);
arrParam[2].Direction = ParameterDirection.Output;
CREATE PROC CopyContractInfo @ContractID UNIQUEIDENTIFIER,
@Out_ContractID UNIQUEIDENTIFIER OUTPUT,
@rtnValue int output
AS-----
COMMIT TRANSACTION
set @rtnValue = 0err_handle:
ROLLBACK TRANSACTION
set @rtnValue = 1然后再在程序中取第二个输出参数.
应该是
但这里有误:下标错了。
//返回return,@@ReturnValue 不用你在存储过程中声明。但必须是@ReturnValue
arrParam[1] = new SqlParameter("@@ReturnValue", SqlDbType.UniqueIdentifier);
arrParam[1].Direction = ParameterDirection.ReturnValue;
//输出
arrParam[2] = new SqlParameter("@Out_ContractID", SqlDbType.UniqueIdentifier);
arrParam[2].Direction = ParameterDirection.Output;