# Create Stored Procedures

This part of the tutorial shows how to create stored procedures that are used to load data using pipelines.

> **📝 Note**: The **SQL Editor** only runs the queries that you select, so ensure you have them all selected before selecting **Run**.

Run the following SQL commands to create the stored procedures:

```sql
USE trades;

DELIMITER //

CREATE OR REPLACE PROCEDURE iter_stocks(iterations int(11) NULL) RETURNS void AS
    DECLARE
         tickers ARRAY(CHAR(5));
         prices ARRAY(DECIMAL(18,4));
         last_ids ARRAY(bigINT);
         counts ARRAY(INT);
         next_id bigINT = 1;
         ticker CHAR(5);
         price DECIMAL(18,4);
         c INT;
         rand DECIMAL(18,4);
         tickers_q QUERY(t CHAR(5), p DECIMAL(18,4), lid BIGINT, c INT) = SELECT stock_symbol, share_price, MIN(id), COUNT(*) FROM trade GROUP BY stock_symbol;
         q ARRAY(RECORD(t CHAR(5), p DECIMAL(18,4), lid bigINT, c INT));
         q_count QUERY(c INT) = SELECT COUNT(*) FROM trade;
         total_c INT;
    BEGIN
         q = COLLECT(tickers_q);
         tickers = CREATE_ARRAY(LENGTH(q));
         prices = CREATE_ARRAY(LENGTH(q));
         last_ids = CREATE_ARRAY(LENGTH(q));
         counts = CREATE_ARRAY(LENGTH(q));
         total_c = SCALAR(q_count);
         FOR r IN 0..LENGTH(q)-1 LOOP
              tickers[r] = q[r].t;
              prices[r] = q[r].p;
              last_ids[r] = q[r].lid;
              counts[r] = q[r].c;
         END LOOP;
         FOR j IN 0..(iterations-1) LOOP
              FOR i IN 0..LENGTH(tickers)-1 LOOP
                   ticker = tickers[i];
                   price = prices[i];
                   next_id = last_ids[i];
                   c = counts[i];
                   rand = POW(-1, FLOOR(RAND()*2)) * RAND();
                   INSERT INTO trade
                   SELECT id + total_c, stock_symbol, shares, share_price + rand, trade_time FROM trade WHERE stock_symbol = ticker AND id >= next_id;
                   prices[i] = price + rand;
                   last_ids[i] = next_id + total_c;
              END LOOP;
         END LOOP;
    END //

CREATE OR REPLACE PROCEDURE seed_trades(num_trades int(11) NULL) RETURNS int(11) NULL AS
    DECLARE
         ranked_companies ARRAY(RECORD(symbol CHAR(5), _rank INT));
    DECLARE
         q QUERY(symbol CHAR(5), _rank INT) =
              SELECT symbol, rank() OVER (ORDER BY marketcap_to_DECIMAL(market_cap)) AS _rank
              FROM company
              WHERE LENGTH(symbol) < 5
              ORDER BY _rank DESC LIMIT 200;
         i INT = 0;
         rank_num INT;
         next_id INT = 1;
         sym CHAR(5);
         price_base DECIMAL(18,4);
         current_prices ARRAY(INT);
         l ARRAY(RECORD(symbol CHAR(5), _rank INT));
    BEGIN
         l = collect(q);
         FOR r IN l LOOP
              i += 1;
              rank_num = r._rank;
              sym = r.symbol;
              price_base = FLOOR(rand() * 50) + 50;
              FOR j IN 1..((rank_num / 10) + RAND() * 10) LOOP
                   INSERT trade VALUES(
                        next_id,
                        sym,
                        FLOOR(1 + RAND() * 10) * 100,
                        price_base,
                        DATE_ADD(NOW(), INTERVAL RAND() * 6 HOUR));
                   next_id += 1;
                   IF next_id > num_trades THEN RETURN(next_id); END IF;
              END LOOP;
         END LOOP;
         RETURN(next_id);
    END //


DELIMITER ;

```

***

Modified at: October 10, 2024

Source: [/db/v9.1/introduction/sample-data/load-stock-trading-data-into-singlestore/create-stored-procedures/](https://docs.singlestore.com/db/v9.1/introduction/sample-data/load-stock-trading-data-into-singlestore/create-stored-procedures/)

(An index of the documentation is available at /llms.txt)