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
0answers
182 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 it ...
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
871 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
364 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
487 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
5k 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 ON;...
1
vote
3answers
955 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
49 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
304 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 VARCHAR(...
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 IF-...
1
vote
2answers
48 views

Сopy rows from one table to another and replace duplicates in the process

I have an SSIS job that loads daily data from CSV files into a raw staging table in a SQL Server database (technically is an Azure SQL database). Then after that, a store procedure is executed to copy ...
1
vote
2answers
128 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
75 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
49 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
3answers
18k views

How to create a SQL trigger to execute a stored procedure when a table is updated

i need some help with creating a trigger to run a qf.stored_procedure when a qf.table is created. AT the moment, i have a job t run the stored procedure every 15 minutes but changing that is not an ...
1
vote
2answers
170 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
883 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
2answers
233 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
105 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 ...
1
vote
2answers
149 views

deteriorating stored procedure running times

I have a stored procedure that is executed by my web application. Today, when an end user started an operation that affected more rows than usual, I started getting calls that the wait times for the ...
1
vote
1answer
39 views

Problem locating rows with dynamic SQL

I'm new at SQL in general, so I'm have a few problems with some of my stored procedures I'm designing. My procedure takes in 3 parameters. A location (or table name basically, a varchar), a room ...
1
vote
1answer
660 views

stored procedure vs. function in postgresql and which is better for executing dml statements

I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure? My basic steps are: RENAME TABLE ...
1
vote
2answers
771 views

Stored procedure - problem with merge - Insert

I am calling a stored procedure from my c# code . The procedure works fine for update, but fails for Insert. Is the following merge-insert procedure code correct? Any help is appreciated. Here is ...
1
vote
4answers
5k views

Execute a Stored Procedure Daily

I am building a Web Application which is attached to a Database which is in a Web Server(which is at unknown remote location). I want to execute a Stored Procedure daily at a specific time. I searched ...
1
vote
2answers
2k views

multiple select query for the same tables with union [closed]

Using below query i want to find out the top number of students from each catagory(SC,ST,OBC,etc) on plus2percentage basis. eg i want top 3 students from SC catagory, top 2 from ST and Top3 from OBC. ...
1
vote
1answer
6k views

column auto updated after 24 hours in mysql

i have a table T1 with column : Name State time mon 1 2014-01-09 11:23:00 tue 0 2014-01-07 14:40:00 wed 2 2014-01-08 09:23:00 thu ...
1
vote
1answer
10k views

Stored Procedure Performance using Temporary Tables

I have the following Stored Procedure I would like to improve the performance of DELIMITER $$ CREATE PROCEDURE NEXT_UNIT_OF_WORK ( IN batchId INT, IN size INT ) BEGIN start transaction; ...
1
vote
2answers
123 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 ...
1
vote
2answers
788 views

From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads

I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0. Now I am using features of SQL Server like: Common Table Expressions (I have a table ...
1
vote
1answer
1k views

How can I restore the sql_variant_property of baseType back to a table variable in SQL Server

Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008 I have a table that I am trying to use as a storage container. It has 3 columns: reportID integer, dataLabel ...
1
vote
1answer
173 views

oracle how to call procedure

This is very basic question, but I couldn't find an answer here... So I have this very useful procedure without any parameters: CREATE OR REPLACE PROCEDURE DISABLE_TRIGGERS IS v_statement VARCHAR2(...
1
vote
1answer
162 views

transactional Replication of millions of updates

I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates ...
1
vote
1answer
2k views

Executing a stored procedure on remote database

I have 2 databases on the same server. DB-A and DB-B. DB-B has a user that has read and write access to database B. I would like to grant the user in DB-B execute permissions to 2 stored procedures in ...
1
vote
1answer
34 views

Use stored procedure to get minimum value from column and decrement it and other values

I'm trying to understand how the following algorithm could be completed in SQL 2008 R2. I have a table that contains production data. The table uses a "rolling" scheduling method, meaning that orders ...
1
vote
3answers
28 views

Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3. use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(...
1
vote
2answers
47 views

Why does my statement result in a syntax error?

I tried to run this SQL statement in MySQL 5.5 and 5.7.1 but always got error. DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE @batchsize BIGINT DEFAULT 1; ...
1
vote
1answer
55 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
1
vote
1answer
33 views

SUM() 3 columns with alias into a fourth one

This is one part of my procedure: select nome, --casos expedidos (- 3 meses) isnull((SELECT count(cod_rep) FROM tbReps where ...
1
vote
1answer
81 views

How to change aggregate function without duplicating SQL (by using SQL)

In MS SQL Server 2016 I have a scenario where data will be processed according to different aggregation functions in a large GROUP BY ROLLUP. I would like to have a stored procedure that has a ...
1
vote
1answer
52 views

Determine structure of out cursor

I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains? For ...
1
vote
1answer
49 views

SQL Stored Procedure with one parameter and several variables that will change over time

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in Error output: ...
1
vote
2answers
479 views

Script to find the list of stored procedures in all databases

I need to pull out the list of stored procedures which are available in my instance. I used the following T-SQL statement to get the stored procedures in a given database. select * from MyDatabase....
1
vote
1answer
21 views

Grant Permissions Within Report SP

Leaving aside why one would want to do such a thing: In MS SQL Server 2008, Is it possible to have a report SP grant permissions to a column used in that report to a table in a database other than ...
1
vote
1answer
66 views

Why RECOMPILE doesn't fix parameter sniffing, but local variables do?

A developer was asking me about why his procedure wasn't running, but the query was ok. When I run the query, it was fast.But running the procedure, it was executing forever. What I did was to add ...
1
vote
2answers
28 views

Retrieve data from SP inside of From

Goal: Retreive a list of data from a stored procedure that is acting as a database name. When you have retrieved the list from stored procedure, I want to make another new data manipulation. Problem: ...
1
vote
1answer
1k views

SQL Server stored procedure causing 100% CPU usage

I'm using SQL Server 2008 R2 We have a single stored procedure that causes 100% CPU usage when ran. The said SP is ran once a month and performs heavy calculation for approximately 5 minutes. Since ...
1
vote
1answer
49 views

stored procedures location influence their performance?

I have a procedure SP_MYPRO that updates data in MYDB1. 1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1? 2) what if the procedure SP_MYPRO only read data, ...