Advertisements For this stored procedure, how to add a condition for value Location? If tblEmployees.Office = USA and tblEmployees.state = West then Location = CA, else Location = others: CREATE PROCEDURE spEmployee AS BEGIN SELECT EmployeeId, Name, Gender, DepartmentName, Location FROM tblEmployees INNER JOIN tblDepartments ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId END >Solution : That’s a simple… Read More SQL stored procedures use condition from one table

Advertisements On my database MySQL 8.0.12 version I have this stored procedure DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`( IN spsID VARCHAR(255), IN spsName VARCHAR(255)) BEGIN DECLARE m LONGTEXT; FLUSH HOSTS; IF spsID <> "-" THEN SET spsID = spsID; SET m = CONCAT(‘AND sID = \”,spsID,’\”); ELSE IF spsName <> "-" THEN SET spsName =… Read More MySQL stored function with nested IF… ELSE IF… END IF

Advertisements I am doing a migration exercise from SQL Server to Postgres and I found this type of variable in a stored procedure: CREATE PROCEDURE [dbo].[CambiodedepositanteTIDIS_CERTS] @Client(50), @Email varchar(50), @Planilla1 [dbo].[TipoPlanilla1] Readonly In the variable planila1 , the type TipoPanilla1 is being inherited, if I understood correctly, this is the type: CREATE TYPE [dbo].[TipoPlanilla1] AS… Read More How to create a table variable in a procedure that inherits a type that has already been created previously on postgresql?

Advertisements I have this working Union INSERT INTO [Transform].PosSalesUnion (DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber, poolID) SELECT poolID, DeviceId, DayClosureId, TransactionId, IndetId, TicketId, CategoryId, ArticleId, CurrencyId, Datetime, IsSuccessful, Quantity, Turnover, TurnoverTarget, SalesTransactionTypeId, ValidFrom, ValidTo, SerialNumber FROM [Transform].Devices_DeviceGroups_PosSales UNION SELECT NULL, DeviceId, DayClosureId, TransactionId, IndetId,… Read More I want to get rid of using one table by adding code as a subselect to the UNION

Advertisements We created a user/login for some devs, and we have an issue where this user/login cannot view stored procedure code. This is for MySQL 5.6. I checked the GRANTS and also the information_schema (schema_privileges) and things look "good" to me. Here are the commands I used to GRANT the database access and privileges: GRANT… Read More MySQL 5.6 – User cannot view stored procedure code

Advertisements I am creating a SP so that I can choose the field of a Table im interested in and a second parameter which is "ASC" or "DESC" and de SP will order the table by that field and also will order it ASC or DESC depending on what the second parameter is. But I… Read More Can not find the Syntax 1064 Error in my store procedure using MySQL

Advertisements I’m new to Mysql Stored Procedures. Tring to return rows in a stored procedure after a LOOP. Here’s my code BEGIN DECLARE date_SD date; DECLARE c_stack CURSOR FOR select SD from t4 where date(SD) >= "2022-05-01" and date(SD)<= "2022-05-30" group by SD; DROP TEMPORARY TABLE IF EXISTS final_result; CREATE TEMPORARY TABLE final_result LIKE templaedb.temp_table;… Read More Mysql Stored Procedure – No rows fetched

Advertisements I need some help to identify difference between 2 times in sec variable : current_time : this is current time check_begin_time : in YYYYMMDDHH24MMSS format I need to check the difference i.e. current time – check_begin_time in secs Thanks. >Solution : As difference of two DATE datatype values in Oracle represents number of days… Read More Difference between 2 times in sec in Pl/SQL

Advertisements As the title mention, i have a problem to apply simple filter on a query result using the operator (IN) in a MySQL Stored Procedured. The simple example of the Stored Procedured looking like this DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_example`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_example`( `filter_uid_value` TEXT ) BEGIN SELECT a.id , a.name… Read More MySQL Stored Procedured apply filter using IN and the filter value is coming from a parameter