I have a stored procedure where I want to check multiple IF condtions. SO I wrote the below query. But it’s not compiling and giving error as Error(97,5): PLS-00103: Encountered the symbol "IF" when expecting one of the following: ; The symbol "IF" was ignored. PROCEDURE GET_SPAN_BY_MZ ( PUSERTYPE IN NVARCHAR2, POPERATIONTYPE IN NVARCHAR2, PSPANTYPE… Read More Multiple IF conditions is not executing procedure in Oracle
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 enough… Read More SQL stored procedures use condition from one table
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 = spsName;… Read More MySQL stored function with nested IF… ELSE IF… END IF
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 TABLE(… Read More How to create a table variable in a procedure that inherits a type that has already been created previously on postgresql?
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, TicketId,… Read More I want to get rid of using one table by adding code as a subselect to the UNION
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 SELECT,… Read More MySQL 5.6 – User cannot view stored procedure code
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 can… Read More Can not find the Syntax 1064 Error in my store procedure using MySQL
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; OPEN… Read More Mysql Stored Procedure – No rows fetched
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 between… Read More Difference between 2 times in sec in Pl/SQL
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