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

2
votes
1answer
644 views

How to get an array as output parameter from stored procedure?

This is my stored procedure: CREATE PROCEDURE `getAttritionData`( IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT ) DETERMINISTIC ...
2
votes
1answer
249 views

Periodically update a table with new records from another server

I have databases on two different servers. I need to regularly retrieve new records from a table on server A, and process them in order to use them to update a table on server B (with a different ...
2
votes
2answers
590 views

Sql Agent Jobs executes too slow

I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure USE [xxxx] GO /****** ...
2
votes
1answer
1k views

How to output more than 4000 characters in sqlcmd

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this: restore database [DB] from disk='F:\FULL1.bak' with norecovery restore ...
2
votes
1answer
878 views

SQL Server stored procedures fast in SQL but slow when called by ASP.NET

I am using ASP.NET with MVC3 with a SQL server database. From time to time, one of our stored procedures become incredibly slow when called by the web platform, but stay as fast when called directly ...
2
votes
1answer
5k 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; ...
2
votes
1answer
128 views

MySQL Stored Procedure Requiring SUPER

I'm trying to figure out a "safe" way of allowing a non-SUPER user to execute a specific command requiring SUPER privileges, i.e. SET SESSION binlog_format = 'MIXED'. I don't want to grant the user ...
2
votes
1answer
321 views

How can I load 'utf8' into plperl by default?

I am attempting to solve a problem explained in another question here. On Debian 7.4 with Postgres 9.1.12, I am able to use the Perl qr operator just fine. On Solaris 5.11 with Postgres 9.2.4, ...
2
votes
2answers
17k views

insufficient privileges while executing oracle stored procedure?

Im getting insufficient privileges error while executing the following oracle stored procedure. Im using Oracle Database 10g Express Edition. CREATE OR REPLACE PROCEDURE sp_update_acounts( ...
2
votes
2answers
3k views

MySQL Workbench Alter Procedure

i have a problem with my workbench program. I use Mac Lion OS X and mysql workbench program. When i want to do any procedure to alter that doesn't work. Which is show to the procedure code i right ...
2
votes
1answer
4k views

MySQL: how to optimize the stored procedure?

MySQL 5.5.28 Running pt-query-digest on a slow query log, I get something like this: # 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # ...
2
votes
1answer
1k views

Add user to Server Role in a transaction

Situation One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
2
votes
2answers
2k views

Bulk grant execute permissions for scalar functions & stored procedures

We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions ...
2
votes
1answer
2k views

performance issues when concurrent requests

We are using SQL Server 2008 R2 on a Windows Server 2008 server. We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it. I'll take one SP as an ...
2
votes
1answer
1k views

How to use the 'select benchmark' command on a procedure

I am trying to check which of my procedures is slowest and thus needs optimization. I currently have around 160 procedures and about 30 functions. I am using the select benchmark() to check the ...
2
votes
1answer
744 views

high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005

I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity) SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ...
2
votes
1answer
36 views

Stored procedure unable to compare

Hi I tried implementing the below stored procedure which always takes my if condition as true and implements . CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOpen`(in uid varchar(11),in S_ID ...
2
votes
1answer
53 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
2
votes
0answers
43 views

Mysql stored procedure SQLEXCEPTION

Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id. insert into folder (name)values("new"); update folder set count = count + ...
2
votes
0answers
38 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
2
votes
1answer
2k views

Get values from Select statement and Insert them in Temp table Stored procedure [closed]

Here is my stored procedure. I want to select data from a table, save it in a variable then insert in other variable. However, my select statement has where. In it the value for the day should come ...
2
votes
0answers
20 views

Sum of points and rank according to points with join three tables [duplicate]

I am creating a game where I am calculating the user points on the basis of user activities. Everything working fine I got the sum of points and on the basis of points I calculated the rank. I have ...
2
votes
1answer
150 views

MySQL stored procedure updates twice

I have this stored procedure: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT) BEGIN DECLARE ...
2
votes
1answer
17 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
2
votes
0answers
668 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 ...
2
votes
0answers
4k views

How do I access a result set from a StoredProcedure… in another StoredProcedure

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like ...
2
votes
1answer
2k views

How to call stored procedure inside trigger?

I have made stored procedure that will update product quantity as per product id. That works fine for me. Following is code : DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE ...
2
votes
0answers
127 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
2
votes
0answers
166 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 ...
2
votes
0answers
276 views

Stored procedure doesn't return results or any messages when executed as other privileged user [closed]

I have many stored procedures with... EXECUTE permission given to role XXXUser. Login XXXLogin is created and assigned to the role of XXXUser There is one particular stored procedure that I have ...
2
votes
0answers
180 views

Stored procedures over SQL in the code, good idea? [duplicate]

Possible Duplicate: What are the arguments against or for putting application logic in the database layer? Assuming I got over the problem of maintainability and debugging. My assumption is ...
1
vote
3answers
2k views

How can I drop a stored procedure whose name is in a variable?

Given a variable that contains a stored procedure name: DECLARE @stored_procedure_name sysname; SET @stored_procedure_name = 'some_stored_procedure_name'; How can I drop the stored procedure?
1
vote
3answers
5k 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
3answers
809 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 ...
1
vote
2answers
358 views

ORM-style server-side programming languages (OO replacement for PL/SQL?)

Good evening, Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]? (for MySQL, PostgreSQL, Oracle or etc.)
1
vote
1answer
482 views

How to improve performance of SP with many optional filtering parameters?

I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem ...
1
vote
3answers
4k views

Convert all ntext columns to nvarchar(max)

I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max). This is the code ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar] AS BEGIN SET NOCOUNT ...
1
vote
3answers
907 views

Support both stored procedures and functions with same TSQL code base?

We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends. Given the following ...
1
vote
1answer
33 views

SPID Change for a process?

On SQL Server 2012 can a process SPID change mid-operation? We have a very unique query that we've been monitoring (production, but controlled testing) and have seen it at different intervals with ...
1
vote
2answers
298 views

SQL Server RandomNumbers Stored Procedure Error

I tried to create a stored procedure for generating random numbers but it doesn't work; the random numbers are identical in every record. CREATE PROCEDURE Update_Driver_Position @TempName ...
1
vote
4answers
2k views

How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?

I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query. Simply i can use ...
1
vote
2answers
65 views

SQL Server CREATE and DROP TABLE Permissions in Stored Procedure Only

I'm attempting to create a stored procedure that's able to create and drop tables. A given user will have EXECUTE permissions on that stored procedure. Is there any way that the user would be able to ...
1
vote
1answer
65 views

Using NOCOUNT to Improve Procedure Performance

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON. I've read a couple of articles on the subject: Aaron Bertrand SET ...
1
vote
1answer
48 views

Is it possible to get a list of procedures created with WITH EXECUTE?

Is it possible to get a list of stored procedures create with WITH EXECUTE? I tried looking at sys.procedures, but they all have the principal set to null, and I know there are some procedures ...
1
vote
2answers
160 views

pg_stat_activity - is it possible to decompose a stored procedure further?

I got a long running query in pg_stat_activity: datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | ...
1
vote
2answers
2k views

Issues with module signing and SSIS catalog internal procedures

I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user. I found this article which ...
1
vote
2answers
85 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 ...
1
vote
2answers
2k 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; ...
1
vote
1answer
853 views

How to split the comma separated list in the stored procedure

I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
1
vote
1answer
98 views

SQL Server many active create procedure processes

While troubleshooting performance issues on a SQL 2008 instance, I've noticed there are several SPIDs which appear to be executingcreate procedurestatements for already existing procedures. Is this ...