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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Storing query results into a variable and modifying it inside a Stored Procedure

Examples related to sql-server

Examples related to asp.net-mvc

Examples related to sql-server-2008

Examples related to stored-procedures