# Procedural SQL

**URL:** https://www.singlestore.com/forum/c/support/procedural-sql/24.md?page=1

[Latest](https://www.singlestore.com/forum/latest.md) · [Categories](https://www.singlestore.com/forum/categories.md) · [Tags](https://www.singlestore.com/forum/tags.md)

**Page:** 2

---

## [How to ensure strong consistency in MemSQL?](https://www.singlestore.com/forum/t/how-to-ensure-strong-consistency-in-memsql/539)

<div class="topic-metadata">

**Author:** [@alesanco](https://www.singlestore.com/forum/u/alesanco)\
**Replies:** 5\
**Last updated:** [September 25, 2022, 9:36pm UTC](https://www.singlestore.com/forum/t/how-to-ensure-strong-consistency-in-memsql/539 "2022-09-25T21:36:40Z")

</div>

When I work with MySQL databases for example, I get integrity restriction by declaring: SET FOREIGN\_KEY\_CHECKS = 1; Foreign keys, for example CONSTRAINT Key\_DimProduct FOREIGN KEY (Key\_DimProduct) REFERENCES dw.dim\_p…

---

## [What permission is needed to SHOW CREATE PROCEDURE?](https://www.singlestore.com/forum/t/what-permission-is-needed-to-show-create-procedure/1413)

<div class="topic-metadata">

**Author:** [@mgregory](https://www.singlestore.com/forum/u/mgregory)\
**Replies:** 5\
**Last updated:** [September 19, 2022, 4:33pm UTC](https://www.singlestore.com/forum/t/what-permission-is-needed-to-show-create-procedure/1413 "2022-09-19T16:33:27Z")

</div>

I have users who should be able to see the definition of a procedure without the permission to execute it. However, running SHOW CREATE PROCEDURE on v 6.8.9 currently returns an error that “SHOW CREATE FUNC” is denied. …

---

## [Execute immediate into not working](https://www.singlestore.com/forum/t/execute-immediate-into-not-working/4573)

<div class="topic-metadata">

**Author:** [@user11](https://www.singlestore.com/forum/u/user11)\
**Replies:** 1\
**Last updated:** [May 27, 2022, 12:04am UTC](https://www.singlestore.com/forum/t/execute-immediate-into-not-working/4573 "2022-05-27T00:04:53Z")

</div>

Hi I am getting compilation error while running execute immediate into a variable. Code works fine without “into” option but i need the output of the query in a variable to use it further in stored procedure. DB Versi…

---

## [Lead / Lag function Bug?](https://www.singlestore.com/forum/t/lead-lag-function-bug/4919)

<div class="topic-metadata">

**Author:** [@DataSleek](https://www.singlestore.com/forum/u/DataSleek)\
**Replies:** 4\
**Last updated:** [August 26, 2022, 8:25pm UTC](https://www.singlestore.com/forum/t/lead-lag-function-bug/4919 "2022-08-26T20:25:11Z")

</div>

I am using a lead function to check if the price of previous day has changed or not. The price is grouped by event\_date, event\_id, and ticket\_id. The first row is correct, yesterday’s price does not exist, however for …

---

## [Select \<QUERY TYPE Variable\>](https://www.singlestore.com/forum/t/select-query-type-variable/4918)

<div class="topic-metadata">

**Author:** [@prathap\_26](https://www.singlestore.com/forum/u/prathap_26)\
**Replies:** 1\
**Last updated:** [August 26, 2022, 3:43pm UTC](https://www.singlestore.com/forum/t/select-query-type-variable/4918 "2022-08-26T15:43:50Z")

</div>

Hi I have a query on QTV, create or replace procedure test11(ename\_in VARCHAR(55) AS DECLARE q\_eids QUERY(eid INTEGER)=select employee\_id FROM EMPLOYEE where name=ename; /\* alternative way if we wish to go in itera…

---

## [Django & MeMSQL Error when running migrate.py: django.db.utils.OperationalError: (2012, 'Error in server handshake')](https://www.singlestore.com/forum/t/django-memsql-error-when-running-migrate-py-django-db-utils-operationalerror-2012-error-in-server-handshake/4846)

<div class="topic-metadata">

**Author:** [@smartdnshail1](https://www.singlestore.com/forum/u/smartdnshail1)\
**Replies:** 1\
**Last updated:** [August 9, 2022, 8:02pm UTC](https://www.singlestore.com/forum/t/django-memsql-error-when-running-migrate-py-django-db-utils-operationalerror-2012-error-in-server-handshake/4846 "2022-08-09T20:02:41Z")

</div>

I have a Django application with a default MySQL database. I want to move my default Database to MeMSQL. I set the credentials in settings.py to be: DATABASES = { 'default': { 'ENGINE': 'django.db.backends.…

---

## [Avoiding Memory Issues with SQL Alchemy and pandas read\_sql](https://www.singlestore.com/forum/t/avoiding-memory-issues-with-sql-alchemy-and-pandas-read-sql/4847)

<div class="topic-metadata">

**Author:** [@smartdnshail1](https://www.singlestore.com/forum/u/smartdnshail1)\
**Replies:** 2\
**Last updated:** [August 9, 2022, 12:12pm UTC](https://www.singlestore.com/forum/t/avoiding-memory-issues-with-sql-alchemy-and-pandas-read-sql/4847 "2022-08-09T12:12:37Z")

</div>

0 I have a query that obtains all records in a day for certain conditions, and this query is inserted into a python loop to obtain data from my MySQL (memsql I believe) database. I am receiving the following error (mas…

---

## [UPDATE large number of rows in time series data](https://www.singlestore.com/forum/t/update-large-number-of-rows-in-time-series-data/4844)

<div class="topic-metadata">

**Author:** [@smartdnshail1](https://www.singlestore.com/forum/u/smartdnshail1)\
**Replies:** 1\
**Last updated:** [August 8, 2022, 10:19pm UTC](https://www.singlestore.com/forum/t/update-large-number-of-rows-in-time-series-data/4844 "2022-08-08T22:19:02Z")

</div>

UPDATE myTBL SET columnA = columnA/1000, columnB = columnB/1000, columnC = columnC/1000, columnD = columnD/1000, columnE = columnE/1000 WHERE timestamp between '2010-01-01 00:00:00' and '2020-10-07 23:59:59' It is a pro…

---

## [Execute sql insert statements via sql alchemy within for loop](https://www.singlestore.com/forum/t/execute-sql-insert-statements-via-sql-alchemy-within-for-loop/4848)

<div class="topic-metadata">

**Author:** [@smartdnshail1](https://www.singlestore.com/forum/u/smartdnshail1)\
**Replies:** 1\
**Last updated:** [August 8, 2022, 10:12pm UTC](https://www.singlestore.com/forum/t/execute-sql-insert-statements-via-sql-alchemy-within-for-loop/4848 "2022-08-08T22:12:59Z")

</div>

I need to extract a bunch of records within a singlestore database and insert the records into another table. For performance, the ideal way to do this is to create a query string with an Insert Into statement and iterat…

---

## [Null last in order by in Mem SQL](https://www.singlestore.com/forum/t/null-last-in-order-by-in-mem-sql/4849)

<div class="topic-metadata">

**Author:** [@smartdnshail1](https://www.singlestore.com/forum/u/smartdnshail1)\
**Replies:** 1\
**Last updated:** [August 8, 2022, 9:26pm UTC](https://www.singlestore.com/forum/t/null-last-in-order-by-in-mem-sql/4849 "2022-08-08T21:26:33Z")

</div>

Can someone help me how to use Null last in Mem sqlMEM, in RDBMS we have option for null last but in Mem SQL it does not supports

---

## [How to reset the auto\_increment vlaue of a column whenever we truncate table](https://www.singlestore.com/forum/t/how-to-reset-the-auto-increment-vlaue-of-a-column-whenever-we-truncate-table/1899)

<div class="topic-metadata">

**Author:** [@rjsv](https://www.singlestore.com/forum/u/rjsv)\
**Replies:** 3\
**Last updated:** [August 5, 2022, 11:56pm UTC](https://www.singlestore.com/forum/t/how-to-reset-the-auto-increment-vlaue-of-a-column-whenever-we-truncate-table/1899 "2022-08-05T23:56:59Z")

</div>

We have a table as below. While deleting the records,we will capture the records into this table. To generate the id value we are using ‘auto\_increment’ column. We will truncate this table weekly once. How to reset the a…

---

## [Execute Immediate a SP/Func inside SP from a query table](https://www.singlestore.com/forum/t/execute-immediate-a-sp-func-inside-sp-from-a-query-table/4692)

<div class="topic-metadata">

**Author:** [@missgeekcode](https://www.singlestore.com/forum/u/missgeekcode)\
**Replies:** 3\
**Last updated:** [July 14, 2022, 7:11pm UTC](https://www.singlestore.com/forum/t/execute-immediate-a-sp-func-inside-sp-from-a-query-table/4692 "2022-07-14T19:11:52Z")

</div>

Hello, I’m having trouble to execute immediate a SP inside a SP from a table. because i have a lot of SP stored in a table (as a data/string). in oracle we can use “select sp\_1(:1,:2) from dual” and running it via ex…

---

## [Execute multiple queries from file or directly in the SQL editor](https://www.singlestore.com/forum/t/execute-multiple-queries-from-file-or-directly-in-the-sql-editor/4669)

<div class="topic-metadata">

**Author:** [@pawanbag](https://www.singlestore.com/forum/u/pawanbag)\
**Replies:** 2\
**Last updated:** [June 14, 2022, 12:58pm UTC](https://www.singlestore.com/forum/t/execute-multiple-queries-from-file-or-directly-in-the-sql-editor/4669 "2022-06-14T12:58:35Z")

</div>

Hi, I am new to MemSQL. I want to execute multiple statement (around 50-60) at one go. how should I do it? I know it is possible in other databases like Oracle, SQL Server using go function. I have wrote queries like…

---

## [Expression in an Update or Select Query not working](https://www.singlestore.com/forum/t/expression-in-an-update-or-select-query-not-working/4529)

<div class="topic-metadata">

**Author:** [@manoj](https://www.singlestore.com/forum/u/manoj)\
**Replies:** 3\
**Last updated:** [June 2, 2022, 10:03pm UTC](https://www.singlestore.com/forum/t/expression-in-an-update-or-select-query-not-working/4529 "2022-06-02T22:03:02Z")

</div>

I maybe missing somethin simple to explain what is going on. I am trying to get another problem solved and I encountered the following issue that is very different from other DB’s I use. I have the following query: 1. …

---

## [Select rows only from specific partititons](https://www.singlestore.com/forum/t/select-rows-only-from-specific-partititons/4552)

<div class="topic-metadata">

**Author:** [@Benzion](https://www.singlestore.com/forum/u/Benzion)\
**Replies:** 1\
**Last updated:** [May 25, 2022, 5:45pm UTC](https://www.singlestore.com/forum/t/select-rows-only-from-specific-partititons/4552 "2022-05-25T17:45:26Z")

</div>

Hi, I have a table ABC. It has a shard based on one of the fields. I want to select rows only from specific partition. Something like: select \* from ABC partition 10 where some\_id=‘6’ Please advise.

---

## [Nested transaction](https://www.singlestore.com/forum/t/nested-transaction/3176)

<div class="topic-metadata">

**Author:** [@sid2sarkar](https://www.singlestore.com/forum/u/sid2sarkar)\
**Replies:** 4\
**Last updated:** [May 17, 2022, 2:56am UTC](https://www.singlestore.com/forum/t/nested-transaction/3176 "2022-05-17T02:56:59Z")

</div>

Does memsql support nested transactions? I wish I could cut-paste my test from my office login. Anyway, I am using 7.14 create table sid\_tran ( col1 int primary key, col2 timestamp(6) not null, tran\_cnt smallint n…

---

## [Locking Mechanism? LOAD DATA INFILE](https://www.singlestore.com/forum/t/locking-mechanism-load-data-infile/4181)

<div class="topic-metadata">

**Author:** [@hiteshupreti51](https://www.singlestore.com/forum/u/hiteshupreti51)\
**Replies:** 2\
**Last updated:** [April 29, 2022, 2:25pm UTC](https://www.singlestore.com/forum/t/locking-mechanism-load-data-infile/4181 "2022-04-29T14:25:03Z")

</div>

Hey folks, Pls help me to understand, is there any locking happens when we use LOAD DATA LOCAL INFILE F1 INTO TABLE T1. If there is any locking? then what kind of locking it is table level or row level. And what happe…

---

## [Having Updatable View](https://www.singlestore.com/forum/t/having-updatable-view/4429)

<div class="topic-metadata">

**Author:** [@sid2sarkar](https://www.singlestore.com/forum/u/sid2sarkar)\
**Replies:** 2\
**Last updated:** [April 27, 2022, 3:30pm UTC](https://www.singlestore.com/forum/t/having-updatable-view/4429 "2022-04-27T15:30:08Z")

</div>

We want to replicate our SingleStore database using SingleStore internal replicate database feature only. The issue we have is that there are few tables that are very volatile where data are being inserted, updated and…

---

## [Temporary table in WITH Clause (CTE) is not working](https://www.singlestore.com/forum/t/temporary-table-in-with-clause-cte-is-not-working/4106)

<div class="topic-metadata">

**Author:** [@ganesh.usi.gk](https://www.singlestore.com/forum/u/ganesh.usi.gk)\
**Replies:** 3\
**Last updated:** [April 11, 2022, 10:55am UTC](https://www.singlestore.com/forum/t/temporary-table-in-with-clause-cte-is-not-working/4106 "2022-04-11T10:55:59Z")

</div>

Hi Team, In Procedure, I have created Temporary Table and am trying to use that temporary table in WITH clause (CTE). But am getting the below error. Error Code: 2242 Unhandled exception Type: ER\_USER\_RAISE (2242) Me…

---

## [Singlestore - External function call for the incoming query](https://www.singlestore.com/forum/t/singlestore-external-function-call-for-the-incoming-query/4310)

<div class="topic-metadata">

**Author:** [@baskar.sks](https://www.singlestore.com/forum/u/baskar.sks)\
**Replies:** 1\
**Last updated:** [March 29, 2022, 1:02am UTC](https://www.singlestore.com/forum/t/singlestore-external-function-call-for-the-incoming-query/4310 "2022-03-29T01:02:33Z")

</div>

Is it possible to invoke an external function once for certain queries being fired from external client? I have a table called test with 2 columns which are encrypted using AES\_encrypt. But, the encryption key being use…

---

## [Aes\_encrypt and aes\_decrypt](https://www.singlestore.com/forum/t/aes-encrypt-and-aes-decrypt/4280)

<div class="topic-metadata">

**Author:** [@baskar.sks](https://www.singlestore.com/forum/u/baskar.sks)\
**Replies:** 1\
**Last updated:** [March 25, 2022, 2:46pm UTC](https://www.singlestore.com/forum/t/aes-encrypt-and-aes-decrypt/4280 "2022-03-25T14:46:39Z")

</div>

select hex(AES\_ENCRYPT(‘secret message’, ‘A1A2A3A4A5A6CAFE’)); It returns → 71990030B38EB0626A4F56B32E103A8F SELECT AES\_DECRYPT(unhex(‘71990030B38EB0626A4F56B32E103A8F’), ‘A1A2A3A4A5A6CAFE’); It returns → 73 65 63 …

---

## [Invoking REST service from Singlestore](https://www.singlestore.com/forum/t/invoking-rest-service-from-singlestore/4174)

<div class="topic-metadata">

**Author:** [@baskar.sks](https://www.singlestore.com/forum/u/baskar.sks)\
**Replies:** 1\
**Last updated:** [March 19, 2022, 4:09am UTC](https://www.singlestore.com/forum/t/invoking-rest-service-from-singlestore/4174 "2022-03-19T04:09:31Z")

</div>

Hi, I am using AES\_Encrypt(,\<Encryption\_Key\>) & AES\_Decrypt(,\<Encryption\_Key\>) within Singlestore storedproc and in the SQL query. We are required to encrypt the key using some other service and call the service during…

---

## [Commit intermediate queries without committing the entire procedure](https://www.singlestore.com/forum/t/commit-intermediate-queries-without-committing-the-entire-procedure/3994)

<div class="topic-metadata">

**Author:** [@Himanshu](https://www.singlestore.com/forum/u/Himanshu)\
**Replies:** 2\
**Last updated:** [February 23, 2022, 11:26pm UTC](https://www.singlestore.com/forum/t/commit-intermediate-queries-without-committing-the-entire-procedure/3994 "2022-02-23T23:26:47Z")

</div>

Hi All, @hanson I want to commit a transaction without committing the main block because main block may have several validations and based on the validation result, we may want to rollback the main block. However we st…

---

## [External function](https://www.singlestore.com/forum/t/external-function/3866)

<div class="topic-metadata">

**Author:** [@ira](https://www.singlestore.com/forum/u/ira)\
**Replies:** 3\
**Last updated:** [February 23, 2022, 1:34pm UTC](https://www.singlestore.com/forum/t/external-function/3866 "2022-02-23T13:34:45Z")

</div>

Hi Guys, I created microservice to select data from PostgreSQL db. This microservice receives SQL query as input parameter with name sqlquery and returns json. It works e.g. see: curl --location --request POST “http:…

---

## [Get\_lock alternative](https://www.singlestore.com/forum/t/get-lock-alternative/3766)

<div class="topic-metadata">

**Author:** [@stefandevo](https://www.singlestore.com/forum/u/stefandevo)\
**Replies:** 3\
**Last updated:** [February 21, 2022, 1:27pm UTC](https://www.singlestore.com/forum/t/get-lock-alternative/3766 "2022-02-21T13:27:27Z")

</div>

I understand from Unsupported MySQL Features · SingleStore Documentation that get\_lock is not supported. I am using a migration tool that is explicit calling this MySql function to migrate tables in ‘cluster mode’; mean…

---

## [Insert Ignore into table takes a long time](https://www.singlestore.com/forum/t/insert-ignore-into-table-takes-a-long-time/3545)

<div class="topic-metadata">

**Author:** [@DataSleek](https://www.singlestore.com/forum/u/DataSleek)\
**Replies:** 5\
**Last updated:** [December 21, 2021, 6:43am UTC](https://www.singlestore.com/forum/t/insert-ignore-into-table-takes-a-long-time/3545 "2021-12-21T06:43:24Z")

</div>

Hi, It takes more than 2 min to insert 200k rows from the query below. When it’s less than 100k rows, it’s much faster. Insert Ignore into table (col1, col2, col3 .. col8 ) Select col1, col2, col3 .. col8 FROM anot…

---

## [Replica read server temporary table alternative](https://www.singlestore.com/forum/t/replica-read-server-temporary-table-alternative/3825)

<div class="topic-metadata">

**Author:** [@vntkhatri](https://www.singlestore.com/forum/u/vntkhatri)\
**Replies:** 1\
**Last updated:** [February 5, 2022, 1:00am UTC](https://www.singlestore.com/forum/t/replica-read-server-temporary-table-alternative/3825 "2022-02-05T01:00:40Z")

</div>

Hi Team, we are using temp table in procedure and we have to run that get data procedure which has temp table and have to run and read data directly from replica read server but its failing with error like ‘Creating t…

---

## [SELECT INTO KAFKA - support for populating kafka key](https://www.singlestore.com/forum/t/select-into-kafka-support-for-populating-kafka-key/3773)

<div class="topic-metadata">

**Author:** [@Pini\_Faran](https://www.singlestore.com/forum/u/Pini_Faran)\
**Replies:** 2\
**Last updated:** [January 27, 2022, 12:44pm UTC](https://www.singlestore.com/forum/t/select-into-kafka-support-for-populating-kafka-key/3773 "2022-01-27T12:44:39Z")

</div>

Hi, I tested the SELECT INTO KAFKA sql statement and it seems the integration with confluent kafka is working ok using: SELECT to\_json(telemtry\_test.\*) FROM telemtry\_test INTO KAFKA ‘\[broker-host-and-port\]/\[topic-nam…

---

## [Problem with external function](https://www.singlestore.com/forum/t/problem-with-external-function/3597)

<div class="topic-metadata">

**Author:** [@irinafikh](https://www.singlestore.com/forum/u/irinafikh)\
**Replies:** 2\
**Last updated:** [December 12, 2021, 9:34am UTC](https://www.singlestore.com/forum/t/problem-with-external-function/3597 "2021-12-12T09:34:19Z")

</div>

Hi All, I try this: CREATE LINK HTTP\_link AS HTTP CREDENTIALS ‘{“headers”:{}}’ CONFIG ‘{“headers”:{“x-rapidapi-host”: “community-open-weather-map.p.rapidapi.com”, “x-rapidapi-key”: “09e1708b03msh5f6f7593db163acp1683f…

---

## [Executing CURRENT\_USER() from stored procedure](https://www.singlestore.com/forum/t/executing-current-user-from-stored-procedure/3539)

<div class="topic-metadata">

**Author:** [@sid2sarkar](https://www.singlestore.com/forum/u/sid2sarkar)\
**Replies:** 9\
**Last updated:** [November 19, 2021, 6:57pm UTC](https://www.singlestore.com/forum/t/executing-current-user-from-stored-procedure/3539 "2021-11-19T18:57:00Z")

</div>

Hello, In my stored procedure, I am using current\_user() while inserting data into a table like the following. INSERT INTO myTable select current\_user(), list of other columns from anotherTable where condition; Wh…

[Previous page](https://www.singlestore.com/forum/c/support/procedural-sql/24.md)

[Next page](https://www.singlestore.com/forum/c/support/procedural-sql/24.md?page=2)
