Questions tagged [stored-procedure]

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 was originally implemented in applications.

Filter by
Sorted by
Tagged with
1
vote
1answer
740 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: ...
1
vote
1answer
37 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
158 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
65 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 ...
7
votes
1answer
70 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
69 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
37 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
472 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
130 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
118 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
32k 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
135 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
357 views

Simple Dal for Accessing Procedures

How could I improve this code? ...
3
votes
1answer
65 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
2k 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
96 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
216 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 ...
46
votes
2answers
4k 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 ...
8
votes
1answer
8k 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
6k 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
43k 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? ...
7
votes
2answers
23k 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
3k 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
131 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
7k 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
68 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
142 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
93 views

Selecting and copying event log entries for some machine

I have a PL/pgSQL function with erratic performance: ...

1
2