Questions tagged [stored-procedures]

10928 questions
0

votes
2

answer
32

Views

How to pass string with single quotes to Sql Stored Procedure

i'm using a stored procedure how can i pass a string like 'White','Black','Green' i'm trying to execute procedure like var data = new List(); string colorstr = ''White','Black','Green''; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string sql = $'exec G...
Faisal
1

votes
1

answer
44

Views

Optimize A Complex Query

The below procedure taking 9 seconds to execute, and the primary table has only 30000 records. ALTER PROC [dbo].[TransactionReport_NewWork] @StartDate datetime = NULL, @EndDate datetime = NULL , @Mid varchar(50) = NULL -- varchar(5000)=null , @BatchNo var...
-2

votes
1

answer
34

Views

SQL use in query for comma separated string

I'm using a stored procedure and I pass it a string with comma like this: Apple,Banana,Orange I want to use this string inside an IN clause, but I don't want to use SELECT value FROM STRING_SPLIT(@MyStr, ',') How can I generate string that I can use like SELECT * FROM MyTable WHERE Fruit IN (@MyS...
Faisal
0

votes
1

answer
33

Views

MySQL stored procedure with a cursor

From the table 'redis-state' I want to get data with cursor in table and written this procedure, but I get an error declare not founds 1064 error code at line 10. Purpose is to print the table all rows. One print for each entry in table. [![enter image description here][2]][2] CREATE PROCEDURE redi...
bilal shakir
1

votes
2

answer
28

Views

How to order a query by a field that is not being selected

I am a nwbie to SQL Queries and Stored Procedures and I am trying to order a query where there is 3 inner joins but I get this error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. here is the code of query that I have tried: SELECT DISTINCT P.ProductId, PID.IndexId F...
mmffggss
0

votes
1

answer
20

Views

Finding correct shift based on time in SQL Server stored procedure

I have 8 hrs shifts like this in my table Shift and shiftstart and shiftend datatype as time(7). ShiftNo ShiftName ShiftStart ShiftEnd IsNextDay IsBothNextDay -------------------------------------------------------------------- 1 Shift1 7:00:00 14:59:59 0 0 2 SHift2...
ssd
0

votes
0

answer
16

Views

How to Change the title of SQL query according to parameter passed on stored procedure?

I have a stored procedure which accepts value @OrderType and if Ordertype is Production i want heading as ProductionDate and if it is TakeDown I want heading as TakedownDate. DECLARE @OrderType nvarchar(50); SELECT CONVERT(VARCHAR(10), FromDate, 101) AS [PostingDate] FROM table //if ordertype i...
Biraz Dahal
0

votes
0

answer
4

Views

How to put in progress bar in the vb.net coding when execute a stored procedure

Im new to the .net programming, I just want to try and help my company improve the system. I want to show the progress bar when i click the compute button, the compute button is execute the stored procedure. I have try to search online, youtube for create the progress bar, but unfortunately that is...
Jackson
1

votes
1

answer
31

Views

Cannot execute the unique identified input parameter in stored procedure throws error

When I execute the stored procedure that has the first input parameter of type uniqueidentifier which is the primary key column in a table that inserts the values into, it is throwing the below error. I couldn't find whats wrong with this input or stored procedure. EXEC @return_value = DbInsert @...
Learner
1

votes
3

answer
54

Views

Retrieve data from SQL Server with AND/OR when some parameters may be left blank

I am using a stored procedure to retrieve data from SQL Server. The application allows the user to search by several parameters. There may be times when the user needs to leave one or more fields empty. For example, they may search by last name, zip code, race; or they may search by last name and z...
Shane
1

votes
2

answer
48

Views

Oracle SQL - How To Loop Through Records Using Pagination?

I'm using an Oracle database where I have a table containing 20 records CREATE TABLE MyTable ( MY_RECORD_ID NUMBER PRIMARY KEY, SOME_DATA_1 VARCHAR2(200), SOME_DATA_2 VARCHAR2(200) ); INSERT INTO MyTable VALUES (1, 'BLAH1', 'BLAH2'); INSERT INTO MyTable VALUES (2, 'BLAH1', 'BLAH2'); INSERT INTO My...
Space Cadet
1

votes
1

answer
32

Views

How to skip the max function which has only one entry when i do a group by in SQL Server

I have a requirement where I do a group by the table Table Name salary ------------ abc 10000 abc 1000 def 100 Query: select max(salary) from table group by Name Result: abc 10000 def 100 I don't want 'def' to be displayed since it's a single entry in the table. How can I achieve this...
NAGARAJA H I
1

votes
1

answer
100

Views

JDBC CallableStatement - call multiple stored procedures in one shot

To call Oracle's stored procedure using only simple JDBC in Java we could do something like this: con.prepareCall('{ call ? = my_procedure(?) }'); callStm.registerOutParameter(1, Types.INTEGER); callStm.setInt(2, -100); callStm.execute() I want to know is it somehow possible to call more then one pr...
jsosnowski
1

votes
1

answer
58

Views

How to combine query results?

I have three queries that are tied together. The final output requires multiple loops over the queries. This way works just fine but seems very inefficient and too complex in my opinion. Here is what I have: Query 1: SELECT t.type_id, t.category_id, c.category_name, s.type_shortcode FROM type t I...
espresso_coffee
1

votes
2

answer
5.3k

Views

Make Directory Part Dynamic In Call To BFILENAME function In Oracle 10g

I am trying to open an existing physical file from file system in a procedure in Oracle 10g. The bfilename function requires a directory and path. I want to pass on the directory such as 'c:\abc' directly to the procedure, i.e. I don't want to pass a directory object. The reason is, the directory ca...
Atif
1

votes
1

answer
37

Views

Sql stored procedure combining like materials and adding lengths together

I have a SQL Stored procedure that looks through SQL tables, then gives me the material and its length. What I am wanting is the query results to only show the material 1 time, and have all the lengths be added to its material. I am including the stored procedure and a snapshot of the results I am...
ronnogal
1

votes
1

answer
28

Views

How to get MS SQL Stored Procedure return value In PHP with PDO?

Simple example CREATE PROCEDURE [dbo].[InsertLoginInPHP] @login as nvarchar(50), @password as nvarchar(50), @resultOK as int, @resultnot as int AS BEGIN SET @resultOK = 10 SET @resultnot = 1 IF EXISTS (SELECT * FROM Users WHERE Login = @login) BEGIN SELECT @resultnot as return // 1 END ELSE BEGIN IN...
Afqan Haciyev
1

votes
0

answer
385

Views

Java: how to execute stored procedure from other schema

Executing the procedure, isb.pkg_po_test.get_component, I have to get a cursor from remote database. Our schema is ISBREPORTER and we have limited access to the schema ibs. However, on Oracle SQL Plus the following is executed very well: SQL> var r refcursor SQL> exec isb.pkg_po_test.get_component(1...
valijon
1

votes
0

answer
199

Views

How to skip to next iteration if no results are returned in SELECT statement in Stored Procedure?

I have a case where I SELECT rows from one table and INSERT them to another table. However, I get Error: 1329 SQLSTATE: 02000 if the SELECT statement doesn't return any row. Can I do something like: CREATE PROCEDURE SP() DECLARE CONTINUE HANDLER FOR NOT FOUND; BEGIN ITERATE END BEGIN DECLARE cur1 C...
swdon
1

votes
0

answer
46

Views

Neither recordset or SP approach to validating salted hashbyte fields working

UPDATE: ASP page still always returns a '1', even when correct password is supplied, but when stored proc is run in SSMS, it works properly. I added SELECT statements for the fields being tested and modified the running of the stored proc to return a recordset when executing the command. Set rs2 =...
Jan Paul
0

votes
0

answer
12

Views

Full Text Search Using Multiple Partial Words

I have a sql server database that has medical descriptions in it. I've created a full text index on it, but I'm still figuring out how this works. The easiest example to give is if there is a description of Hypertensive heart disease Now they would like to be able to type hyp hea as a search term a...
Jhorra
-3

votes
0

answer
28

Views

How i can SqlDataReader from DataTable using Stored Procedure?

i have class called DataAccessLayer and contain this code : //Read Data From DataBase public DataTable SelectData(string stored_procedure, SqlParameter[] param) { SqlCommand sqlcmd = new SqlCommand(); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.CommandText = stored_procedure; sqlcmd.Con...
GMCadiom
0

votes
0

answer
4

Views

Printing Dynamic Sql with Parameter Values

I want to print the SQL Query which is used in the below stored procedure with parameter values.Currently it is printing the SQL Query without parameter values substitution while executing the stored procedure.It is printing the the parameters as it is(for example AND TP.F_LANGUAGE = @LANGUAGE) CRE...
Ram
1

votes
0

answer
83

Views

CLR Assembly in SQL Server Error “Timeout Exceeded” when executed in stored procedure, runs fine in new query window

I have this stored procedure, we'll call 'wrapper', that calls another procedure to build an XML message then executes a CLR Assembly to send that XML message to a web service. The web service should then return a message saying success or failure. The issue is, the assembly is exceeding its timeout...
John Donahue
1

votes
2

answer
66

Views

SQL Server 2008 R2 old version of stored procedure comes back

I have a stored procedure in my production environment that was originally written by someone that didn't have enough time to design it correctly. It was written with 9 or more joins to a database across a linked server connection. The stored procedure was taking more than an hour to run. About a...
RemonsterGrrr
1

votes
2

answer
890

Views

How to get n element from nested table pl/sql?

I have problem with the compilation of my stored procedure. create or replace type CartLine as object ( offeringId OfferingIdList ,productLine varchar2(50) ,equipment char(1) ,installment CHAR(1) ,cartItemProcess varchar2(50) ,minimalPrice decimal ); create or repla...
anton1009
1

votes
1

answer
614

Views

How to confirm the edit of user data using sweet Alert in ASp MVC

I am developing an ASP MVC web application using stored procedures via SQL Server. I am trying to establish a confirmation's alert (Sweet Alert) in order to confirm the update of the data for a logged user. The edit's code work perfectly without the confirmation. But once adding the confirmation ale...
Exact
1

votes
0

answer
930

Views

Spring SimpleJdbcCall Oracle sql/pl - Unable to determine the correct call signature - multiple procedures/functions/signatures

I have a stored procedure in a package which has it defined with three different method signatures in the same package. They vary by only a few parameters (each calls the next with a few default values). I've over-simplified the list of columns for the sake of brevity here. I have about 55 fields...
Josh Hull
1

votes
1

answer
37

Views

Generate table with define SQL Server

I have 2 tables to manage generate real table with table name and column: Table (TableID, TableName) TableColumn (ColumnID, ColumnName, Datatype, Size) --(Size is size of nvarchar type, ID is auto identity(1,1)) Table: TableID TableName ---------------------- 1 TopCity TableColumn: Colum...
O Thạnh Ldt
1

votes
2

answer
42

Views

Many-to-many that shows columns with null

I'm having some difficulty trying to figure how to adjust my query. I'm not very good at SQL queries as it's not my forte. Anyway, I'm not sure what I'm doing wrong. Here's my table setup. ID | Customer ---+------------- 1 | John 2 | Jane 3 | Steve ID | Assets ---+------------- 1 | RealEstate 2...
gdubs
1

votes
0

answer
154

Views

mysql exit with LEAVE exits the caller Procedure

In our mysql setup, we have a long running SP (stored proc) which runs for approximately 4 hours ( doing lots of data massaging.). This SP calls different SPs one by one and then they call some other SPs. SP1 ( main SP) -> some long running queries.. -> calls SP2 -> SP2 calls SP3 SP3 has this code...
keridotboy
1

votes
0

answer
46

Views

Performance issues while passing UDTT[] to postgres Function

I have created a function in postgres that takes a UDTT[] as an importing parameter, and want to eventually insert that data into a Table Example Udtt create type udtt_mytype as ( id uuid, payload int ); And then an example Function is something akin to CREATE OR REPLACE FUNCTION dbo.p_dothething(...
Andrew Moore
1

votes
1

answer
44

Views

store procedure with multiple string parameter out mssql

i'm having trouble retriving multiple parameter in store procedure here is my code enter code here SqlCommand cmd = new SqlCommand('getdataComport', cnn); cmd.CommandType = CommandType.StoredProcedure; // set up the parameters cmd.Parameters.Add('@ComName', SqlDbType.Text).Direction = ParameterDirec...
jhunkhan
1

votes
1

answer
37

Views

How to retrieve stored proc data without the word “table” in the return value using c# web api

I'm trying to pull some database rows to be consumed through an api using the code below: DataSet ds = new DataSet(); using (SqlConnection con = new SqlConnection('data source=.; database=GiveTradeApp; integrated security=true;')) { SqlCommand cmd = new SqlCommand('sp_get_products_by_user', con); c...
Mr.Google
1

votes
0

answer
46

Views

SQL Server Temp Table Creation For Stored Procedure that Returns Multiple Tables

I've been struggling with this all afternoon, so I'm hoping someone here can help me. I've seen several threads similar to this, but none of provided me with an answer to my exact situation. I'm using SQL Server 2016 and I have a stored procedure (I didn't write it and can't modify it) that returns...
sccrbrg
1

votes
1

answer
972

Views

How to call Oracle stored procedure with custom object as input parameter

In Oracle database I have defined type: create or replace TYPE person_type AS OBJECT (id NUMBER(10), name VARCHAR2(50), age NUMBER); and a stored procedure create or replace PROCEDURE add_person (in_person IN person_type) AS BEGIN INSERT into person (id, name, age) VALUES(in_person.id, in_person.nam...
Iwaneez
1

votes
0

answer
461

Views

Stored procedure error A .NET Framework error occurred during execution of user-defined routine or aggregate

I have a procedure that was working perfectly and suddenly without any updates it stopped, and I got this massage. I tried everything I could get or know and nothing Msg 6522, Level 16, State 1, Procedure CreateFileAsmoFormat, Line 0 A .NET Framework error occurred during execution of user-defined r...
HuMax
1

votes
1

answer
428

Views

SQL Stored Procedure with Input parameters with While loop

I have a code below that should insert records into the table but unfortunately this code foes not work in case multiple records are inserted or updated or deleted. How should I rewrite the code for procedure to loop through all the inserted / deleted records? And I do need to use that stored proced...
enigma6205
1

votes
2

answer
54

Views

Regarding this SQL query, how can I output list of 28/30/31 days according to user's choice of certain month and year

I've declared all the variables right here. declare @FromDate as datetime; declare @ToDate as datetime; declare @OperID as varchar(20) = 'OP1'; declare @Year as int = 2018 declare @Month as int = 1 set @FromDate = convert(date,convert(varchar,@Year) + '-' + convert(varchar,@Month) + '-01') set @ToD...
Muhd Ehsan
1

votes
0

answer
61

Views

What is best practice for building stored procedure with various parameters

i'm trying to build stored procedure which dynamically build me a query depends on provided parameters for initial requirements i had 2 parameters which are switchable, and 3rd one which is optional i did this PROCEDURE `myProcedure`(IN pred1 int(11),IN pred2 varchar(45),IN pred3 datetime) BEGIN DEC...
user902383

View additional questions