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
-3
votes
1answer
33 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].[...
-3
votes
0answers
37 views

Incorrect syntax near end [closed]

Does anyone knows how to solve this issue 'Incorrect syntax near end'. Here is my store procedure. use eNtsaRegistrationDB; CREATE proc [dbo].[Usp_CheckUserRoles] @UserId int as begin if((select ...
-2
votes
0answers
14 views

StoredProcedure has error on Field name, ID and TableName how to fix? [closed]

create proc [dbo].[Usp_UserNamebyUserID] @UserId varchar(200) as begin select UserName from Users where Id =@UserId end go Hi Mates i have this procedure on my sql editor, on the table itself from ...
1
vote
1answer
31 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
26 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
34 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
26 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
48 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
42 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
54 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
50 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
134 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
62 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
237 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
48 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
29 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
30 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
54 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
25 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
228 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
47 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
159 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
26 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
24 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
16 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
19 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
587 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
64 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
87 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
vote
1answer
65 views

Update dynamic column names using keys from jsonb argument

I'd like to write a generic procedure for Postgres 12 that updates a table's columns depending on what data is supplied in a jsonb argument. It could be done in application logic instead of course, ...
0
votes
3answers
87 views

Create stored procedure with dynamic number of columns + column names

I am relatively new to postgresql (I have version 12), and am having some trouble creating my first stored procedure. The real trouble is that I need it to be dynamic in some way, and thus I am having ...
1
vote
0answers
42 views

Need suggestion on optimizing the query which calls a scalar function

--gets the configuration (team or client or default) CREATE FUNCTION [dbo].[get_config] (@TEAM_CONFIG BIT, @CLIENT_CONFIG BIT, @DEFAULT_CONFIG BIT) RETURNS BIT AS BEGIN DECLARE @bit BIT = ...
3
votes
0answers
29 views

Encrypted stored procedure is encrypting the plan cache query plan

I'm having a really bizarre issue when querying the plan cache that relates to stored procedure encryption. However, it's the stored procedure that is querying the plan cache that is encrypted, not ...
1
vote
2answers
36 views

How to copy table content before deleting to flat file using BCP

I have created stored procedure where, I delete data in any given time. I want to save the records to a flat file before deleting in case I need to import the data back to table. I DO NOT want to call ...
0
votes
1answer
103 views

Error: Cannot create new transaction because capacity was exceeded

We are getting error messages from our SQL-server when we run a stored procedure to increase a counter. This SP is executed many times for different entities but when executed for a shipment-number ...
0
votes
1answer
71 views

How to optimize stored procedure so it won't end with “Out of memory exception”

I have created a stored procedure that check against 2 and then delete from the 3. When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: ...
0
votes
0answers
38 views

Stored procedure execution duration and sending email to DBA if it run long

Does anyone has a T-SQL query which checks the stored procedure run duration and inform DBA team if it run long may be more than 1 min. Any help would be appreciated Thanks
1
vote
0answers
29 views

Missing CALLs on identifiers_all using PL/SCOPE

Posted this on StackOverflow, but maybe this is the right place for it: I'm trying to use the view all_identifiers from PL/SCOPE to find out calls to procedures and functions but it's not showing all ...
0
votes
1answer
92 views

Create a user on database A with a procedure from database B (with EXECUTE AS)

I have 2 logins : LoginA: role dbcreator + CONNECT ANY DATABASE LoginB: role sysadmin I have 2 databases : dbA: everyone has all rights on it dbB: only LoginB has rights on it, LoginA is only the ...
-1
votes
1answer
26 views

insert a variable and table value as parameters to procedure [closed]

CREATE PROCEDURE [dbo].[GetFruitName] ( @quantity int ) AS BEGIN SET NOCOUNT ON; INSERT INTO Table1(fname,fprice,quantity) SELECT f.name,f.price from ...
0
votes
1answer
26 views

Is it safe to leave/exit the stored procedure without commit or rollback on a transaction?

Like the code below is it safe to do that? create definer=`root`@`%` procedure `test`() label: begin declare exit handler for sqlexception rollback; start transaction; select condition from table ...
0
votes
0answers
16 views

prepared statement returns NULL, unable to retrieve the column value

I'm creating a procedure where it will iterate over every column in a table and get maximum value of every column. This is my code: DELIMITER // DROP PROCEDURE IF EXISTS min_max // CREATE PROCEDURE ...
0
votes
1answer
92 views

Executing a stored procedure inside another stored procedure from a linked server

I'm having a problem and I don't know how to solve it, I have searched the web and found good advice but I can't work it out. This is the problem: I have a SQL Server instance running on my PC, and I ...
-1
votes
1answer
30 views

Unable to declare user-defined variable in MySQL

I'm creating procedure called min_max to find minimum and maximum for every column in a table. But I'm encountering an error for declaring the variable. See below: DELIMITER // DROP PROCEDURE IF ...
1
vote
2answers
38 views

Trigger function does not exist, but I am pretty sure it does

I have a procedure: create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp) as $$ begin update sensor set last_read = read_time where ...
3
votes
2answers
148 views

Way to obtain full list of column dependencies for stored procedure?

I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a ...
0
votes
1answer
50 views

output parameter in SQL

I'm trying to pass a new ID onto another procedure but keep getting a "too many parameters" error. CREATE OR ALTER PROCEDURE dbo.Process_Insert @Name VARCHAR(50), @IntOnly INT, @...