I have a website where customer order stuff online, in a cart model etc.
我有一个网站,客户在网上订购东西,在购物车模型等。
The problems is when I knew order is recorded there are different information that needs to be updated like entry in order table, deductions from stock table, updating sales table etc. and I currently I am doing this by running each single query one time to the database getting its result modifying as per requirement and then run another query to update the result, as the next query requires information from the previous one:
问题是当我知道订单被记录时,有不同的信息需要更新,如订单表中的条目,库存表中的扣除,更新销售表等。我目前通过运行每个单个查询一次到达数据库根据需要修改结果,然后运行另一个查询来更新结果,因为下一个查询需要来自前一个查询的信息:
bool stts = false;
int re1 = 0, re2 = 0, re3 = 0;
short maxOr = 0, maxCa = 0, maxOc = 0;
SqlConnection conn= Shared.GetSqlCon(); //Make connection object
conn= Shared.GetSqlCon();
var comm1 = new Commmand("SELECT MAX(orId) FROM [order];", sqlCon);
maxOr = Shared.OSC(sqlCon, comm1);
Shared.COC(sqlCon); //Close & Dispose connections
conn= Shared.GetSqlCon();
var comm2 = new Commmand("SELECT MAX(caId) FROM [cart];", sqlCon);
maxCa = Shared.OSC(sqlCon, comm2);
Shared.COC(sqlCon);
conn= Shared.GetSqlCon();
var comm3 = new Commmand("INSERT INTO [order_cart](orId,caId) VALUES(@maxOr,@maxCa);", sqlCon);
comm3.Parameters.AddWithValue("@maxOr", maxOr + 1);
comm3.Parameters.AddWithValue("@maxCa", maxCa + 1);
And of course this in any way is not a great way to do it going back and forth to database again and again and I think going it through SQL Server Stored Procedures
would be a better idea. But even after trying and finding a lot I couldn’t find an example of how can store the result of a query in a SP
variable and use it inside it, somewhat like this:
当然,这在任何方面都不是一次又一次地回到数据库的好方法,我认为通过SQL Server存储过程进行更好的想法。但即使经过尝试和发现很多,我找不到一个如何将查询结果存储在SP变量中并在其中使用它的示例,有点像这样:
Declare @myVar int //Stored Procedure variable
@myVar = SELECT MAX(caId) FROM [cart] //Getting query result in the variable
INSERT INTO [order_cart](orId,caId) VALUES(@maxOr, @myVar); //Updating record through the variable
return @myVar //return variable value to the program
Is this possible to do this? If yes than how please guide.
这可能吗?如果是,请如何指导。
3 个解决方案
#1
23
Yup, this is possible of course. Here are several examples.
是的,这当然是可能的。以下是几个例子。
-- one way to do this
DECLARE @Cnt int
SELECT @Cnt = COUNT(SomeColumn)
FROM TableName
GROUP BY SomeColumn
-- another way to do the same thing
DECLARE @StreetName nvarchar(100)
SET @StreetName = (SELECT Street_Name from Streets where Street_ID = 123)
-- Assign values to several variables at once
DECLARE @val1 nvarchar(20)
DECLARE @val2 int
DECLARE @val3 datetime
DECLARE @val4 uniqueidentifier
DECLARE @val5 double
SELECT @val1 = TextColumn,
@val2 = IntColumn,
@val3 = DateColumn,
@val4 = GuidColumn,
@val5 = DoubleColumn
FROM SomeTable
#2
5
Try this example
试试这个例子
CREATE PROCEDURE MyProc
BEGIN
--Stored Procedure variables
Declare @maxOr int;
Declare @maxCa int;
--Getting query result in the variable (first variant of syntax)
SET @maxOr = (SELECT MAX(orId) FROM [order]);
--Another variant of seting variable from query
SELECT @maxCa=MAX(caId) FROM [cart];
--Updating record through the variable
INSERT INTO [order_cart] (orId,caId)
VALUES(@maxOr, @maxCa);
--return values to the program as dataset
SELECT
@maxOr AS maxOr,
@maxCa AS maxCa
-- return one int value as "return value"
RETURN @maxOr
END
GO
SQL-command to call the stored procedure
用于调用存储过程的SQL命令
EXEC MyProc
#3
0
Or you can use one SQL-command instead of create and call stored procedure
或者您可以使用一个SQL命令而不是create和调用存储过程
INSERT INTO [order_cart](orId,caId)
OUTPUT inserted.*
SELECT
(SELECT MAX(orId) FROM [order]) as orId,
(SELECT MAX(caId) FROM [cart]) as caId;
#1
23
Yup, this is possible of course. Here are several examples.
是的,这当然是可能的。以下是几个例子。
-- one way to do this
DECLARE @Cnt int
SELECT @Cnt = COUNT(SomeColumn)
FROM TableName
GROUP BY SomeColumn
-- another way to do the same thing
DECLARE @StreetName nvarchar(100)
SET @StreetName = (SELECT Street_Name from Streets where Street_ID = 123)
-- Assign values to several variables at once
DECLARE @val1 nvarchar(20)
DECLARE @val2 int
DECLARE @val3 datetime
DECLARE @val4 uniqueidentifier
DECLARE @val5 double
SELECT @val1 = TextColumn,
@val2 = IntColumn,
@val3 = DateColumn,
@val4 = GuidColumn,
@val5 = DoubleColumn
FROM SomeTable
#2
5
Try this example
试试这个例子
CREATE PROCEDURE MyProc
BEGIN
--Stored Procedure variables
Declare @maxOr int;
Declare @maxCa int;
--Getting query result in the variable (first variant of syntax)
SET @maxOr = (SELECT MAX(orId) FROM [order]);
--Another variant of seting variable from query
SELECT @maxCa=MAX(caId) FROM [cart];
--Updating record through the variable
INSERT INTO [order_cart] (orId,caId)
VALUES(@maxOr, @maxCa);
--return values to the program as dataset
SELECT
@maxOr AS maxOr,
@maxCa AS maxCa
-- return one int value as "return value"
RETURN @maxOr
END
GO
SQL-command to call the stored procedure
用于调用存储过程的SQL命令
EXEC MyProc
#3
0
Or you can use one SQL-command instead of create and call stored procedure
或者您可以使用一个SQL命令而不是create和调用存储过程
INSERT INTO [order_cart](orId,caId)
OUTPUT inserted.*
SELECT
(SELECT MAX(orId) FROM [order]) as orId,
(SELECT MAX(caId) FROM [cart]) as caId;