0
votes
1answer
41 views

Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again

I have a stored procedure that returns a lot of data for a business dashboard. On first load it takes 18 seconds, after that it takes 0 seconds, even if I change the input parameters at random it ...
2
votes
3answers
208 views

Run multiple stored procedures in sequence

I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for ...
-1
votes
0answers
16 views

I'm trying to make a stored procedure to validate the input parameter [duplicate]

I want to make a stored procedure to validate if a input integer is maximum 15 digits and first digit is not 0. And I have this for now: CREATE PROCEDURE usp_verify_phone_numbers (@phone_number INT) ...
0
votes
2answers
44 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
-1
votes
1answer
34 views

Issues with procedures, setting multiple values [closed]

I am trying to set multiple values but I am only able to set one value. This code fails: alter procedure modifyvalue @ID int, @flight varchar(6), @destination char(10) as begin update ...
1
vote
1answer
51 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
5
votes
1answer
289 views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...
1
vote
2answers
60 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
0
votes
0answers
49 views

set error message on out variable in mysql stored procedure

I have a stored procedure: fill_DATA (serial varchar(20), OUT oError) I am doing an Insert operation here. so what I want is to SET value of oError for any error message in case the query fails to ...
1
vote
0answers
64 views

Stored procedure taking much longer than query [closed]

I have a simple code segment that runs in about 1 second when run alone, but more than 5 minutes when deployed as a stored procedure. I'm using sql server 2008. Unfortunately, I can't post the code ...
0
votes
1answer
65 views

Create SP for inserting data from a table to a table

I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the ...
2
votes
1answer
116 views

Query returning unused stored procedures

When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries. When I run the following query, the results show the ...
1
vote
0answers
131 views

Storing changes to stored procedure and triggers in PostgreSQL

I want a mechanism where I can store/track changes in stored procedure and triggers. I want to track these logs in some table. What is the best possible way ?
0
votes
1answer
90 views

Table Valued Parameter not pulling the correct data for my sp?

http://sqlfiddle.com/#!3/b2643/5/1 http://sqlfiddle.com/#!3/b2643/5/0 This link above has the sample database tables I created on SQLfiddle. I have 3 main tables in my stored procedure: ...
1
vote
1answer
100 views

Maintenance plan using external tool to execute query and stored procedures giving off no results

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ ...
-1
votes
1answer
123 views

Access table field on creating a trigger postgresql

How can I access table field on creating a trigger? CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
2
votes
1answer
99 views

Can comments in stored procedures cause problems

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state ...
2
votes
1answer
27 views

Running members of a job subset at different intervals

So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for ...
0
votes
1answer
132 views

SQL Server Stored Procedure - Does Raiserror affect performance

Does RAISERROR in a SQL Server stored procedure affect performance? Would I be better off just having a try-catch wrapper around the whole procedure and just catch the errors in the database access ...
1
vote
1answer
400 views

can I Run a Stored Procedure across all SQL servers and return 1 html report

I have the following set up as a sp and it runs daily. It gives me a report for each SQL cluster in my environment but what I'd like to do is configure it to run across all clusters and return 1 ...
1
vote
3answers
328 views

Proper use of database functions for business logic

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry. These ...
2
votes
1answer
659 views

Return ResultSet with column names from postgresql Stored procedures

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
-3
votes
1answer
92 views

Sproc - Adding columns with matching ID column [closed]

I currently have a sproc that is used for reporting on a table similar to the one below: ID Weight QTY OtherData 1 20 12 1234 1 44 4 1234 ...
0
votes
1answer
86 views

how to Update Value in local tabel when job Finished

I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs. In other words, I want do something like status when the all jobs ...
1
vote
1answer
159 views

Stored procedure to validate xml content against dtd

I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file? Thank you in advance!
0
votes
3answers
1k views

Can I give the same name to a table and a stored procedure?

Is it possible to name a stored procedure the same as a table? I have an existing table named 'buyers', and I'm trying to create a stored procedure named 'buyers', but I'm getting this error: ...
1
vote
2answers
332 views

Stored Procedure in MySQL

I am new to MySQL Stored Procedure, in my first attempt I got the following issues, Stored Procedure: Delimiter $$ create procedure accountstatus ( IN inmode varchar(27), IN ...
1
vote
1answer
224 views

Inserting filtered data from one table to another

I need to find the number of employees who are not in all departments. I have one more table T2 in which same department column is present. Now I want to insert only those values of department which ...
3
votes
0answers
239 views

SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows

So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part: ALTER PROCEDURE ...
0
votes
1answer
249 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
0
votes
2answers
3k views

How to Drop Tables using a variable in SQL Server?

DETAILS: I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table. WHY?: In my Access Database I am running a ...
3
votes
1answer
136 views

Custom sp_who/sp_whoUsers

I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential ...
4
votes
1answer
1k views

Join To a @Table Variable is running ineficiently

Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
8
votes
2answers
4k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
12
votes
2answers
10k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
0
votes
2answers
5k views

Use of CASE statement in a MySQL query

I have 3 tables: TASK (id_task (PRIMARY KEY), name) PROJECT (id_project (PRIMARY KEY), name) PROJECT_HAS_TASK (id_project, task_id) PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK. ...
1
vote
1answer
1k views

MySQL - Create a function from another function

I want to create a function same like my old functions. When I create a table, I can use `AS to other table as follows: CREATE TABLE dummy2 AS SELECT * FROM dummy1; Can I do the same thing with ...
1
vote
1answer
124 views

Stored procedures and network congestion

When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it ...
1
vote
1answer
1k views

Production SQL Server High CPU for a Single Query

I have a single query which is consuming a larger amount of CPU than I would expect in SQL 2008. I am not able to determine why the CPU is spiking for this single query. I can trigger an ...
4
votes
2answers
21k views

How do I use the IF…ELSE condition in a WHERE clause?

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT. Should this work? CREATE OR REPLACE package body If_Else_Pack IS PROCEDURE Moving ...
11
votes
2answers
2k views

PostgreSQL procedural languages - differences between PL/pgSQL and SQL

Can anybody please summarize the differences between: http://www.postgresql.org/docs/9.1/static/xfunc-sql.html and http://www.postgresql.org/docs/9.1/static/plpgsql.html ? Main points: ...
4
votes
1answer
7k views

Show procedure code in MySQL

I've tried to show the SQL code of a stored procedure called add_pixel: mysql> SHOW PROCEDURE CODE add_pixel; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ...
10
votes
1answer
9k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
6
votes
3answers
2k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
4
votes
3answers
1k views

SQL Server stored procedure keeps restoring to previous one

There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure ...
5
votes
1answer
1k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN ...
5
votes
2answers
17k views

Granting Execute Permissions on Stored Procedures, Functions and Views

I am using a user which has read permission only. It basically has permission to Connect Execute However, it does not have execute permissions on Functions and Stored Procedures. In part of my ...
5
votes
1answer
6k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
4
votes
1answer
515 views

How to change database owner for login and database created inside stored procedure?

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...
0
votes
1answer
117 views

TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs

I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs. Each office has already imported data ...