The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
33 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
9 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
27 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
25 views

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

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
0answers
13 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
12 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
0answers
37 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
19 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 ...
3
votes
2answers
61 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, @...
0
votes
0answers
24 views

Inserting data using Gridview, only first row is entered, subsequent rows ignored [migrated]

I have a Gridview in my asp.net site that collect user entries and then submits those entries for insertion to my SQL table. Currently this process works but it will only insert data from the first ...
2
votes
2answers
45 views

Using Triggers in SQL Server

new to SQL, I'm looking for a way to setup a Trigger that will update Table B when a new row is inserted on Table A. For basic understanding here are some of the columns in my tables: TABLE A: +------...
0
votes
1answer
27 views

After new row INSERT statement, use newly created PK as FK in a second INSERT statement within the same Stored Procedure

Using SQLExpress -- I have a table Orders that contains a PK of order_id. I have a second table Items that contains model and quantity along with a FK of order_id. I am using a gridview to collect ...
0
votes
0answers
22 views

How to process Orphaned records in a data warehouse

I've been working on a Data Warehouse project where I need some help regarding how to process orphaned records (records with parent-child relationships). The warehouse is denormalized which means we ...
1
vote
1answer
24 views

Stored Procedure has too many arguments [closed]

Here is the code for my stored procedure, lots of things going on and lots of parameters being passed. I'm knew to sql and I'm sure there's a far better way of performing these functions but need some ...
1
vote
3answers
78 views

How to return the value from a query with SP_EXECUTESQL in a stored procedure

I have a stored procedure that generated a query string and then executes it with EXECUTE SP_EXECUTESQL @Query The query, e.g., is: Select Name, Age from tableA, so very simple. But how to get the ...
0
votes
0answers
31 views

How do I handle deadlocking on a Stored Procedure when running an SSIS package?

I am concurrently running (for example 25) the same SSIS package that retrieves data from a 3rd party platform. This data is then inserted into our Production database. In order to increase ...
1
vote
1answer
31 views

Drop multiple columns at the same time from a SQL Server table

I am currently looking some way to delete multiple columns at the same time from a given table in sql server. One way of doing that is to use ALTER statement as shown below: Alter Table [USER_DB].[...
2
votes
1answer
23 views

Stored procedure for selecting table rows from a date range used to populate two other tables

I have this two tables shipments_docs and dict_shops: CREATE TABLE shipment_docs ( doc_id INT PRIMARY KEY, doc_num VARCHAR(20) NOT NULL, doc_date DATETIME NOT NULL, shop_id INT NOT NULL ...
0
votes
1answer
14 views

UUID array declared in stored procedure, on access throws explicit type casts needed

I have a stored procedure where I have declared an array of UUIDs, on using that variable, it throws casting exception. create or replace function test.test_function(max integer, interval interval) ...
1
vote
0answers
24 views

Syntax error while using variables in stored procedure

This might look like a silly question so pardon me. I was trying to write a trigger in mysql and am constantly getting #1064 which, I believe is a syntax error. Here is the sql for the trigger: ...
0
votes
1answer
40 views

Run time fetch the value from a table and iterate these values and sent to another procedure

I am new to write store procedures in sql I want to use a store procedure into another store procedure Test is a store procedure which works fine but in usage store procedure I want stock is a table ...
-1
votes
1answer
25 views

How to proceed with a transaction even if an AFTER UPDATE trigger fails

I have this stored procedure which makes some GET request: CREATE procedure [dbo].[alert_open_ordr]( @order_number varchar(max) ) as DECLARE @status int DECLARE @responseText as table(responseText ...
4
votes
1answer
124 views

Performance issue with Top, Order By and Where condition with more than one million rows involved

I have a query which looks something like below: Declare @CompanyID as int = 10; Select Top 50 UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, S.ID as SiteUserID from ...
0
votes
0answers
17 views

Error Handling in MySQL Events

I have an event that runs every 15 minutes like this: CREATE EVENT test_event_02 ON SCHEDULE EVERY 15 MINUTE STARTS CURRENT_TIMESTAMP + 1 HOUR ENDS CURRENT_TIMESTAMP + INTERVAL 2 YEAR ON COMPLETION ...
3
votes
1answer
198 views

Incorrect syntax near '+' in stored procedure sql server

I am writing a stored procedure which will accept a parameter and send mail to some recipients. I am using the sql builtin stored procedure. here is the code create proc send_mail_admin(@usage_data ...
1
vote
2answers
40 views

Trying to trace error in Stored Procedure using XEvents

Here is the situation: I have a SQL server ETL application with a stored procedure that is inserting/updating some data into a table. The table has 1 INT column (it is an identity column and there ...
2
votes
3answers
165 views

T-SQL Stored Procedure Returns Before Completing

We are running into a very curious error. We have two production SQL Servers. We call a stored procedure on one (call it Server_A) that calls a stored procedure on the other (Server_B). The stored ...
0
votes
1answer
40 views

How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259?

I got an error while trying to send an email: Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259. Here is my query: DECLARE @Table TABLE ( ...
1
vote
1answer
94 views

Why is my auto-restore procedure not dropping the databases when I expect it to?

I wrote a stored procedure to loop through my databases in a cursor, restore the most recent backup, run checkdb, drop the restored database, then move on to the next one. I have found that during the ...
0
votes
1answer
37 views

How to add multiple query results into one csv file?

By the help of the members of this community I have created a script which shows recent T-log backup for each database in the server. Instead of sp_MSforeachdb I used a customized script written by ...
1
vote
0answers
28 views

WEHRE IN array values using stored procedure

I have a select query that uses a WHERE IN clause in a MemSQL database. The list of values searched in are a result of another select query. Before, I inserted the values for that clause as a string ...
0
votes
1answer
50 views

How to exclude tempdb from sp_MSforeachdb?

Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list. exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup....
0
votes
2answers
43 views

Fill in missing dates for a vacation schedule

Have an issue that has been driving me nuts for the past week and can't seem to get my head around it. I have a stored procedure that grabs a bunch of vacation requests from our scheduling system. I ...
0
votes
0answers
22 views

MySQL: how to update a table based on changes to another table with trigger or stored procedure

Because of the limitation of a wordpress plugin I am using I cannot update tables from the website when they are created from JOIN statement. So I created a bigger table with data of all tables. But I ...
1
vote
2answers
109 views

How to pass a table-valued parameter to sp_execute_external_script?

I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit ...
3
votes
2answers
41 views

How can I determine which permissions needs to be assigned to the newly created login?

We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use ...
0
votes
1answer
25 views

Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL

I have this statement which generate a random string: SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') FROM ...
0
votes
2answers
88 views

Specifying Order of Executing Stored Procedures

Issue: The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error: Msg 2627, Level 14, State 1, Procedure ...
0
votes
1answer
48 views

insert results from store procedure with parameters into table

I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this? DECLARE @RC int DECLARE @startweek varchar(20) DECLARE @...
4
votes
1answer
117 views

Inner procedure is rolled back but outer procedure is still committed

I have an outer procedure which calls 5 different inner procedures something like Parent procedure(Tran/try/catch) Inner sp1 (Tran/try/catch) ' ' Inner sp5 (Tran/try/catch) But in this case, if one ...
0
votes
3answers
36 views

MySQL: list users which have been granted EXECUTE permission on a given procedure

I would like to audit my database to check which users have been granted individual EXECUTE permission to which procedure. I would like to use this information to be able to save GRANTs when changing ...
0
votes
0answers
45 views

MS SQL giving separate result on same parameters using application & query editiors

I have a SQL stored procedure which gives out result based upon the days passed in parameters. I miss the weekend day (Saturday) from result if I call the stored procedure from my application but I ...
1
vote
1answer
18 views

Running .sql files in postgres (structured programs)

In oracle I could create .sql files and run them with a structure (like a stored procedure) with begin declare end; I did some tests, because I need to run scripts to fix problems, not is ...
0
votes
2answers
98 views

SQL SERVER: How to get the stored procedure text EXCLUDING comments?

I need to find all the stored procs that use Transactions, as I want to enable transaction abort to those procedures. However, --I didn't do this; it's inherited-- many of the stored procedures ...
0
votes
0answers
47 views

Errors with trying to run a stored procedure with local file output

I'm trying to run a stored procedure that I'm passing some variables to. The output is being writted to a CSV file on a local file share. The CSV file gets created, but I'm hitting some errors ...
0
votes
0answers
41 views

Syntax error in store procedure mysql

BEGIN DROP TEMPORARY TABLE IF EXISTS tblEmployeeAttendance; CREATE TEMPORARY TABLE tblEmployeeAttendance AS SELECT emp.emp_id,emp.emp_name,x.attendance_date, CASE WHEN x.CheckIN ='0' and x.Checkout ...
0
votes
0answers
31 views

How to recalculate and adjust the entire row of time in and time out in sql server?

In SN = 1, I have an original schedule of 09:15:59 with my first time in is 09:50:57 so that I am late about 34 mins. In SN=2, 13:27:05 is my time out. In SN=4, I time in again of 15:40:13 that gives ...
0
votes
1answer
46 views

Execute SSIS procedure from stored procedure using non-privileged SQL login

I have a web app running on a different server that uses a SQL based login (not a windows based login) to connect to the SQL server. That app is calling a stored procedure that is located in a ...
1
vote
1answer
85 views

Parameter Sniffing - Running query in code vs in management studio

I just had a problem at work where a stored proc, being called by some C# code, was very slow. When I ran the same stored proc with the same parameters in Management Studio, it was very fast. After a ...