Questions tagged [stored-procedures]

A subroutine available to applications accessing a relational database system.

0
votes
0answers
8 views

Outputting a System.Object (Table) to SSIS from a stored procedure

Currently we have the some SQL in a Execute SQL task in SSIS and it is running dog slow. I have moved the SQL out into a stored proc and optimized the performance with a few indexes and temp tables. ...
-5
votes
0answers
20 views

How to Display image from database which is of the type bytes using stored procedure? [on hold]

i have been trying to insert image into database as bytes using stored Procedure and to display.I couldnt find the solution .Somebody please help me with this.I am using Stored Procedure. [HttpPost][...
0
votes
0answers
19 views

MYSQL prepare statements procedure which updates a variable number of columns of a table

I need to create a stored procedure which uses prepared statements, whose purpose is to update a table. The thing is that I don't know beforehand which columns of the table will be updated. It can be ...
0
votes
0answers
38 views

Stored procedure always returns same query

I tried to write a SQL query to get results with some parameters, but when I send parameters, it doesn't work. It always returns the last query in if else condition statement. SET ANSI_NULLS ON GO ...
0
votes
1answer
57 views

Convert LINQ stored procedure to SQL inline query

I'm new to LINQ and I would like to convert code execution of stored procedure (executed in C#/LINQ) into SQL code. I want to execude those in SSMS. try { using(var context = new ...
0
votes
0answers
15 views

SQL server python service-“DECLARE @myscript NVARCHAR(MAX) = N' ” gives syntax error, what could be the possible mistake?

I am using SQL Server 2017 with in-database Machine Learning with python scripts with a goal to run Machine Learning models. While following a tutorial on PluralSight 'Getting started with Python on ...
1
vote
3answers
26 views

Need to concatenate to a field value, after checking if value [isnull]

I have a stored procedure where I'm pulling data from a table. The value in the field can either be NULL or a number value (ex: 15). I am hoping to display this value with a $, however I'm finding ...
0
votes
1answer
22 views

DB2 String Tokenizer

I'm using a string tokenizer code as below - had it from my earlier question. It works fine without the QUALIFIER but soon as I add QUALIFIER KGT (schema name) to it - I get an error: THE CLAUSES ...
0
votes
1answer
43 views

How to use dynamic table of every month in the insert statement while one static value we are passing in one column

In one Stored proc, I used insert into statement i declare one variable as v_tbl_name which is dynamic. I am going to use this dynamic table in insert into block while we are passing one static value ...
-1
votes
2answers
48 views

How to find the name of the database from the given table name, using stored procedure?

I am newbie here. I have many databases in my SSMS, so I need to find the database name using the given table name using stored procedures. And I am not good at writing SP's and handling errors. I ...
1
vote
2answers
42 views

C#. NPOCO. Error: “Incorrect syntax near '&'. ” when trying to join

I have this stored procedure, created by NPOCO after the next linq query: var componentVarians = _repository.Get().Include(x => x.Component) .Where(x => x.IsActive == true & ...
0
votes
2answers
21 views

Get the body of a stored procedure from PostgreSQL using JDBC

I can't get a stored procedure's body from Java code using JDBC. Which methods should I use to solve this? I found the metadata of all stored procedures using this code: ResultSet resultSet1 = ...
-1
votes
0answers
17 views

Error Code: 1265. Data truncated for column 'id' at row 1 ? ? ? No data inserted after stored procedure is run sccesfully

I am working on a stored procedure that takes data from my tmp table and insert data into the permanent table. I run my SP in the query editor with success, but when I look at the permanent table. It ...
-2
votes
0answers
16 views

Need help to write an Oracle stored procedure for generating report of the columns and data types in the database

I need help to write an Oracle stored procedure that will accept the Oracle database name as input along with the connection parameters, and generate a report of all the columns and their ...
1
vote
1answer
24 views

How to assign a variable to the execution of a stored procedure?

I want to assign the value returned from the sp_formCreateEventID stored procedure into a new variable (@eventId). I initially thought this was the way to go. This solution is also in line with the ...
-2
votes
1answer
22 views

“Invalid column” when called from another stored procedure from .Net

I've added a call to a SQL Server stored procedure (let's call it "child") at the end of an existing stored procedure ("parent"). Both child and parent (which, again, includes child) run successfully ...
0
votes
1answer
20 views

MySQL run query if previous query is not null

I am trying to run two queries where the second query will run only if the first is not null. Something like: if((select * from abc where id =1)!=null) select * from cde else exit; ...
0
votes
0answers
19 views

MySQL case based on query result

I have two tables Question and Circles whith the following structure **Circle** id int name varchar **Question** id int question varchar circle int Here question.circle is a foreign key to circle....
-1
votes
1answer
38 views

How will I achieve the following requirement using sql function or query

How will I write a sql function for the below requirements I have a temp table table1 with all columns of int type table1 scoreA | scoreB | scoreC | classA |classB | classC | ...
1
vote
1answer
32 views

PL SQL Procedure from Trigger

I created procedure GETDEL that select name from students , but when i try to call this procedure in trigger it says i gave wrong types of argument to call I have been trying to pass (records) or (...
2
votes
1answer
27 views

Stored procedure DateTime 'Parameter not found in the collection' error

I have a c# .Net program that utilizes the MySQL.Data library to connect to my MySQL database hosted in AWS. I have a stored procedure called "get_dates" in my database that is defined as follows: ...
0
votes
0answers
38 views

It is possible to generate SQL script to create only stored procedures for EF mapping?

I have a SQL Server database project with definition of tables, relations, keys, indexes, security, publish profiles, etc. and I also have an ASP.NET Core MVC project. The issue is that I would like ...
3
votes
3answers
62 views

How to call a SQL Server procedure with both input and output parameters?

I'm trying to write a procedure which returns values on the basis of a select query result. Like find the ID of a user, use first name and last name to locate a user ID, and return the ID as an OUTPUT ...
0
votes
1answer
31 views

PLS-00306: wrong number or types of arguments in call to error

I get this error when calling the procedure. I am trying to do this practice. Create a procedure called find_region_and_currency to fetch and return the currency and region in which a country is ...
0
votes
1answer
24 views

GetDate Function

I have a stored procedure that runs once a month for the whole previous year. So for example 5-1-18 to 5-1-19. I am going to create a job that fires it off at the first of each month. I need my sproc ...
-1
votes
0answers
13 views

SSIS package from stored procedure with multiple results [duplicate]

I have a stored procedure which returns two tables. I would like to get those tables and write them into excel sheets. The stored procedure uses temp tables, so there are errors while retrieving ...
0
votes
0answers
21 views

Call procedure having multiple output parameter with spring JPA

I am trying to call a procedure having multiple output parameter with spring JPA while I do have code for a single out parameter, given below @NamedStoredProcedureQueries({ @NamedStoredProcedureQuery(...
0
votes
1answer
47 views

Dynamic SQL for checking stored procedure existence

I want to execute this query using dynamic SQL, but it doesn't affect the @Exists variable. What is the problem? Purpose: check whether a stored procedure with name @TransformProcName exists in the ...
0
votes
1answer
12 views

How to fetch data from AWS RDS in AWS Glue job script and transform the data accordingly and insert it back in aws rds?

I want to insert data from s3 bucket csv through AWS Glue but insertion of data depends on the data stored in AWS RDS(like if that data already present in then do not insert or if already present and ...
0
votes
0answers
22 views

How to pass multiple selections of string from a reporting tool to DB2 as a parameter?

I'm trying to run a report using Free-Form SQL in Microstrategy using stored procedures.This involves passing the prompt or selection answers from the reporting tool to the DB2 database using ...
1
vote
0answers
36 views

Oracle Stored Procedure takes much longer from MVC UI than it does in SQL Developer

So, I'm trying to implement a call to a stored procedure through my ASP.Net MVC web application. Calling the stored procedure isn't the issue though. When I manually run the procedure in SQL Developer,...
0
votes
0answers
22 views

Mysql 5.7 Issue in declare handler for duplicate key

We are facing an issue in declaring handlers in MYSQL 5.7 stored procedure. Using the documentation present in https://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html, I'm trying to create ...
-1
votes
0answers
13 views

Issue with stored procedure with multiple values

Getting below error in stored procedure. Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL'...
0
votes
1answer
23 views

Is there a way to activate a trigger from inside a trigger with a stored procedure?

I have an 'A' table that has a trigger to extract information from a document that is in pdf that was uploaded to my program. However, sometimes this pdf file has more than one document. So I did a ...
-1
votes
0answers
32 views

How to get a input check into a stored procedure

I'm busy makeing some homework for school. Now I need to implent a input check into my stored procedure. My stored procedure inserts into a table for cleaning. But i need a check in the procedure that ...
-1
votes
0answers
14 views

mysql stored procedure takes too much time on “checking permission”

I have a trigger running which calls a stored procedure. Things work fine except sometimes it stuck on "Checking permission". Any kind of help is highly appreciated. my code: CALL `insert_tags_new (...
-1
votes
0answers
27 views

MYSQL: Stored procedure loop exiting prematurely

I have the following stored procedure: DELIMITER $$ USE `my_database`$$ DROP PROCEDURE IF EXISTS `integrateBilling`$$ CREATE DEFINER=`user`@`server` PROCEDURE `integrateBilling`(IN bill_run_date ...
0
votes
1answer
40 views

ORA-12728: invalid range in regular expression Inserts

The error arises with the insert of the table clients and only in my computer, since testing with others if it manages to load it. Use sqldeveloper 18.2 and oracle database express edition 11g. Table:...
1
vote
2answers
43 views

The data reader is incompatible. A member of the type, 'RoleId', does not have a corresponding column in the data reader with the same name

Stored procedure works and deletes what I want but I still get this error after deleting: The data reader is incompatible with the specified 'AMSIdentity.Models.RemoveRoleFromUserViewModel'. A ...
0
votes
2answers
30 views

I want to generate new id based on previous id in sql stored procedure

I have one table which will be stored qno as 'res/12-19/001'. I want to Increment it by 1 but when I increment it by 1 the new qno will be res/12-19/2. I want it to be res/12-19/002. I am using ...
-3
votes
1answer
42 views

How will I achieve the below requirement with sql query

I have a following tables table1 table1_id name 1 abc 2 cde 3 efg table2 table2_id(...
0
votes
0answers
17 views

Mysql - Insert Millions of rows in chunk

We need to insert 2 million rows in a table and what we are thinking to insert rows in a batch of 10000 rows each time, so that Mysql query won't excedded its size. We are planning to insert 10000 ...
0
votes
0answers
15 views

Mysql - Fast way to insert Millions of rows using Store Procedure [duplicate]

We are in a business use case in which we need to insert Millions of rows based on coming inputs in one go under actual business tables. I have an idea that we can use for loop over the input list ...
0
votes
0answers
37 views

Getting error while sending date in stored procedure parameter “error converting data type nvarchar to date” [closed]

I am passing a datetime parameter from C# code to a SQL Server stored procedure. But I'm getting an error Error converting data type nvarchar to date Strange behavior is that same code is working ...
0
votes
1answer
23 views

Stored procedure execution in background?

how to run mysql stored procedure behind the scenes without creating a loading website to wait for the results of the procedure to finish? DELIMITER $$ USE `simbakda_sensus`$$ DROP PROCEDURE IF ...
0
votes
1answer
21 views

EDMX files and Changes in the database

I am working with a big WPF app and this app is using .edmx files to connect to the tables, views and procs n the database. We are in the process to increase some columns in some of those tables and ...
0
votes
1answer
55 views

Pass a list/array in DB2 stored procedure

SELECT cc.clientid FROM customer_client cc GROUP BY cc.clientid HAVING SUM(CASE WHEN cc.customerid IN (4567, 5678) THEN 1 ELSE 0 END) = COUNT(*) AND COUNT(*) = 2; I'm calling this query in a Db2 ...
0
votes
0answers
32 views

Multiple IF statement results into Temp Table [closed]

I am trying to apply sales tax correctly in the Invoice Detail table. There are several factors involved in determining the sales tax. Customer is Exempt State is exempt (does not required us to ...
1
vote
1answer
32 views

How can I alter procedure based on the information present in an excel file using SSIS and SQL

I receive an excel file with following info: Source_column_name,Source_table_name,Destination_column,Destination_table,Where_clause_Condition,Join_condition,Procedure_name,Created_date,Modified_date ...
0
votes
1answer
49 views

How to fix: Cannot insert the value NULL into column, insert failed

For school I'm busy writing a stored procedure on my database. Now I get an error when I try to execute it. I already tried looking on stack overflow for fixes but could not find anything that helps ...