Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing query results into a variable and modifying it inside a Stored Procedure

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:

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.

like image 378
Maven Avatar asked Jul 19 '13 06:07

Maven


People also ask

How do you store the result of SQL query in a variable in stored procedure?

This provides a way to save a result returned from one query, then refer to it later in other queries. The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving.

How do you store a return value of a stored procedure in a variable?

You can use the return statement inside a stored procedure to return an integer status code (and only of integer type). By convention a return value of zero is used for success. If no return is explicitly set, then the stored procedure returns zero. You should use the return value for status codes only.

How do you store SQL query results in a variable in Python?

First of all, c. fetchall() retrieves ALL the results from your query, we'll put them in a variable called rows . Then we create a iterator (the thing you tried to do with the while loop) by doing for row in rows . Then we simply print each row.

How do you assign a value to a variable in SQL stored procedure?

Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.


2 Answers

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 
like image 95
Joel Cuff Avatar answered Oct 10 '22 18:10

Joel Cuff


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

EXEC MyProc 
like image 29
Alexander Sigachov Avatar answered Oct 10 '22 20:10

Alexander Sigachov