Questions tagged [stored-procedures]

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.

Filter by
Sorted by
Tagged with
0
votes
0answers
6 views

Is it possible to force a query to ignore the server option “remote proc transaction promotion” so it doesn't promote remote proc transaction?

I have a specific query that executes a stored procedure on a remote linked server and inserts the results into a local temp table. I don't want it to promote a remote procedure transaction but I can'...
0
votes
0answers
10 views

An item with the same key has already been added Error in Report Builder

An item with the same key has already been added Error in Report Builder Relevant SQL Server stored procedure executed successfully when we attach the data set to the Report builder after refreshing ...
4
votes
1answer
69 views

Why does querying for records that violate a foreign key return incorrect results?

While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant ...
-1
votes
1answer
35 views

How to Addition two Sums with Groups [closed]

( ( SELECT Sum(itemb.qty_invoiced*( CASE WHEN cod.disc > 0 THEN itemb.price * (1-cod.disc/100) ELSE itemb.price ...
0
votes
0answers
40 views

SQL Job get stuck on a simple Select into vaiables query

I have an issue that I please need help in trying to resolve as its really becoming a bit pain. I have some Jobs that monitor our systems for calls stuck and it's getting stuck (the irony is not lost ...
1
vote
2answers
19 views

What configurations do I need to fix the “unable to begin a distributed transaction” error when trying to run a remote procedure?

This is the error message I'm receiving: Msg 7391, Level 16, State 2, Procedure spStoredProc, Line 62 [Batch Start Line 1] The operation could not be performed because OLE DB provider "SQLNCLI11" ...
0
votes
1answer
69 views

change stored procedure to accept a range of ids

So I'm looking at a stored procedure, which works with nopcommerce. It's used when searching products. There is one section which allows filtering of specification attributes, and it it seems to only ...
0
votes
1answer
51 views

ORACLE DB literal does not match format string

I' am having this error: ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string" *Cause: Literals in the input must be the same length as ...
0
votes
1answer
27 views

SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER

We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker ...
1
vote
1answer
67 views

Can sp_BlitzFirst pass the @ExpertMode parameter to sp_BlitzWho [closed]

When running sp_blitzFirst @expertmode = 1 the first result set(result set of sp_blitzwho)does not return nearly as many columns and it does in Brent's video. With a little troubleshooting it was ...
1
vote
2answers
74 views

Viability of a large database without using indexes and some other things

I'll explain my situation as briefly as possible. My group is working for another company and I'm mostly creating stored procedures for making reports. The twist is that, last month, they decided to ...
0
votes
1answer
44 views

SQL Statement Layer Filter Queries Limit Access

Goal: Want to create a so called "SQL Statement Layer" which checks/filters all incoming queries. If incoming query is invalid, should be denied. Use-case: Limit database access and avoid ...
0
votes
0answers
15 views

Create postgresql function to be triggered before insert row

I've created a function to be executed before inserting rows in a table, in order to avoid the error "malformed array literal". It still throws the error and I deduct that the process starts earlier ...
0
votes
3answers
102 views

MySQL LIKE IN with Input Parameter of stored procedure Comma Separated Values

I am creating a stored procedure to search user's Towns. From the User Interface user will select multiple towns that will be sent to the stored procedure. Sample input of the towns parameter for the ...
1
vote
1answer
31 views

Rollback is not working if the storedproce call another storedproc

Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit? example: pseudo procedure proc1 create procedure proc1(in var1 int, out var2 int) ...
0
votes
0answers
19 views

SQL Server - How to know to which database user certificate is mapped to?

Inside a user database, I have two certificates, both are used for signing stored procedures First cert was created long time ago by someone Second I created myself for testing purposes For ...
1
vote
1answer
18 views

Postgres get function owner

I would like to be able to get a table of function names and function owner names. Currently, this query returns a list of functions for a given schema: SELECT proname FROM pg_catalog....
3
votes
1answer
71 views

How to grant permission to execute stored procedure in master database from another database

Our developers have a domain account used by their application that needs to be able to execute stored procedures created in the master database using the "sp_" naming convention from other databases ...
0
votes
1answer
24 views

ALTER TABLE WITH VALUES FROM SELECT

I have a table1 with fluctuating number of columns. (colname1,colname2,colname3...colnamen) I want to : ALTER TABLE table2 CHANGE COLUMN `columns` `columns` ENUM('colname1','colname2','colname3' .......
0
votes
1answer
25 views

ROUTINE TO SELECT FOM A TABLE AND UPDATE ANOTHER TABLE [closed]

I do not understand how to update a table from another table after a table has been modified. I have a table with 3 columns like id(INT), distname(VARCHAR)UNIQUE and score(INT), and I have another ...
-3
votes
1answer
36 views

Store procedure Incorrect syntax else [closed]

Could I be missing a condition inside my stored procedure? I'm getting this error: Incorrect syntax near keyword else line 41. use eNtsaRegistrationDB; create proc[dbo].[...
1
vote
1answer
52 views

DB2 - Using stored procedure variable in a “CREATE TABLE as (..)” Statement

migrating from Netezza to DB2 we face the following problem: CREATE OR REPLACE PROCEDURE DoStuff() BEGIN DECLARE vStarttime TIMESTAMP; SET vStarttime = now(); CREATE TABLE foo AS ( ...
2
votes
1answer
31 views

calling scalar function to assign value to local variable

In a SQL Server stored procedure, if we declare a local variable and assign the value by calling a scalar function DECLARE @variable_name INT= dbo.scalarfuntion_name(@p1, @p2). Would it have any ...
1
vote
1answer
36 views

Stored procedure to set value to a column must accept returned values from functions

I created a very simple table in Data Mart to store ETL diagnostic information: CREATE TABLE [internal].[etl_status] ( [property_name] VARCHAR(100) NOT NULL PRIMARY KEY, [property_value] ...
0
votes
1answer
27 views

Ge the value of id in master table and supply it to other tables

I have 5 tables, of which one is master table and other 4 are the tables with have the foreign key of the id of the master table I have to create a procedure which INSERT's data in all the tables ...
-1
votes
1answer
49 views

Updating tables in sql

I've made this procedure to update the fields on a table: CREATE OR REPLACE PROCEDURE actualizarEncargadosP( OID_EN2 IN encargadosp.oid_en%TYPE, nombre2 IN encargadosp.nombre%TYPE, apellido2 IN ...
2
votes
1answer
48 views

Finding stored procedures with XACT ABORT ON OR OFF

I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog ...
2
votes
1answer
56 views

Are stored procedure query texts stored in any tables?

I don't readily see anything in the sys schema objects, other than the DM_EXEC_SQL_TEXT function that requires a plan handle, but the procedures I'm trying to get their query texts from aren't ...
-2
votes
1answer
53 views

Case statement in WHERE clause in SQL Server [closed]

I am new to SQL Server i am writing stored procedure for report search CREATE PROCEDURE [dbo].[SP_BILLSEARCH]( @CustCode INT, @BillAmount MONEY, ...
1
vote
1answer
57 views

INSERT EXEC statement cannot be nested

I have a stored proc spGetSites that returns three columns for SiteName, SiteId and UnitCount. The stored proc runs perfectly fine. I'm trying to store the results of the stored proc to a temp table @...
3
votes
2answers
153 views

SQL Deadlocks - Same Stored Procedure Called Concurrently

I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to ...
0
votes
3answers
64 views

PostgreSQL: Is it possible to get a different function result type (i.e. TABLE) in stored function based on input?

The question is as follow, I wanted to get some flexibility in my example function, so based on input arguments to function I wanted to return different table as an output result. So is it possible ...
2
votes
3answers
255 views

SQL Server 2017 Stored Proc & TVF Performance Problem

I have a SQL Server 2017 Enterprise Edition instance where a stored procedure was taking approx. five minutes to execute. After reviewing the stored procedure code, I could see that there was an ...
0
votes
2answers
51 views

How to drop a procedure across a lot of databases (SQL Server 2016)

I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete ...
1
vote
1answer
31 views

Distribute data into x groups of at-most n records each

I have a set of Z entries(e.g. 100 records), which I want to insert into Header and Detail tables. I want to divide this group into X groups, with each group containing at-most N entries. The last ...
0
votes
2answers
39 views

Database table archiving

I am trying to create a stored procedure (SP) in order to archive a table within my production database which is about 1TB. The production DB runs on Linux SUSE OS and the destination location for ...
2
votes
1answer
70 views

Linked Server Timeout - Not Queried

I have a job that runs for multiple regions across the globe. Depending on what region is running and what time zone it's in, the job might not run. It uses GOTO to get to the end and reports that it ...
2
votes
1answer
27 views

User and Schema Recreated during Stored Procedure Creation

As mentioned in the subject, a new user and schema are being created when a developer I'm working with creates a stored procedure. Originally I made a user and login for that developer individually ...
3
votes
2answers
252 views

Rolling back truncate table

I have a stored procedure that does the following: BEGIN TRANSACTION -- Code to delete updated records from production (dbo) table DELETE FROM [dbo].[factMyTable] WHERE exists (SELECT * ...
1
vote
2answers
52 views

Trying to run a procedure, error says result consist more than one line

There is an officecode and employeeNumber in the employees table, which references city in office table. I want to create a stored procedure setRelocationFee to set the relocation fee for a given ...
3
votes
3answers
255 views

Only return recordset(s) from Stored Procedure if it has rows

I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty. I'd like to reduce overhead and only return those which have 1 or ...
0
votes
1answer
28 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
0
votes
1answer
29 views

Stored Procedure taking longer than a normal query

I have a stored procedure which returning single but taking 16+ seconds when I call that but only .5 second, if I run that by normal query. Procedure DELIMITER $$ DROP PROCEDURE IF EXISTS `...
1
vote
0answers
17 views

Variable usage in MySQL Stored Procedure

I've some issue with a stored procedure on a MySQL 5.6.36: DELIMITER $$ USE `test_schema`$$ DROP PROCEDURE IF EXISTS `count_requests`$$ CREATE PROCEDURE count_requests() BEGIN DECLARE ...
1
vote
1answer
33 views

Unexpected results from MariaDB IF EXISTS … THEN

I am having unexpected results from MariaDB IF EXISTS... in a stored procedure. I created a minimal example to demonstrate the issue: PROCEDURE testdb.testSearchResults(IN pParam1 INT, IN pParam2 ...
12
votes
1answer
598 views

How to get the parameter values of a stored procedure that's in the middle of execution?

Is there a way to get the values of the parameters that were passed in to a stored procedure while the stored procedure is in the middle of execution? (I'm trying to debug an actively running stored ...
0
votes
0answers
18 views

what is the difference between replication options proc exec and definition only?

I understand proc exec option as for example when you run a procedure which inserts 1000 records into table, those insert afterwards will be replicated to the subscriber. When it comes to definition ...
0
votes
3answers
66 views

Nullable smallint column usage in a CASE Statement nested in a STUFF function

I'm having issues writing a Stored Procedure that uses a CASE statement inside of a STUFF function. I thought my issue may have to do with Restricted being a nullable smallint. I tried converting it (...
7
votes
1answer
171 views

If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
2
votes
1answer
89 views

Find Table Variables definitions in Stored Procedures

As part of tuning of a large database, I am first of all interested in finding all the table variables. I am using this script: SELECT [Schema]=schema_name(o.schema_id), o.Name, o.type FROM sys....

1 2 3 4 5 28