A stored procedure is a subroutine available to applications that access a relational database system. Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that ...
2
votes
1answer
47 views
Get all company branches using stored procedure
Am creating an application for a transportation company that will store information about their drivers, the cars they drive and to what branches and districts they belong to.
I have a branch table ...
2
votes
1answer
26 views
Stored procedure to swap account holders
I'm doing an exercise, and would like to know if what I'm doing is correct and follows the correct structure for rollback in case a transaction fails. I would like to know as well if my ...
4
votes
0answers
65 views
Stored procedure to insert a new person
I have not written very many stored procedures before, and this is the first one I have written to be used in a real business application to write new data into the database, so I would like to get ...
0
votes
0answers
30 views
MySQL stored procedure to search medical insurance claims
This stored procedure is taking a lot of time to execute. How can I optimize this query?
...
0
votes
0answers
23 views
Using OCI functions
I've never used Oracle with PHP before, what I'm doing is calling a stored procedure that returns a refcursor. Which is also a first. I use frameworks like Yii etc and don't seem to be able to use the ...
3
votes
2answers
73 views
Fetching a number of data tables from a stored procedure
Within a method, this code is calling a SPROC that is doing a 'select *' from 9 different views. This method then goes on to access the returned DataTableCollection ...
1
vote
0answers
57 views
Current .NET session username injection in SQL Server stored procedures
I have a legacy WCF service that is ported from a security model using database logins and roles to a custom security. One effect of this change is that stored procedure are no longer executed using ...
4
votes
1answer
67 views
SQL procedure for Archiving and Deleting
I have a requirement for archiving 5 huge tables from PROD to ARCHIVE server without losing the integrity of the tables.
The query makes use of the Linked Server functionality and current idea is to ...
3
votes
1answer
98 views
Database trigger to monitor insert/update events and update another table
I am working on a MSSQL Database (for a program called Sage 200). There are many tables in the database, however, I want to be notified of changes (new record inserted or existing record updated) via ...
1
vote
1answer
31 views
Stored Procedure enhancement for updating columns in a subset
The purpose of this store procedure is to reach into a large database (test), query for a subset, and update a column value, but with a big constraint - I can only update one row at a time in order to ...
3
votes
1answer
63 views
Stored procedure to query custom data tables as dynamic SQL
There's a lot going on here, but the background for why this is necessary is that there is a set schema, or 'core' set of tables that are prefixed with 'bu', and any core table can have a custom table ...
3
votes
2answers
131 views
Insert or update user data
I inherited an application that uses some stored procedures. Here is a sample of one of the stored procedures used to insert or modify user data.
would like to get the group's opinion on the code. ...
4
votes
1answer
63 views
Stored procedure to classify policies by sales date
Currently this is the store procedure I am working on, it is working according to some testing that was done. I feel that it could be improved though, could use some optimization, feedback or ...
1
vote
0answers
36 views
Stored procedure to compare stored procedure result sets
Originally posted as an answer to a SOF question, this is about a generic procedure that allows back-end developers (e.g. SQL developers) to compare the results sets obtained by running two procedures ...
3
votes
3answers
306 views
SQL Procedure that will calculate the Last Price paid for a certain Part
I am trying to write an SQL Procedure that will calculate the Last Price paid for a certain Part.
...
7
votes
2answers
67 views
Execute every Monday that is not the Monday following last Saturday in month
I think the following is good but wondering if it can be simplified or improved.
My condition for code execution is the following:
It is a Monday
AND
It is not the Monday following the last Saturday ...
4
votes
1answer
83 views
Coin flips and Dice rolls in a stored procedure
I saw a post in chat a few days ago, of what I thought was an interesting (albeit fairly simple) statistical challenge:
By @skiwi:
If I shoot once with 50% chance of target A and 50% of target B, ...
10
votes
2answers
149 views
Executing a stored procedure with optional attributes
Lately, I have been reading Uncle Bob's Clean Code book, and decided to try and follow some of his principles to make a class that was a somewhat confusing static helper class, with an abundance of ...
3
votes
0answers
57 views
T-SQL Pbdfk2 (Rfc2898 SHA2_256) Implementation
Using my previous function as a base I've come up the following:
...
4
votes
0answers
154 views
T-SQL Hmac (Rfc2104 SHA2_256) Implementation
I'm trying to implement an Hmac in T-SQL using this question as an guide. Please critique this:
...
17
votes
2answers
265 views
Find & Delete Duplicate Records on All Tables in current MSSQL Database
The purpose of this code is to search the entire database for duplicate records and produce a script that a user could then run to delete all of the duplicates.
The stored procedure takes the ...
1
vote
1answer
150 views
Oracle query to split 1 row into two where conditions are applicable
The scenario question:
"I'm having difficulties with splitting a single row into two individual ones."
My test schema is:
...
0
votes
1answer
31 views
Create variable and use in one stored procedure
This finds a value via a stored procedure then runs a second process in the same stored procedure rather than calling a second stored procedure.
When a call is 'simple' i.e. not Where x y then... I ...
1
vote
1answer
61 views
Stored procedure where column to ORDER BY depends on input parameters
I'm getting three inputs and based on those three inputs, I'm returning. Input items may vary, so I used CASE WHEN statements here.
Is this code correct? If so, ...
3
votes
2answers
54 views
Updating user log in time while he logs in to the web page
My intention is to store the user's last login date and time when he logs in to the web page. I pass his username and password to the stored procedure to check whether is it correct or not! I believe ...
8
votes
1answer
63 views
Assigning permissions to new role without RBAR
I'm currently faced with a problem in our SQL Server environments. We need to migrate our existing logins, users and roles to a new login, user and role with the same permissions as the old. I've been ...
6
votes
1answer
52 views
Searching school data
I am running a test load using Telerik Test Studio using 100 users at once entering search filters on a page that calls a stored procedure.
My application that calls the stored procedure is an ASP....
3
votes
1answer
32 views
Return most recent non null field values for multiple unknown fields
I am attempting to create a stored procedure that looks at one table and imports changes in that table to another.
This stored procedure is going to be preformed on multiple source tables and will ...
2
votes
0answers
209 views
Function to split a given string according to predefined patterns
Here's a function that splits given string according to predefined patterns. It's part of a trigger function used to populate one table from another. I want to be sure that I'm not making any mistakes....
5
votes
2answers
124 views
Selecting Employee details from a complicated schema
I have a "litte" problem with my stored procedure because I need some values selected at a point in time.
I need to do many select and group by's in my Left outer join which looks and feels like I am ...
3
votes
1answer
99 views
SQL stored procedure for fruit checks with several joins
I developed a SQL stored procedure which works lovely, but when under stress, it takes more than 2 minute to get back to the user. I am providing the stored procedure but would also provide the table ...
6
votes
2answers
25k views
Inserting multiple rows in MySQL
I am building a store. When a user decides to make a purchase I need to store it in the database, but since the purchase might be of more than 1 item I'm wondering which way is the best to insert ...
7
votes
3answers
130 views
Step 2: Creating functions the business tool will use
I've been working on this project and here is my first function for step 2. I feel this is the most important one, as I will carry over recommendations from this ...
0
votes
1answer
290 views
3
votes
1answer
48 views
Optimizing sales report stored procedure
I need to optimize the following stored proc. Please let me know of any techniques or modifications that I can make to optimize this piece of code.
The procedure is for a report that needs to run ...
5
votes
2answers
817 views
General select statement to stored procedures
I'm trying to write a general function to several specific stored procedures, but I'm afraid that it will cause errors that I don't see now. The function is to get the name of the stored procedure as ...
5
votes
1answer
87 views
Selecting template from database using organization ids
This is my first SQL post over here, and in fact, I have more or less the experience of a regular SQL programmer, except that the task at hand this time was way harder than normal.
I am expecting, ...
3
votes
2answers
207 views
Determining if an entity exists in a database via a stored procedure
Here is a short and simple Ajax method that returns "True" or "False" if an entity exists in a database via a stored procedure ...
36
votes
2answers
3k views
Tired of FizzBuzz yet?
There have been many FizzBuzz questions lately, and here is one more! Granted, it is pretty straightforward to do FizzBuzz with SQL using calculations.
So instead, I'm going to do a FizzBuzz which ...
7
votes
1answer
3k views
Dynamically filtering, sorting, and paging using a stored procedure
Is there a better way (I'm pretty sure there is) to create a stored procedure to do dynamic paging, filtering and sorting than the following code?
I'm actually generating this sproc from a database ...
10
votes
3answers
2k views
Speeding up a stored procedure
Is there any way to optimize this stored procedure? Maybe something instead of so many joins? It takes some time to execute. Maybe there are other options that I could look into?
...
3
votes
1answer
23k views
Stored Procedure with many If Else if statement
In this Stored procedure (called by this inventory-checking function), many if else if statement are used. How can it be improved?
...
6
votes
2answers
11k views
Implementing repository pattern and DAL with stored procedures
Using .net 3.5, implementation of the repository pattern along with enterprise library 5 and stored procedures. Did not use EF, stored procedures already available and VS2008 is limited to EF 3/4.
...
3
votes
1answer
810 views
Stored Procedure calculating employee earnings
I have following stored procedure in a C# win forms application which calculates employee earnings based on attendance as follows.
Note that a shift is 12 hours and employees mark attendance for in ...
2
votes
1answer
121 views
Execute edit in a stored procedure based on database value
I have code below which is set to check the date of DateToComplete, and if the date is 2 weeks or more ago, change the status of Complete from 3 to 2.
Is this the ...
7
votes
2answers
3k views
Update column based on input variable in stored procedure
The purpose of the below code is to update a column based on the name of the field name sent from the .NET code. This allows one piece of code to handle multiple rows when a user is only adding/...
0
votes
1answer
67 views
A search for reviews of places
This is the stored procedure I'm using to list some places in my website. How can I optimize it to perform well? Which functions in query is taking much time for its execution?
...
3
votes
2answers
121 views
Searching through tables for a value
I have a PL/SQL stored procedure which searches for a row in 3 different tables (not all columns are the same).
The logic goes as follows:
...
5
votes
1answer
92 views
Selecting and copying event log entries for some machine
I have a PL/pgSQL function with erratic performance:
...
