FAQ Database Discussion Community


Same SPROC with different names performs differently when called from C#

c#,sql-server-2008,stored-procedures
I have two identical stored procedures, but with different names. When I call Sproc A from C# I get a quicker overall response when compared to Sproc B. When I run each Sproc in SSMS and include the execution plan I get the very close to the same results. Any...

SQL - Stored Procedures - get values from subquery

sql-server,stored-procedures
I have a stored procedure and EXEC call like: EXEC Schedule_InsertReservations @ScheduleID = (SELECT ScheduleID FROM Schedule WHERE Job_No = 'ABC'), @ScheduleTaskID = (SELECT ScheduleTaskID FROM ScheduleTasks WHERE (ScheduleID = (SELECT ScheduleID FROM Schedule WHERE Job_No = 'ABC')) AND LibraryTaskID = 247), @ResourceID = (SELECT Vendor_ID FROM tblVendors WHERE Vendor_Name...

Delete multiple records from different tables with SQL stored procedure

sql-server,stored-procedures
I'm trying to create a SQL agent job that executes a stored procedure every 5 minutes or so, but I'm at a loss with part of the SQL syntax. I'm using forms authentication and I need to select all users that have not been active in the last 15 minutes...

SQL Syntax error #1064 cursors (mysql)?

mysql,sql,if-statement,stored-procedures,cursor
So my procedure looks something like this: OPEN tstamp_cursor; get_timestamp: LOOP FETCH tstamp_cursor INTO cSID, ctstamp; IF finished = 1 THEN LEAVE get_timestamp ; END IF; SET @MID = 0; SET @MX = 0; SET @MY = 0; SET @MT = 0; SELECT @MID:=m.vID, @MX:=m.x , @MY:=m.y , @MT:=m.timestamp FROM...

SSRS Average Time in Minutes and then Format to HH MM

sql,stored-procedures,reporting-services,formatting
I've seen a lot of questions about average time in SSRS but I'm pretty new to this and none of these questions address my specific need so any help would be appreciated. I have a duration field (Fields!Duration1.Value) which is pulling in a duration in minutes (example: 170). In my...

When executing stored procedures in VB.NET/SQL, are we supposed to add “EXEC” or not?

sql-server,vb.net,stored-procedures
I'm not sure what to believe anymore. When we are running our VB.NET SqlCommand off of stored procedures, do we add EXEC in our commands or not?! I get an error: Could not find stored procedure 'EXEC uspGrabAutoByYMM'. But then other people tell me you MUST put EXEC in there...

How to count and compare value of colume in the same table

mysql,sql,stored-procedures
I need to create procedure which will find the worst user in one table by counting status with 'P' and 'U' calculate ratio then compare it with other users, take that user id and find it in another table and write all user information that are in two tables. And...

Stored procedure for pagination is not working

sql-server,stored-procedures
I am implementing pagination in my asp.net application. For this, I have created a stored procedure to get records from the CommunityPost table. But this stored procedure is not working correctly. It does not return any records. My stored procedure is: ALTER PROCEDURE [dbo].[CommunityPostLoadAllPaged] ( @PageIndex int = 0, @PageSize...

MySQL Stored Procedure that compares each row's date field and updates based on curdate()

php,mysql,stored-procedures
This might be a noob question, but I tried googling about this issue and I haven't found any solution for it (I think). Anyway, I need to compare a date in a row from a mysql table to the current date and update the row depending on the results. I...

PL/SQL stored procedure which outputs values depending on user input value

oracle,stored-procedures,plsql
I have an Oracle table (sample): +----------------------------+----------------+-----------------------+ | DEPT | BUILDINGID | GEOMETRY | +----------------------------+----------------------------------------+ | Emergency Services AMB | 516 | [MDSYS.SDO_GEOMETRY] | | Emergency Services AMB | 287 | [MDSYS.SDO_GEOMETRY] | | Emergency Services FIRE | 283 | [MDSYS.SDO_GEOMETRY] | | Emergency Services FIRE | 460 | [MDSYS.SDO_GEOMETRY]...

Is it possible to add Stored procedure dynamically from hibernate?

java,mysql,sql,hibernate,stored-procedures
I want to add stored procedures dynamically from my Java code using hibernate. I did the following but this constantly results in error saying " You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use what...

Cast sql_variant into data_type provided as varchar

sql,sql-server,stored-procedures,casting
I have a following sql table: Types table -------------------------------------- |Name(varchar(50))|Type (varchar(50))| -------------------------------------- | Car | varchar(50) | | Apples | int | -------------------------------------- I am using another tables for storing values such as: Apples table: ---------------------------- |Value (providedType - int)| ---------------------------- |50 | |60 | ---------------------------- To insert values into...

Test tsql inserts before doing actual inserts?

c#,sql,asp.net,tsql,stored-procedures
I have an asp.net application that inserts to several tsql tables. The format is the following: Car myCar = new Car(); myCar.InsertNewCar(); //Makes SP call usp_InsertCar() Truck myTruck = new Truck(); myTruck.InsertNewTruck(); //Makes SP call usp_InsertTruck() Customer myCustomer = new Customer(); myCustomer.InsertNewCustomers(); //Makes SP call usp_InsertCustomers() Each of these methods...

Issue running stored procedure from .net

asp.net,sql-server,visual-studio-2012,stored-procedures,sql-server-2008-r2
I have this stored procedure : CREATE PROCEDURE [dbo].[GetTransactionFeeType_Pager] @PageIndex INT = 1 ,@PageSize INT = 10 ,@ProgramName VARCHAR = '' ,@RecordCount INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT ROW_NUMBER() OVER ( ORDER BY ID ASC )AS RowNumber ,FeeType,TransactionNumber,Amount,TransactionDate,ResponseDescription,PaidUsing INTO #Results FROM TransactionLog where [email protected] SELECT @RecordCount = COUNT(*)...

What is the differece between my codes when I use cursor to fetch my data with repeat and do while?

mysql,stored-procedures,cursor,mysql-workbench
I use cursor to fetch my datas.The number of my data is two.But I find that when I use repeat to fetch my data,it always fetch 3 times. And When I use do while,it is right which fetch 2 times.What is the different of thest two ways?How to fixed the...

SQL stored procedure: increment months from a starting date to an end date

sql-server,tsql,date,stored-procedures,cursor
I'm trying to create a stored procedure in SQL Server that basically increments the months from a given start date up to a given end date and updates them into a predefined table. Problem: Somewhat like this: Exec MonthRunner @Start ='2014-01-01', @End = '2014-06-01' Should give me a table like...

Stored procedure with dynamic query “Error”

sql-server,stored-procedures
what wrong with my code getting the error while executing the application Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'and'. And Here is my Code. any help will be appreciated. ALTER PROCEDURE [dbo].[Usp_ReportList] @pAccountType varchar(35)=null, @pFromDate datetime=null, @pToDate datetime=null, @pAccountId int=null, @pUserId int=null, @pTeamId...

Filtering data in a table for a required condition

php,mysql,stored-procedures
Don't bother about the title, I am unable to think through the exact question. Please find my question below. I am trying to filter out the data in such a way that, the result set should not contain the make and model corresponding to the given year. For example if...

ORA-04091 mutating table error when calling a function from a procedure

oracle,stored-procedures,plsql
I have a task: Write a procedure to update salary (salary * % of increment) in emp table based on grade. Use function to get increment This is my procedure: CREATE OR REPLACE PROCEDURE sal_incre IS CURSOR c_cur IS SELECT * FROM emp_task; BEGIN UPDATE emp_task SET sal = sal...

Compare two sql outputs

sql,sql-server,stored-procedures
I have a table, which has to resemble an annual song chart. It contains 2000 songs and their position per year, ranging from 1999 to 2014. I have to show all the songs that have gone up in the charts the next year the table contains an SONGID (PK, FK,...

Hashing Password in ASP.NET and Resolving it In SQL Procedure

asp.net,vb.net,stored-procedures,hash,password-protection
I'm developing an ASP.NET application using SQL Server Stored Procedures. I need to hash my login password and resolve it in my sp_LoginCheck Stored Procedure. Any suggestions? I have already inserted data in the database. For example: UserName/Password ABC/123456 DEF/987654 I want to encrypt or hash whatsoever the password then...

TSQL Updating Table with Counts from Other

sql-server,tsql,stored-procedures
I have a stored procedure I am creating and having a little trouble coming up with the logic. I have a table that contains a list of Queue names such as (Bugs, Support, etc). I am making a page that is going to show all of the Queues along with...

How to fix ORA-01427

sql,oracle,stored-procedures,plsql
I'm working on converting a stored procedure from SQL to Oracle and I get this error: ORA-01427: single-row subquery returns more than one row ORA-06512: at "CMIUSER.PROCEDURE3", line 21 ORA-06512: at line 7 t_name_match and t_descript_match are global temp tables. Can someone explain to me what I'm doing wrong? create...

select query returns empty in sp but when run it alone, it is not empty. Why?

sql-server,stored-procedures
I have a stored procedure that I write to Table and read table. I write with no problem but when I want to read it returns empty. but it is not empty when I select query and run it returns data. Why it can be. Here is my codes. I...

Alias case statement

sql,stored-procedures,view
I have stored procedure that sometimes return negative number in the ID. (on purpose). In additional, I have a view that shows this table (simple select), but I don't want that '-' will appear in the ID column, I mean, if the number is negative - I want to add...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

PHP with stored procedure - cursor output in Oracle

php,oracle,stored-procedures,oci
I've been trying to get the result from a stored procedure in Oracle using PHP. This stored procedure has some input parameters and one output cursor which give me the result. Here is my stored procedure: CREATE OR REPLACE PROCEDURE "TEST1" ( pPARAM1 CLOB, pPARAM2 VARCHAR2, p_record OUT SYS_REFCURSOR )...

passing parameter values to stored procedure when calling from another procedure

sql,sql-server-2008,stored-procedures
i have a stored procedure sp1 which takes parameters @id int, @type int, @ orderno int Now, I am trying to call sp1 from sp2 alter proc sp2 /insert into @temp_tbl exec sp1 1,2,3 set @select = 'select * from @temp_tbl' [email protected] is already declared exec (@select) Now when I...

Replacing non-ascii characters in MSSQL

sql-server,regex,stored-procedures,non-ascii-chars
I have done quite a bit of research and can not seem to find any answers. What I need to do is the following: REPLACE all characters in a MSSQL column which are non ascii characters with their ascii equivalents. For example: ë --> e ï --> i ñ -->...

Stored procedure timesout called from code, executes ok from SSMS

c#,sql-server,stored-procedures,connection-timeout
I have stored procedure which takes around 10 seconds to run when called from SSMS and executes successfully. The procedure takes an int as a parameter. When calling the same stored procedure from code: using (var connection = new SqlConnection(ConnectionStringName)) { using (var cmd = new SqlCommand("ProcedureName", connection)) { cmd.CommandType...

Importing Python file from PostgreSQL stored procedure [duplicate]

python,postgresql,stored-procedures
This question already has an answer here: How to import a module given the full path? 15 answers It is possible to have Python code in a PostgreSQL stored procedure. For example: CREATE FUNCTION someProc() RETURNS void AS $$ # Some Python3 code... $$ LANGUAGE plpython3u; But how can...

Finding a gap in dates

sql,sql-server,sql-server-2008,tsql,stored-procedures
I have a table with 3 columns: Date smalldatetime not null Val1 decimal not null Val2 decimal not null And a lot of data in this table. Not all calendar dates are presented in the table. How can I find all places where the dates gap is more than x...

Weird Behaviour of MySQL stored procedure

c#,mysql,stored-procedures
I have a stored procedure in mysql as defined below DROP PROCEDURE IF EXISTS subway.cust_inventory; CREATE PROCEDURE subway.`cust_inventory`(IN lastItem INT,IN lastValue INT) BEGIN delete from temp_inventory; drop table temp_inventory; create table temp_inventory as SELECT CONCAT ('390', cast(DATE (t6.InventoryDate) as char) ,cast(t6.SKUorItem as char) ) AS PK,t6.CustomerID, t6.entityID, t6.inventoryDate, t6.SKUorItem, t6.Category,...

How declare a variable inside a procedure MYSQL

mysql,function,stored-procedures
I'm trying to do an easy procedure inside my MySQL: DELIMITER $$ CREATE PROCEDURE `countRows`(IN v varchar(30)) BEGIN DECLARE e INT DEFAULT 0; SET @t1 =CONCAT("SELECT COUNT(*) FROM ",V, " INTO",e); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; SELECT CONCAT("The ",V," table contains ",e," rows"); END$$ DELIMITER ;...

Call to a member function fetchAll() on MYSQL Stored Procedure

php,mysql,stored-procedures
I am trying to execute stored procedure, i want to have output as an associative array, but i am getting following error. Call to a member function fetchAll() on boolean and following is my PHP script which is calling the procedure $query_text = "CALL procedure1(?,?,@mobNum,@firstInsert);"; $output=$this->pdo->prepare($query_text); $output=$output->execute(array($number,$code)); print_r($output->fetchAll(PDO::FETCH_ASSOC)); ...

Get ResultSet from NamedParameterJdbcTemplate executing a stored procedure

java,sql-server,stored-procedures
I am trying to get a ResultSet from the execution of a stored procedure using NamedParameterJdbcTemplate. I have set up my stored procedure to return the value with id as the column name. Unfortunately, the way I have found to execute the stored procedure seems to call the execute twice...

SET NOCOUNT ON is not working under ubuntu

php,sql-server,stored-procedures,yii
I am using mssqlserver with yii framework I have created one stored-procedure please see below code. //Call Store procedure to get data $sql = "EXECUTE IESReportData @assessmentId=:assessmentId, @queId=:queId,@instanceId=:instanceId"; //set database connection and start the yii query builder to be executed. $connection = Yii::app()->db; $command = $connection->createCommand($sql); $command->bindValue(":assessmentId", $assessmentId); $command->bindValue(":queId", "");...

Stored Procedure for insert

sql-server,stored-procedures,datatable,syntax-error,sql-insert
I am taking a datatable as parameter in my stored procedure and I am trying to insert a few of its column in one of the database tables but I don't know the correct syntax. This is what I have tried ALTER PROCEDURE [dbo].[spInsertInvoice] @tblInvoice [TypeExcel] READONLY AS BEGIN SET...

SQL - Looping through ever row of table in mysql?

mysql,sql,loops,stored-procedures,cursors
So I have 2 tables, communication,and movement. communication has columns fromID,timestamp that has ID of caller, and time the call was made. Then I have another table movement that has ID,timestamp,x,y, that has the ID of a person, their location (x,y), and the time that they are at that location....

SQL Stored Procedure How to Modify and Return the Result of an Executed Dynamic Query

sql-server,tsql,stored-procedures
I have created a stored procedure that returns a single string of concatenated fields. The issue is that some of these fields may be empty strings resulting in a string much like the below: , Mendip Road, Farnborough, Hampshire, GU14 9LS or even , , Farnborough, Hampshire, GU14 9LS I...

why it doesnt accept a concated string as parameter in SP?

sql,sql-server,stored-procedures
I wrote an SP that works perfectly. I was calling the SP with regular parameters and worked. However, now I need to call the SP like below EXEC @v_nReturn = sp_get_next_value 'LP_' + @WhID + '_COUNTER' The intellisense gives an error on the first '+' sign. It says "incorrect syntax...

how can i check return value of stored procedure in entity framework

c#,stored-procedures,entity-framework-6
I have a stored procedure call but i want to get the return value of the stored procedure. Return type is integer. Following the code where I am calling the stored procedure else if(ReportName=="LandMarkInOutReport") { _DBContext.LandMarkInOutReport(report.ReportParameters.StartDate, report.ReportParameters.EndDate, Convert.ToInt64(paramArr1[3]), Convert.ToInt32(paramArr1[9]), Convert.ToInt32(paramArr1[11]), paramArr1[5], paramArr1[7]); } So please guide me how can I...

Return all values or search query

sql,sql-server-2008,stored-procedures
I have a reporting website where there are several hundred stored procedures which output the data. I need to add searching functionality to this which I could do by adding the following HAVING clause as such: HAVING @Search in (FirstName, LastName, CAST(EmployeeID as nvarchar(25)) Now, the only issue is that...

Cannot save multiple files to database with Foreign Key

sql-server,asp.net-mvc,entity-framework,stored-procedures,file-upload
In my MVC application I have two tables called Ticket and Attachment and I want to save attachments for per ticket. The problem is that: I need to save multiple attachments with the TicketID when creating a new ticket. So, I think I should create a new ticket in the...

SQL Server TSQL - How to achieve “NOT IN” statements within a CASE

sql-server,tsql,stored-procedures
I have the following working code: INSERT INTO #resultado SELECT 1, v.idReqVoucher, v.dtSolicitacao, v.idFuncionario_solicitante, v.idFuncionario_beneficiario, v.idStatus, NULL as valor FROM reqVoucher v WHERE v.idReqVoucher = CASE WHEN @idRequisicao = 0 THEN v.idReqVoucher ELSE @idRequisicao END AND v.idStatus = CASE WHEN @status = 0 THEN v.idStatus ELSE @status END AND v.dtSolicitacao...

Fire trigger from DocumentDB stored procedure

stored-procedures,triggers,azure-documentdb
We have a stored procedure that is modeled after the bulkImport sproc in the DocumentDB sample code. This sproc takes an array of documents, does some stuff, and eventually calls createDocument, which the documentation says is asynchronous. Now we have written a pre-trigger for Create that checks some fields and...

How do I split a time range into days using stored procedure in informix 11.5?

stored-procedures,informix
I am facing an issue with splitting a time range into days in informix 11.5. My requirement is, I will get two dates for e.g 06/05/2015 11:00:00 to 06/07/2015 03:30:00. I have to split these time range into the following in a stored procedure, 06/05/2015 11:00:00 - 06/05/2015 23:59:59 06/06/2015...

SQL - Adding user defined variables to table (mysql)?

mysql,sql,stored-procedures,phpmyadmin,cursor
So I have this code thats part of a stored procedure: SET @MID = 0; SET @MX = 0; SET @MY = 0; SET @MT = 0; SET @CSTAMP = '2014-6-06 08:03:19'; SELECT @MID=m.ID, @MX=m.x , @MY=m.y , @MT=m.timestamp FROM movement m WHERE m.ID = cSID AND m.timestamp = (SELECT...

How To Get the Sum of a Column within a given Date range in a Table and Update a particular Cell in The Table with The Sum in TSQL?

sql-server,database,tsql,stored-procedures
I have a Table named PersonalLedgerForTheiftFundAndShareClone I want to get The Sum Of TInterest ( TheiftFundInterest ) within The Date Range Of 2014-03-31 00:00:00.000 To 2015-03-31 00:00:00.000 For each Employee and Then Update The TInterest with The Sum Where Date is 2015-03-31 00:00:00.000 For that Specific Employee. I Have Written...

Will a Stored Proc be consider compiled if it does not have an entry in cache?

sql-server,stored-procedures
I am working on SQL Server 2008 R2. I have a stored proc Rpt_RegionReport. It has been run at least once so I can say it is compiled. It has an entry in cache which I can see in sys.dm_exec_cached_plans. Now I delete it from the cache using its plan_handle...

Insert Into two Tables with Foreign Key relationship

sql-server,asp.net-mvc,entity-framework,sql-server-2008,stored-procedures
In my MVC application, I have two entities called Ticket and Attachment, and when inserting a new ticket record, I also want to insert new records for attachments with the FK of previously inserted Ticket's ID. I have a look at some samples as this page, but I need to...

Oracle Stored Procedure varray beyond count

oracle,stored-procedures,plsql,varray
i've created 3 stored procedures. Each of their functions are : 1. proc_insertleveluser -> insert into "leveluser" table and return the last id inserted 2. proc_insertpermissiondtl -> insert into "permission_dtl" table and return the last id inserted 3. proc_insert_relation_lpd -> insert into "lvl_permission_dtl" table with data provided from procedure 1...

pass one strored procedure result into other stored procedure as parameter in Mysql

mysql,stored-procedures
I have two function called in stored procedure: CALL createPost(IN value,@Outvalue); Now I want to pass '@Outvalue' to other stored procedure: CALL createPostMedia(@Outvalue); value is retrieved when we run SELECT @Outvalue; But @Outvalue value is not pass to createPostMedia() in parameters ...

SQL, missing end, but why?

mysql,stored-procedures
I have an issue with my SQL procedure. MySQLWorkbench advise me that it miss 'end' to my first SET, but not for the second. I don't know why. DELIMITER $ drop procedure if exists pay10percent$ create procedure pay10percent(IN montant decimal(9,2),IN idResa INT(5)) begin declare circuitid INT; SET circuitid = (...

Assign the result of stored procedure into a variable using Entity Framework

c#,entity-framework,variables,stored-procedures
I'm new to Entity Framework ORM while using DataSet ORM I can simply assign a stored procedure result to a variable like MyDataSetTblAdapter MyData = new MyDataSetTblAdapter(); string Result = Mydata.MyStoredProcedure().ToString(); And it works fine .. but in Entity Framework I'm trying the same OnlineDvdShopDbEntities dbContext = new OnlineDvdShopDBEntities(); String...

C# - Passing parameter to stored procedure and return IEnumerable

c#,sql-server,stored-procedures,dapper
can I directly pass the parameter in the query as shown below? There is error so anyone could help? public IEnumerable ListTop10countries(string direction) { using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MYCON"].ToString())) { var list = con.Query<OutputCountries>("Usp_GetTop10", direction).AsEnumerable(); return list; } } Error: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but...

Generic Calls to stored procedure for data download from C#

c#,sql-server,generics,stored-procedures
I would like to call a number of stored procedures based on a set of user selection and return data that the user will download. Each stored procedure has a different set of parameter list and type. Those parameter will be exposed through a web page as form elements. My...

Dynamically Retrieve Parameter Names & Current Values Inside T-SQL Stored Procedure

sql-server,tsql,stored-procedures
I have hundreds of templated ETL stored procedures for Business Intelligence. They log their operational activity to audit table. The one thing missing is logging the parameter information being passed into them. The problem is the parameters aren't always the same from one SP to another. I am looking for...

Converting Datasets and Shared Datasets to t-sql stored procedure

tsql,stored-procedures,ssrs-2012
Is there a way to automatically generate stored procedures out of the t-sql in the datasets found in an ssrs project?

How to Update one table by another table datas using storedprocedure?

c#,asp.net,stored-procedures
I have a Table named Dummywith marks and student id (3 mark fields are there). I have another one table Applicantdetails this table also contains mark fields. what I want to do is I want to updates Dummy tables marks to Applicantdetails table's marks as per student id. I want...

SQL Stored Procedure with Dynamic Column Names

sql-server,stored-procedures
I am trying to generate a SQL statement that dynamically get column names from the database based on a filter. We have a table with about 50 columns with a prefix on each column to signify which "set" it applies to. I have created an Query that runs fine in...

SQL SERVER Select MAX Procedure

sql-server,stored-procedures
I'm trying to make a procedure that changes the user login on a table,but change only the last entry if its in a specific status:Here is the procudure i'm trying: Update T_STATUS_CLIC SET iEDV = @iEDV WHERE iClic = @iClic and iStatus = '9' and dtDateCreated = (select max (dtDateCreated)...

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures
I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...

Procedure call function with table fields

mysql,function,stored-procedures
How to call a function in a procedure with the table fields? I've read the question How to call a stored procedure using table data, but it is different. I have a function: CREATE FUNCTION valorPedido (qtde int, decimal value (11.2)) RETURNS double BEGIN RETURN qtde * value; END I...

MSSQL stored procedure select all columns

sql,sql-server,stored-procedures
I have a stored procedure where the SELECT statement looks like this: SELECT @KeyName FROM dbo.FiConfig WITH (NOLOCK) WHERE IssuerKey = @IssuerKey Where I'm creating it like this: CREATE PROCEDURE GET_FICONFIG @IssuerKey INT, @KeyName NVARCHAR(100) Where KeyName is the column name I am retrieving my data from. My question: is...

How LinqToSql Generates output result of stored procedures

.net,linq,stored-procedures,linq-to-sql
for example you have a stored procedure with title : sp_test when you drag into LinqToSql, it generates sp_testResult class with stored procedure output columns as its properties. I want to know how LinqToSql distinguish output result of stored procedure?...

Pass subset of User Defined Table to a Stored Procedure

sql-server,stored-procedures,sql-server-2008-r2,user-defined-types
I have a User Defined Table that I am passing into a stored procedure from within a stored procedure. DECLARE @tmpInput MyTableType; --Table is populated from an INPUT XML exec ValidateInputXML SELECT * FROM @tmpInput TI WHERE TI.EntryType = 'Attribute'; Now this isn't giving me an error, but when I...

Subquery returned more than 1 value. This is not permitted when the subquery follows =,.. or when the subquery is used as an expression

sql,sql-server,tsql,stored-procedures,sql-server-2012
I have this following stored procedure to make a reservation .I have not done the front end to insert the values so I use the execute stored procedure from the sql server menu to insert into the database but it gives me the subquery returned more than 1 value and...

Use data separated by delimiters in Sql Procedure

sql,sql-server,sql-server-2008,stored-procedures
I have a Sql Procedure that receives a string as a parameter. Now the task that I need to perform is that the parameter contains data that is separated by two different kinds of delimiters i.e ":" and "," There are two columns in the DB Id and Value. sample...

PostgreSQL: conditional statements in functions

sql,postgresql,stored-procedures
I've been trying to create a basic user authentication system within postgreSQL 9.4, but have been coming unstuck. My users table looks like this: -- Users table CREATE TABLE users ( user_id SERIAL NOT NULL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL, password...

Select unexist date yyyy/mm

sql,tsql,stored-procedures
I have a SQL query as follows: SELECT R.BOOK_YM, COUNT(*) FROM #TB_TRANSED_GUI_AMOUNT AS R GROUP BY R.BOOK_YM The selected results like as follows: BOOK_YM | count(*) ---------+--------- 201411 | 51 201412 | 142 201501 | 1 201506 | 1 How do I modify the result like the follows: BOOK_YM |...

How to access another database located on a different server in a SP? [duplicate]

sql-server,sql-server-2008,tsql,stored-procedures
This question already has an answer here: Selecting data from two different servers in SQL Server 7 answers I am working on a stored procedure that has been written a few years ago. The stored procedure accesses another database in the join query as shown below, select * from...

How to get stored procedure output parameter into variable using Powershell?

powershell,stored-procedures,output-parameter
I have a stored procedure returning a string and I need the result as a powershell variable. I'm stuck on the output parameter syntax. Powershell script: $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=myserver;Database=mydb;Integrated Security=True" $SqlConnection.Open() $SqlConnection.State $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "testsp3" $SqlCmd.Connection = $SqlConnection $op = new-object System.Data.SqlClient.SqlParameter;...

creating stored procedure in mysql calculate profit from product table

mysql,sql,database,stored-procedures
My following code show some error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN SET @v1:= (select sum( cost_price * current_stock) from product)' at line 2 CREATE PROCEDURE sp_profit BEGIN...

Sql server - stored procedure - concatenated results

sql-server,stored-procedures
In order to build a statistical report, i need to execute this kind of request, for each 5000 agencies, and each 5 state, so 25 000 requests : select count(*) from transaction where state == 1 and where id_agency==1; select avg(duration) from transaction where state == 1 and where id_agency==1;...

Ignore other results if a resultset has been found

sql,sql-server,tsql,stored-procedures
To start, take this snippet as an example: SELECT * FROM StatsVehicle WHERE ((ReferenceMakeId = @referenceMakeId) OR @referenceMakeId IS NULL) This will fetch and filter the records if the variable @referenceMakeId is not null, and if it is null, will fetch all the records. In other words, it is taking...

Can we create stored procedure in ms access 2007 [duplicate]

ms-access,stored-procedures
This question already has an answer here: can make stored procedure or function in Access 2003? 2 answers How can we create stored procedure in MS Access 2007. If we can then how should write procedure for insert statement....

How to use local-name(.) within dynamic sql statement

sql-server,stored-procedures,dynamic-sql,sqlxml,sql-function
I have the following code to create a SQL function that will parse an XML string and create a table of key value pairs that represent the nodes and values. This works fine for me in my use cases. CREATE FUNCTION XmlToKeyValue ( @rootName AS varchar(256), @xml AS Xml )...

Exception “Procedure or function expects parameter, which was not supplied.” thrown even after supplying the parameter

c#,.net,sql-server,stored-procedures,sql-server-2012
I am trying insert some entries in the database using a stored procedure which is called by a C# method as shown below: public int addProfile(UserProfile NewUserPro) { DataSet ds = ExecuteStoredProcedure("AddNewPro", new SqlParameter[]{ new SqlParameter("@UserID", NewUserPro.UsrId), new SqlParameter("@Type", NewUserPro.Type), new SqlParameter("@Phone", NewUserPro.Phone), new SqlParameter("@Name", NewUserPro.Name), new SqlParameter("@FatherName", NewUserPro.FatherName), new...

How to get number of rows affected by last several sql statement inside procedure in teradata?

sql,stored-procedures,rows,teradata
So let I have 3 merge statement inside the procedure: CREATE PROCEDURE SP_Employee(OUT num_rows BIGINT) BEGIN MERGE INTO t1 USING t2 ...; MERGE INTO a1 USING b2 ...; MERGE INTO c1 USING d2 ...; END; How can I get number of rows affected inside procedure? I know about ACTIVE_COUNT, but...

Execute a Stored Procedure with different missing parameter calls

sql,sql-server,stored-procedures
If I have a stored procedure like this: CREATE Procedure [dbo].[insertIntoTableX] -- table_name as there are three tables with the same schema... @table_name varchar(200), -- all the other variables for the columns @column_1 = NULL, @column_2 = NULL, @column_3 = NULL, @column_4 = NULL, @column_5 = NULL AS BEGIN TRY...

SQL Server Stored procedure declaring variable in different way

sql-server,tsql,stored-procedures
I'm just started using MSSQL and need to understand. While I was reading few sample code that I might use in near future found CREATE PROCEDURE [dbo].[SP_Set_LMS_BY_ERW] @UPA nvarchar(30) = NULL Question is what's the difference between @UPA above Declare @count nvarchar(30)? Thank you...

How can I use IF statement in sql stored-procedure

sql,sql-server,stored-procedures
I want to transform an SQL Query to a procedure in SQL server, but I have an issue in the IF statement. This is the logic I want to implement: //If the patientStatus equals "all" there will be no condition patientStatus.ToString() == "all" ? "" : ( patientStatus.ToString() == "out"...

ORA 06502 error when called inside procedure but when executed using test window it works well

oracle,stored-procedures,dblink
I'm invoking a procedure schema1.proc1 from procedure schmea2.proc2 using dblink. For certain cases schema1.proc1 returns the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small however, when i try to execute it with the same input arguments using pl/sql, it works properly. please help me in resolving...

How can I fit this UPDATE query into this existing SELECT statement?

sql-server,tsql,stored-procedures,nested-queries,nested-query
I have an existing stored procedure. I have been asked to attempt to find a way to fit a specific set of logic into the procedure in order to avoid having to create a new one. However, I am not the best with SQL, but I would still like to...

Disallow NULL parameters to stored procedures in MySQL/MariaDB

mysql,stored-procedures,mariadb,stored-functions
I can specify that table columns are NOT NULL, but how do I make a stored procedure or function only be compatible with non-null arguments? Adding NOT NULL after the argument name doesn't work.

how to insert the xml data to sql server using c#?

sql,xml,stored-procedures,sql-insert
I am beginner to the C#, I need to insert my XML file to SQL table pro-grammatically XML file have five nodes called root, name, subject, sno and queries, I will listed below <root> <name> MyName </name> <subject> newsubject </subject> <queries> <sno> 1 </sno> <query> This is the query one...

Multiply time with a decimal value in mysql?

mysql,database,stored-procedures
I am trying to multiply a time with decimal value but not to get it right. That is, when I multiply with any decimal value, it is rounding that value to nearest integer and then doing the multiplication. For Example: All the below operations are resulting the same value 1....

Insert Stored Procedure for One to many relationship SQL

sql-server,stored-procedures
Asp.net Form Student Name Student Address English (marks) Maths (marks) Science (marks) For the above form I have created the following table structure Create Table Student ( sId int primary key identity(100,1), sName varchar(50), sAddress varchar(100) ) Create Table Subjects ( subId int primary key, subName varchar(100) ) Create Table...

MySQL Procedure IN variable don't work properly

mysql,stored-procedures
I'm learning Functions, Procedures and Triggers and I wanted to do a easy procedure that count the rows in a table from parameters. create procedure countRows(IN v varchar(30)) SELECT COUNT(*) FROM v; Can someone tell me why if I do: call countRows('sometable'); call countRows(sometable); //I tried both It just don't...

SQL Procedure: Generating random password and storing it in database

sql-server,tsql,stored-procedures
What I want to do is to create a random password and save the hashed password to db. I am using the "random password create procedure" from this site. And here is how I save the hashed version of it to the datbase. declare @salt varbinary(4) = CRYPT_GEN_RANDOM(4); declare @hash...

Oracle stored procedure wrapping compile error with inline comments

database,oracle,stored-procedures,plsql
I am trying to wrap a stored procedure in Oracle using dbms_ddl.create_wrapped() method. If the stored procedure contains an inline comment I get ORA-24344: success with compilation error Errors: BEGIN dbms_ddl.create_wrapped('CREATE OR REPLACE PROCEDURE TEST_WRAP ' || '(' || ' NAME IN VARCHAR2 ' || ') AS ' || '...

SAS: getting the filesize of created DBF file

stored-procedures,sas,sas-macro
I have SAS stored process that ceates DBF file from SAS data set rr_udf_value and finds its size (F_SIZE): filename dbfout "/SASInside/DBF/myfile"; proc export data=rr_udf_value outfile=dbfout dbms=dbf replace; run; %let f_nm=/SASInside/DBF/myfile.DBF; %let rc=%sysfunc(filename(onefile, &f_nm.)); %let fid=%sysfunc(fopen(&onefile)); %let F_SIZE=%sysfunc(finfo(&fid,File Size (bytes))); %put &F_SIZE; The problem is that the variable F_SIZE is...

DB2 Z/OS 10 Equivalent of AUTONOMOUS procedure

stored-procedures,db2
Is it possible to get the functionality of autonomous stored procedure in DB2 z/OS 10? Scenario is: attempt to execute given stored procedure must logged to our logs table even if the procedure ended with exception (rollback was performed). I was going to use autonomous stored procedure for that, but...

Return result set from SQL Server stored procedure to vb.net

sql-server,vb.net,stored-procedures
The following stored procedure works as I want in the Visual Studio designer. The result is a table containing all the race distances for the input @CourseName ALTER PROCEDURE [dbo].[getCourseDistancesProc] @CourseName nvarchar(50) AS BEGIN SET NOCOUNT ON; SELECT DISTINCT RaceDistances.RaceDistance FROM RacingMaster JOIN RaceDistances ON RacingMaster.Dist_Of_Race_FK = RaceDistances.PKRaceDistancesId JOIN Courses...

How to retrieve a part of a stored procedure's header?

sql-server,sql-server-2008,stored-procedures
I have a C# WinForms application that manages stored procedures used by different services. What the users see is something like that: exec stored_procedure_name param1, param2, param3 And since param1 doesn't mean anything to them (they can't see the stored procedure), I would like to present to them small descriptions...

Retrieve the Return Result with Chronological Order Based on Parameter

tsql,stored-procedures,sql-server-2012
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1...

Oracle Stored Procedure - How to compare type NUMBER with intergers?

sql,if-statement,stored-procedures,oracle11g
I have following stored procedure. Parameter FLAG_ passed in is only allowed to accept null, 0 or 1. But the condition checking (FLAG_ != 0 OR FLAG_ != 1) doesn't work. I guess this is because type for FLAG_ is NUMBER which include float. Is there a way to compare...

How can I read a xml Response from T-SQL?

xml,web-services,tsql,stored-procedures
I have this Store Procedure to call and read the response from a web service. I call a Web services from store procedure. I have a problem when I try to read the response. So the code is this: SET @Response= ' <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soap:Body> <RegisterUserResponse xmlns="http://tempuri.org/">...

How to access a Row Type within an Array Type in DB2 SQL PL

arrays,stored-procedures,types,db2,row
I have a java front end that has a table of data. We need to save this data to the Database via stored procedure. If the passed parameter is a just an array, I am able to access the contents easily. But if the contents of the array is a...