MySQL doesn’t support recursive queries so you have to do it the hard way: Select the rows where ParentID = X where X is your root. Collect the Id values from (1). Repeat (1) for each Id from (2). Keep recursing by hand until you find all the leaf nodes. If you know a maximum … Read more
Needed this answer myself and from the link provided by David Moye, decided on this and thought it might be of use to others with the same question: CREATE PROCEDURE … AS BEGIN BEGIN TRANSACTION — lock table “a” till end of transaction SELECT … FROM a WITH (TABLOCK, HOLDLOCK) WHERE … — do some … Read more
I’ve had a similar issue arise in the past, so I’m eager to see a resolution to this question. Aaron Bertrand’s comment on the OP led to Query times out when executed from web, but super-fast when executed from SSMS, and while the question is not a duplicate, the answer may very well apply to … Read more
To escape special characters in a LIKE expression you prefix them with an escape character. You get to choose which escape char to use with the ESCAPE keyword. (MSDN Ref) For example this escapes the % symbol, using \ as the escape char: select * from table where myfield like ‘%15\% off%’ ESCAPE ‘\’ If … Read more
I now found solution by using mysqli instead of mysql. <?php // enable error reporting mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //connect to database $connection = mysqli_connect(“hostname”, “user”, “password”, “db”, “port”); //run the store proc $result = mysqli_query($connection, “CALL StoreProcName”); //loop the result set while ($row = mysqli_fetch_array($result)){ echo $row[0] . ” – ” . + $row[1]; } … Read more
You can pass a string with your list and use a prepared statements to run a query, e.g. – DELIMITER $$ CREATE PROCEDURE GetFruits(IN fruitArray VARCHAR(255)) BEGIN SET @sql = CONCAT(‘SELECT * FROM Fruits WHERE Name IN (‘, fruitArray, ‘)’); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END $$ DELIMITER ; How to … Read more
If you are using Sql Server 2008 or better, you can use something called a Table-Valued Parameter (TVP) instead of serializing & deserializing your list data every time you want to pass it to a stored procedure. Let’s start by creating a simple schema to serve as our playground: CREATE DATABASE [TestbedDb] GO USE [TestbedDb] … Read more
I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure. I found another answer here “Parameter Sniffing”, Thanks Omnibuzz. Boils down to using “local Variables” in your stored procedure queries, but read the original for … Read more
If you’re using SQL Server 2008, there’s a new featured called a User Defined Table Type. Here is an example of how to use it: Create your User Defined Table Type: CREATE TYPE [dbo].[StringList] AS TABLE( [Item] [NVARCHAR](MAX) NULL ); Next you need to use it properly in your stored procedure: CREATE PROCEDURE [dbo].[sp_UseStringList] @list … Read more
Use the following steps to solve this issue: You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import. In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for … Read more