I have 1.5 million XML documents stored in a SQL Server 2019 database and I need to have a where clause that has multiple nodes in a stored procedure. I need to pull all XML files where PI_ID equals 9983950. The number of PI’s in the PIS node could be one or 5. I’m using this code: when I need
Tag: stored-procedures
How to not retrieve duplicate row due to stored procedure
I’ve been using my stored procedure to retrieve events filter by : a date start, a date end, a shopId, a sellerId and customerId. First of all, here the principal structure of my database which causes me the problem : Ticket: Id Reference .. .. 15222 BOOKING56654 .. .. Seller : Id Name .. .. 41 Caty .. …
Showing null instead of throwing a zero division error by try-catch
Why does this procedure return NULL when count and sum are both 0? and does not produce output like this? How to change the first procedure to act same as the second one when there is nothing in the table, meaning when sum and count are both 0? Thanks. Answer If there is nothing in the table then you have
Is start transaction always needed with Mariadb storedprocedure?
I have a mariadb database and a stored procedure. If a select statement is true then a delete query is done. Like this: My question is, do I need to place the start transaction at the beginning before the select statement or is the following possible? This second code doesn’t require a rollback and only…
Stored Procedure not working with variables
I have a stored procedure as follows: When I run the above by passing the search variable as 04029125070527 I get no results. But if I edit my stored procedure as follows, I get a result. The only difference is in the above I’m trying to send the variable and in the second its hard coded (not what I wan…
How do I write a condition within an SQL Select statement
I have a rather long SQL query to pull some values out my database written inside a stored procedure. I am passing an IN parameter called pupiltype which will either =0 or =1. If the pupiltype value=1, I need the line a line in my query to be: but if the pupiltype value is 0, it needs to be: I
How to create a stored procedure that will update a table
I am trying to create a stored procedure that will update the project status from ‘D’ to ‘C’. As you can see below I do not know what I am doing. Please do not suggest using a trigger as that does not pertain to the question. These are the errors I get when I run this code block: Error…
Get Current date, current month, current year depending on user selection – SQL stored procedure
I have a stored procedure that gives results depending on multiple values from the User(filtering), in my query I have 2 date columns date_start and date_end, I want to allow the user to return data depending on his selection from 2dropdownmenu one for start date and the other for end date as this: (this day …
Postgres stored procedure(function) confusion
I’m pretty new to Postgres and SQL as a whole and could use a bit of help with a function here. I have this table: What i need is for a function to take as input plate, start date and end date and throw an error if the table contains any row with the same plate where the rental period
Divide by zero error encountered error. Null value is eliminated by an aggregate or other SET operation
In this specific part of a SP I’m trying to create, I’m getting an error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Divide by zero error encountered. Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Warning: Null value is eliminated by an aggr…