Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

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.

0
votes
0answers
45 views

Stored procedure executable only after shrink database

I am using SQL Server 2005 (I know...it is old!) and I wrote a stored procedure where the main part makes a few JOIN and OUTER APPLY between tables with 500.000-1.000.000 rows table, then INSERT the ...
1
vote
1answer
43 views

alternatives to passing temp table as a parameter to a stored procedure

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure: USE MASTER GO ALTER PROCEDURE dbo.sp_getDBSpace @Radhe sysname=null, ...
-2
votes
1answer
22 views

Error in stored procedure on Phpmyadmin [on hold]

I have created this simple StoredProcedure in the Phpmyadmin SQL console but I keep getting syntax errors. I have looked at similar created StoredProcedures but can't seem to figure out what the error ...
0
votes
0answers
27 views

Run query dynamically in store procedure oracle

Problem Select all the tables of database where column match than pass table name to next query using loop. If column name and column values matches than return true and exist for loop CREATE OR ...
0
votes
0answers
17 views

DDLTrigger_Audit error “The user does not have permission to perform this action”

I have a DDLTrigger_Audit enabled in my database when I try executing a stored procedure having Drop and Create index statement in it fails with the below error. database "XXX" failed on Jan 17 2019 ...
-1
votes
0answers
35 views

Not able to execute xp_cmdshell from a proc using invokeSQL command [on hold]

I have a powershell script that execute a SQL script remotely on a SQL server using invoke-SQL command but the problem is it is not able to execute a SQL script contains a stored proc that has a task ...
5
votes
0answers
58 views

Do I need a “downtime” to update a stored procedure? (Due to “definition changed” error)

I always thought that updating a stored procedure was something I could do while users were actively using the system. But I am currently testing out an update to a stored procedure and while a long ...
3
votes
1answer
91 views

Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower?

I have a Stored Procedure that I have written that deletes rows from a table in the database. When I will run the Stored Procedure in the Production environment it will run whilst another program is ...
0
votes
3answers
67 views

Slow performance matching columns to lists of values

I've made a stored procedure to get all results which match specific students and objectives. CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY AS SELECT ...
-2
votes
1answer
53 views

What difference between stored procedure and query in application [closed]

What difference between stored procedure and query in application. Performance , network load, server load and speed Thank you very much
0
votes
1answer
75 views

sp_DatabaseRestore msg 50000

I used Ola's script to backup my database (SQL Server 2017) and now I'm trying to restore it using Brent's sp_DatabaseRestore. Currently trying to do a Full restore only. Here is my code, written ...
0
votes
2answers
111 views

Sql Max value from multiple columns in Computed Column

In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this? https://www.mssqltips.com/...
0
votes
2answers
47 views

Multiline UDF or Stored Procedure for SSIS Data Source

We are creating an SSIS Data source with complex code. I read MultiLine Table Functions are slow. Would utilizing a stored procedure produce any faster results when streaming data from one place to ...
0
votes
0answers
27 views

Primary key error when running in C# but not SSMS

I have written a stored procedure to insert a record into a table that is used with our ERP system. When a record is written to this table there are triggers on the table that were written by the ...
0
votes
1answer
47 views

How to create stored procedure that returns query result?

I have query that has a lot of columns (about 30) and I would like to place that query in stored procedure. The reason behind that is because this query is used in multiple places in the system. There ...
1
vote
0answers
73 views

Improve parallel running of bulk copy insert and stored procedures

I have a Java message listener, which upon receiving a message starts bulk inserting thousands of records into a table. After that it calls a few stored procedures sequentially that make some updates ...
0
votes
1answer
31 views

What does “_localserver_view” mean after an MSDB Database table

What is equal to the database table [msdb]. [dbo]. [sysschedules_localserver_view], which contains the suffix _localserver_view? I am having a permissions problem with a [msdb].[Dbo].[...
0
votes
1answer
24 views

MySQL syntax error 1064 near IN stored procedure

an aspiring DBA Here. I'm trying to create a stored procedure for a query I'm running. It is a memsql server which is MySQL based. The procedure is as such: delimiter // CREATE PROCEDURE ...
0
votes
2answers
48 views

How to efficiently handle querying for different combinations of optional parameters

Consider the following Tables: Table: Parts part_id INT PK part_number VARCHAR Table: Jobs job_id INT PK job_number VARCHAR Table: Job_Parts part_id INT FK PK job_id INT FK ...
0
votes
1answer
43 views

Find if Stored Procedure Error

Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch? We have a lot of legacy code 200+ stored procedures. I have seen @Output method and ...
0
votes
1answer
17 views

How to optimize this MySQL routines [closed]

Using MySQL 5.7.22 Run pt-query-digest on slow query log # Query 6: 0.00 QPS, 0.00x concurrency, ID 0x5863FD9F47316C48AE1FBD931F05AA56 at byte 9577338 # Scores: V/M = 2.36 # Time range: 2018-10-...
0
votes
2answers
46 views

Running SSIS jobs sequentially in T-SQL

We are loading ETL data in two parts, first with an SSIS package and then stored procedure. In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 ...
1
vote
1answer
57 views

How to format Procedures for complex searches

So I am trying to create a way for users to search through data on my SQL Database. Right now I've been getting away with using a single view using a lot of Joins: CREATE VIEW PartsData AS SELECT ...
2
votes
2answers
249 views

Create stored procedure that contains newlines with a oneliner sql statement

I want to put code in a single line that will create a stored procedure that contains newlines. Is that possible? Do I need to use sp_executesql? How do I escape newlines in a sql statement? How do I ...
-1
votes
0answers
31 views

Foreign key referencing

I am making an application where you can select a user from the User’s combobox and a role. When the Insert User and Role button is clicked, the userid and roleid is added to tblUserRoles. The ...
0
votes
1answer
29 views

Delete using id's not found in loop - MySQL

I have a stored procedure where I loop through an array passed as an argument, update the values provided, then delete the missing values using an id passed in the given array. /* edit the ...
2
votes
2answers
48 views

availability groups - checking for primary server - cannot call a procedure with parameters

In my servers that are involved in alwayson availability groups if there are jobs running, inside the jobs I always test whether the current server is the primary in the availability group. So the ...
0
votes
1answer
37 views

How to reuse dataset in that stored procedure

First, I need to get a dataset then I want to use that dataset more then one time. SELECT @FullTimeEmpResult = ( select * from emp where type="FullTime" ); SELECT * from @FullTimeEmpResult ...
1
vote
1answer
61 views

SSIS Data Source: Embedded Queries, Stored Procedures, or Functions

We are moving data from OLTP server into Data Warehouse server, utilizing SSIS to conduct this. When writing queries from the OLTP Data Source, it it better to utilize? 1) Inline Embedded queries 2)...
1
vote
2answers
39 views

Testing Geometry Polygon Data is Valid Using a Stored Procedure

We are using a SQL Server Spatial database with the following table setup: SRID of 27700 geometry_column of ogr_geometry geometry_type of POLYGON I am using a SQL Server stored procedure to perform ...
0
votes
2answers
41 views

Return multiples tables with procedure

I apologize if the title for the question doesn't match what I am asking what to do. I am not sure how to describe what I am trying to accomplish. I have the following tables in my database: Table ...
1
vote
1answer
36 views

Stored procedure body missing

I was going through one of our databases in order to answer a question from the boss, and I peeked at a stored procedure. As usual, I right-clicked it in SSMS, clicked Script stored procedure as... ...
0
votes
1answer
120 views

how to user table name as variable in mysql stored procedure

I create a procedure to set auto-increment index value to 1. But there I want to use table name as a variable. I am try it but it not working correctly. I know there have a lot of question like that ...
0
votes
1answer
201 views

if exists, update else insert, with cursors in stored procedures only returns 1 row

I have 2 tables api(columns - api , api_context, api_id -->primary key) and api_request_summary(columns - api_context). i need to insert the number of times the api_context relevant to a particular ...
0
votes
1answer
40 views

Problem with database migrated from SQL Server 2005 to SQL Server 2014

First of all I want to apologize for my English, I hope you understand me correctly. I have a database migrated from a SQL Server 2005 in a SQL Server 2014. Well, in addition, I have a .NET process ...
0
votes
2answers
32 views

Dynamic query in Stored Procedure over table values

CREATE TABLE `Slot` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `starttime` datetime NOT NULL, `endtime` datetime NOT NULL, `shift_Id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=...
4
votes
1answer
146 views

SQL Server Data Warehouse stored procedure location

We are building a Kimball data warehouse. We have a Staging Database, and Final Datawarehouse. We have a stored procedure which creates the table CustomerTransaction. Where is the best location to ...
1
vote
1answer
41 views

Avoiding code duplication for counting and selecting the same resultset

I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (...
-1
votes
0answers
34 views

Executing stored proc in a SQL Agent Job fails on secondaries within a High Availability group with a sys.fn_hadr_is_primary_replica guard

I have a High Availability cluster of two servers. I have the same job setup on both servers within the cluster. The job has a single TSQL step, which is executed against master. The TSQL is as the ...
-1
votes
2answers
83 views

Returning a SQL query as one line of data

Apologies in advance, I have minimal experience in the SQL realm. One of our systems delivers data in a format that places individual lines items from a single record into separate rows. An example: ...
1
vote
1answer
35 views

Log bad call to stored procedure

I have an external application making a stored procedure call into a SQL Server 2017 database, via a normal database connection (PHP library (PDO?) of some form using the server, database, user, ...
0
votes
1answer
26 views

How to accomplish such insert-or-update strategy?

Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better. I've stumbled upon a business requirement that's kind of hard for me and I was ...
0
votes
1answer
23 views

In which context does a procedure run when called from another context?

Envisage: use databaseA; go exec databaseB.dbo.spProcedure(); go Does the procedure execute against databaseA - the context from which it is called, or databaseB - the context in which it exists? ...
0
votes
1answer
123 views

cross database ownership - The server principal is not able to access the database under the current security context

I have this script that set a bunch of databases to the same db owner, and set cross database ownership on at database level select database_name=name, db_owner_name=suser_sname(owner_sid), ...
-2
votes
1answer
27 views

The result from Print (@SQLquery) differs from stored procedure exact result set

This is my query ALTER Procedure [dbo].[OpenExcel] @FileName Varchar(2000), @ExcelName Varchar(2000), @SheetName Varchar(100) , @Header Varchar(50) , @...
0
votes
1answer
61 views

Help to decrese query time in huge table of 100M+ rows on Ms-SQL Server

I am trying to get data using offset limit concept as follows: CREATE PROCEDURE BaseUserIndex @Offset int, @Limit int, @SortCol int, @SortDir nvarchar(10), @Search nvarchar(255) = ...
11
votes
5answers
2k views

How do I limit a SQL stored procedure to be run by one person at a time?

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time. While this ...
0
votes
1answer
76 views

The datediff function resulted in an overflow. But should not

I have this stored procedure in SQL-Server 2014 USE [provbank] GO /****** Object: StoredProcedure [dbo].[GetHBSeconds] Script Date: 2018-10-05 08:40:07 ******/ SET ANSI_NULLS ON GO SET ...
1
vote
0answers
21 views

Stored Procedure AddDate function and validation errors

I am trying to create a stored procedure and testing some basic concepts. For the last few hours, I am getting errors with this procedure, in terms of syntax. This is in MySQL (MariaDB). use ...
1
vote
1answer
61 views

Changing stored procedure parameter results slow stored procedure performance

I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to ...