在插入语句中使用存储过程 [英] using Stored procedure in insert statement

问题描述

我遇到一个问题....我编写了如下程序，需要在插入语句中调用它...

创建过程P_NextSequenceNo
AS
设置NOCOUNT ON
宣告@iReturn int
开始交易
SELECT @iReturn =来自dbo.EMPSEQUENCE
的序列 UPDATE dbo.EMPSEQUENCE SET Sequence =(Sequence + 1)
提交交易
SELECT @iReturn
返回@iReturn

然后，我必须在插入语句下面的SeqNoKey列中调用该函数.

插入EmpSequence(发票编号，列1，列2，列3)
选择``SeqNoKey''，b.value3，0，a.value1，c.value4
来自表1 a，
在a.value1 = b.value3上加入table3 b
并在c.value4 = a.value1

am having one issue.... i write a procedure like below, and need to call it in insert statement...

CREATE PROCEDURE P_NextSequenceNo
AS
SET NOCOUNT ON
DECLARE @iReturn int
BEGIN TRANSACTION
SELECT @iReturn = Sequence FROM dbo.EMPSEQUENCE
UPDATE dbo.EMPSEQUENCE SET Sequence = ( Sequence + 1 )
COMMIT TRANSACTION
SELECT @iReturn
RETURN @iReturn

And after that i have to call that function into below Insert Statement in place of SeqNoKey column.

Insert Into EmpSequence (InvoiceNo, column1, column2, column3)
select ''SeqNoKey '', b.value3, 0, a.value1, c.value4
from table1 a,
join table3 b on a.value1=b.value3
and join table2 c on c.value4 = a.value1

推荐答案

上加入table2 c即使您的问题是空白:但我仍然要问两个问题.
1-您想知道如何在Sql Server中编写存储过程.
2-您知道上述问题的答案，但想知道如何在C#应用程序中使用该存储过程.

很有可能是第二种情况(尽管您没有提到它，但实际上您提到了NOTHING :()).在这种情况下，这可以作为解决方案

Even though you question is blank: yet there are two question I have to ask.
1 - You wanna know how to write Stored Procedure in Sql Server.
2 - You know the answer for above question but wanna know how to use that stored procedure in C# application.

Most probably its the second case (though you did not mention it, actually you mentioed NOTHING :() ). In that case here can be the solution

public void InsertRecord(int StudentId, string StudentName)
{
  //Sql Connection Object
  SqlConnection conn = new SqlConnection("your connection string");

  //Sql Command Object
  SqlCommand command = new SqlCommand("InsertSpName", conn);
  command.CommandType = CommandType.StoredProcedure;

  //now execute the stored procedure
  command.ExecuteNonQuery();
}

请记住，这只是一个想法，您将如何做事情.不要在此代码中指出问题:)

Remember this is just an idea how you would do things. Don''t pin point the problems in THIS code :)

这篇关于在插入语句中使用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！