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

Question

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.

Will it help for the beginning of findings? http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp — Serg, Jul 19 '13 at 06:55

score 34 · Answer 1 · answered Jul 19 '13 at 09:43

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

score 10 · Answer 2 · answered Jul 19 '13 at 07:43

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

score 0 · Answer 3 · answered Jul 19 '13 at 07:52

0

Or you can use one SQL-command instead of create and call stored procedure

INSERT INTO [order_cart](orId,caId)
OUTPUT inserted.*
SELECT
   (SELECT MAX(orId) FROM [order]) as orId,
   (SELECT MAX(caId) FROM [cart]) as caId;

answered Jul 19 '13 at 07:52

Alexander Sigachov

1,541
11
16

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

3 Answers3