Callable code installed on a database manager, exposing an API through which it can be invoked. Normally written in the native query language, some DBMS platforms support other languages as well.

learn more… | top users | synonyms

1
vote
2answers
2k views

Should I put the error handler inside the loop, or the loop inside the error handler?

When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run ...
1
vote
1answer
2k views

Heterogeneous queries error using stored procedure

Stored procedure ALTER PROCEDURE Proc_DayShift AS insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO SET ANSI_WARNINGS OFF ...
1
vote
1answer
139 views

how to write pl/sql named programme for this [closed]

I have a table M_PARAMETER which has a column MMRUN. When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification. ...
1
vote
0answers
18 views

TSQL Stored Procedure - Granting general user access to specific sproc

I'm attempting to alter a sproc that needs the line added : grant exec on dbo.sproc_name to exec_proc. I believe the admin setup the exec_proc as a group. I added this grant line following the final ...
1
vote
2answers
36 views

What is the correct query set up to access the columns in table being passed as parameter?

I am trying to use table-valued parameter to bulk upsert some data into my table. I have the following stored procedure. This setup seems to agree with the intellisense. I am not getting any errors ...
1
vote
2answers
42 views

call mysql function by string name

Can i call a mysql stored function from another mysql stored function or stored procedure, passing a string to the first function which is the name of the second function to call. I.e choosing the the ...
1
vote
0answers
53 views

How to access another db from a stored procedure in db2

I want to query a table in another db from within a stored procedure in db2. For example, I am connected to db_a1, and call a stored procedure from within which I need to connect to a table in db_a2. ...
1
vote
1answer
116 views

Stored Procedure slower on one server than the other

I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why. Setup 1 is my virtual environment, ...
1
vote
1answer
269 views

MySQL: IF statement check is a table exists

I run in the following issue and can't figure out how to resolve it. delimiter # create procedure pr1() begin declare v1 double default 0; start transaction; ...
1
vote
1answer
113 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` ...
1
vote
1answer
96 views

How to log within a stored procedure that is encapsulated within a transaction

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I ...
1
vote
0answers
292 views

Rolling Back Stored Procedures in Entity Framework

I currently working a on project that operates within a transaction in entity framework. Before now all of the changes were made using a DataContext. I need to add a call to a stored procedure using ...
1
vote
0answers
87 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 ...
1
vote
0answers
445 views

exec sp_getapplock - Different behaviour using go or without go

we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH ...
1
vote
1answer
131 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
1
vote
1answer
2k views

Creating an SSIS package that uses a stored procedure

We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing ...
1
vote
0answers
157 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 ?
1
vote
0answers
110 views

user rights validation inside stored procedure?

You have an application with a Users table, and the Users table has various columns to indicate what rights a User has within the application. Ignoring for the purposes of this question what should ...
1
vote
0answers
105 views

Using #temp to work around Reporting Services blocking issue

Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process lck_m_s when called via a report in SQL Reporting Services I have ...
1
vote
0answers
565 views

How to use the result of regexp_split_to_array() in stored procedure [closed]

I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table. The point where I get stuck is the ...
1
vote
0answers
320 views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
1
vote
1answer
3k views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
1
vote
0answers
140 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
1answer
295 views

Cannot grant execute to stored procedure [closed]

When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below: Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5 Arithmetic overflow error ...
1
vote
0answers
1k views

Does MySQL close cursor if stored procedure encounters an exception?

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines. Assuming, that ...
1
vote
1answer
5k views

How to return resultset from MySQL Stored Procedure using prepared statement?

DELIMITER $$ CREATE PROCEDURE List_IL() BEGIN DECLARE Project_Number_val VARCHAR( 255 ); DECLARE Temp_List_val VARCHAR(255); DECLARE Project_List_val VARCHAR(255); DECLARE FoundCount INT; ...
1
vote
0answers
905 views

How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?

This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's ...
1
vote
1answer
253 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
1
vote
0answers
137 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
1
vote
1answer
758 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
1
vote
0answers
455 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
1
vote
0answers
31 views

What might cause all objects in an Oracle database to suddenly have quoted identifiers?

I have an Oracle schema in which, at some point in the past ("the event"), all of the procedural code objects in the database have been re-created with quoted identifiers. All of the objects are ...
1
vote
0answers
896 views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...
1
vote
3answers
3k views

BACKUP failed to complete - with spaces, for no reason

Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
0
votes
3answers
2k 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: ...
0
votes
2answers
95 views

What do cursors do internally?

I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR... The client saw this SP ran for more than a day so, my manager assigned this to me. Upon searching, I used table ...
0
votes
2answers
178 views

How to use same stored procedure for different tables and avoid using dynamic sql

I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However ...
0
votes
2answers
3k views

Hide column if any value is null or zero via proc sql or view [closed]

Given either of the two following datasets Kağıt Cinsi Sarı Yeşil Mavi Kalın 4 0 5 İnce null 0 3 or Kağıt Cinsi ...
0
votes
2answers
306 views

Can't see some stored procedures in sys.procedures

Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer. select p.name, x.value, p.modify_date from sys.procedures p left ...
0
votes
1answer
444 views

Create and fill temp table

I have a stored procedure that selects some data and returns it. Now I also want to add a temp table, fill it with data and return it with the stored procedure. I define it as follows: create table ...
0
votes
2answers
72 views

Known vulnerabilities for when a guest executes a stored procedure on SQL Server

I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How ...
0
votes
2answers
297 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
0
votes
2answers
2k views

Grant permission to execute stored procedure without granting access to underlying table - Oracle

I am new to Oracle (10g), my background is SQL Server. I have a service account that needs permission to execute a stored procedure, but nothing else. I see from here how to add execute permissions ...
0
votes
2answers
821 views

create table with date name

I am writing a simple procedure to backup a table- CREATE PROCEDURE daily_backup() BEGIN DECLARE given_date VARCHAR(25); SET given_date = now(); CREATE TABLE given_date LIKE db1.table1; ...
0
votes
1answer
6k views

Stored Procedure Create Table from Variable with Variable Constraint

I have managed use a store procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure. The problem: The ...
0
votes
2answers
90 views

Stored Procedure - MySQL

I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to ...
0
votes
2answers
283 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
0
votes
1answer
933 views

Cannot `create function` in plpython3u, permission denied

As postgres user, I have create extension plpython3u; in my database then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true but ...
0
votes
1answer
348 views

Stored procedure arguments get immediately truncated

I have a stored procedure with a single SELECT statement inside: DROP PROCEDURE `pos_neg_test`// CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP) BEGIN SELECT ...
0
votes
2answers
54 views

Stored Procedure never completes with Exec, but i can run the Query in SSMS

Im with a problem here. If i run "exec pr_BuscaLog -2', it will run forever ( days, without end )... BUT, If i select the Query and F5, it runs in seconds. I updated STATS but it didn't work. Some ...