How do I store a value from a SELECT statement in a variable in a MySQL Stored Procedure?
In a stored procedure do this: SELECT COUNT(barcode) AS count into @myVar FROM movieitems;
sql
Storing item positions (for ordering) in a database efficiently
August 2022: Note that the below is flawed and doesn’t work when moving a movie down the list. I’ve posted a new answer here which fixes this issue. If you use a combination of the position and a timestamp that the user put a movie in a given position rather than trying to maintain the … Read more
Find all Database Objects by Name?
Assuming you have the right permissions: SELECT * FROM yourdatabasename.sys.all_objects WHERE upper(name) like upper(‘my prefix%’) –use UPPER for case-INsensitivity
How do I translate a List into a SqlParameter for a Sql In statement? [duplicate]
You could try something like this: string sql = “SELECT dscr FROM system_settings WHERE setting IN ({0})”; string[] paramArray = settingList.Select((x, i) => “@settings” + i).ToArray(); cmd.CommandText = string.Format(sql, string.Join(“,”, paramArray)); for (int i = 0; i < settingList.Count; ++i) { cmd.Parameters.Add(new SqlParameter(“@settings” + i, settingList[i])); }
Zero SQL deadlock by design – any coding patterns?
Writing deadlock-proof code is really hard. Even when you access the tables in the same order you may still get deadlocks [1]. I wrote a post on my blog that elaborates through some approaches that will help you avoid and resolve deadlock situations. If you want to ensure two statements/transactions will never deadlock you may … Read more
Modify materialized view query
No, you cannot alter the query of a materialized view without dropping it. The CREATE MATERIALIZED VIEW syntax does not support that feature. The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways: To change its storage characteristics To change its refresh method, mode, or … Read more
Declaring and using MySQL varchar variables
This works fine for me using MySQL 5.1.35: DELIMITER $$ DROP PROCEDURE IF EXISTS `example`.`test` $$ CREATE PROCEDURE `example`.`test` () BEGIN DECLARE FOO varchar(7); DECLARE oldFOO varchar(7); SET FOO = ‘138’; SET oldFOO = CONCAT(‘0’, FOO); update mypermits set person = FOO where person = oldFOO; END $$ DELIMITER ; Table: DROP TABLE IF EXISTS … Read more
Select truncated string from Postgres
The n in varchar(n) is the number of characters, not bytes. The manual: SQL defines two primary character types: character varying(n) and character(n), where n is a positive integer. Both of these types can store strings up to n characters (not bytes) in length. Bold emphasis mine. Use left() to “truncate” a string to a … Read more
In MySQL: How to pass a table name as stored procedure and/or function argument?
Prepared statements are what you need. CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40) ) BEGIN SET @t1 =CONCAT(‘SELECT * FROM ‘,tab_name ); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END $$