How to Programming with Stored-procedures

stored-procedures,informix

SQL Server doesn't do (full) expression parsing when you call a stored procedure. This is definitely an area where a small change would be highly convenient, although there are probably good reasons for the limitation. As mentioned in a comment, use a separate variable: DECLARE @arg varchar(256) = 'LP_' +...

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

There are multiple ways to do it, depending on how you want to consume the values. Here's one possible way to do it: CREATE PROCEDURE split_date_range(t0 DATETIME YEAR TO SECOND, t1 DATETIME YEAR TO SECOND) RETURNING DATETIME YEAR TO SECOND AS t_begin, DATETIME YEAR TO SECOND AS t_end; DEFINE tb...

Insert Stored Procedure for One to many relationship SQL

java,sql-server,stored-procedures

I think the below Procedure Suits your requirements: First Insert this Value insert into subjects values(1000,'English') insert into subjects values(2000,'Maths') insert into subjects values(3000,'Science') Use this Procedure: Create Procedure uspAddStudentDetails ( @sName varchar(50), @sAddress varchar(100), @English int, @Maths int, @Science int ) AS Begin declare @sid int declare @sqlinsert varchar(max)...

Get ResultSet from NamedParameterJdbcTemplate executing a stored procedure

I fixed the issue by returning a List of integers rather than a result set. This allowed me to get the data before the result set was closed and return it. I found this solution here: http://www.java2s.com/Code/Java/Spring/SelectStatementWithPreparedStatementCallback.htm List<Integer> result = getTemplate().execute(getSql("create"), params, new PreparedStatementCallback<List<Integer>>(){ @Override public List<Integer> doInPreparedStatement(PreparedStatement ps) throws...

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

Concatenate the comma inside the ISNULL expression as follows: ISNULL(POI + ', ','') so your query will look like: SET @SQLQuery = 'SELECT TOP 1 REPLACE((ISNULL(POI + '', '','''') + ISNULL(Name + '', '','''')' + ' + ISNULL(Settlement + '', '','''') + ISNULL(Cou_Unit + '', '','''') + ISNULL(Postcode,'''')),'', , '',...

Compare two sql outputs

oracle,stored-procedures,plsql

I think you want this declare @year as int = 2011 select a.songid, a.position, b.position from YourTable a inner join YourTable b on a.songid = b.songid where a.YEAR = @year and b.year = @year + 1 and a.position > b.position See below, Only song 2 has moved up from 2011...

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

Your function is referring to the same table you're using in the procedure at the point you call that function, which is what causes this error. You're updating and querying it at the same time, in a way that could cause indeterminate (or confusing) results, even though you aren't querying...

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

sql,stored-procedures,rows,teradata

You need to initialize a variable to zero and then add the count (Either using ACTIVITY_COUNT or the GET DIAGNOSTICS ROW_COUNT) after each execution of a DML statement: CREATE PROCEDURE SP_Employee(OUT num_rows BIGINT) BEGIN DECLARE ac BIGINT DEFAULT 0; MERGE INTO t1 USING t2 ...; SET ac = ac +...

How declare a variable inside a procedure MYSQL

mysql,function,stored-procedures

For the statement in @t2; no need of a prepared statement since it doesn't contain any SQL statement to execute. The below mentioned 3 code lines are unnecessary. PREPARE stmt3 FROM @t2; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; You can simply display it like SET @t2 =CONCAT("The ",V," table contains ",e,"...

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

ms-access,stored-procedures

It is not possible to create stored procedures in MS Access 2007. You need Access 2013 - https://msdn.microsoft.com/en-us/library/office/ff845861(v=office.15).aspx

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

sql-server,sql-server-2008,tsql,stored-procedures

You will need to create a linked server. Next you can something along the lines of: SELECT * FROM [server\instance].[database].[schema].[table] ...

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

c#,stored-procedures,entity-framework-6

Database First First, you have to add your stored procedure to the .edmx file. If you have a context variable _DBContext and the stored procedure is called LandMarkInOutReport, you can execute it like this: LandMarkInOutReport_Result returnValue = _DBContext.LandMarkInOutReport(report.ReportParameters.StartDate, report.ReportParameters.EndDate, Convert.ToInt64(paramArr1[3]), Convert.ToInt32(paramArr1[9]), Convert.ToInt32(paramArr1[11]), paramArr1[5], paramArr1[7]).FirstOrDefault(); The stored procedure call without .FirstOrDefault()...

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

sql-server,vb.net,stored-procedures

There was obviously more work to be done than I had realized, but just in case anyone else stumbles across this question the solution I finally adapted from elsewhere is:- Dim myConn As SqlConnection Dim myCmd As SqlCommand Dim results As String Dim ConnectionString As String ' Create the connection...

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

sql-server,asp.net-mvc,entity-framework,stored-procedures,file-upload

I would say "not compiled" if it doesn't have an entry in the Plan Cache. The next time it is called it should need to be parsed and compiled. And just to be clear, plans do not stay cached forever. The cache is cleared when SQL Server service is restarted,...

Cannot save multiple files to database with Foreign Key

The ID property will be automatically filled by EF after a SaveChanges. Your code can then use it. I assume that the viewModel.Ticket object is the actual object saved to the database. If not, please also post the SaveTicket method. [HttpPost] [ValidateAntiForgeryToken] public ActionResult Create([Bind(Exclude = null)] TicketViewModel viewModel /*...

Stored procedure timesout called from code, executes ok from SSMS

c#,sql-server,stored-procedures,connection-timeout

To avoid that error just use: cmd.CommandTimeout = 0; Note : Your query execution will takes infinitive time. ...

How LinqToSql Generates output result of stored procedures

.net,linq,stored-procedures,linq-to-sql

Here is what happens in SqlCommandBuilder.GetSchemaTable(...) which is sadly protected. SqlCommand command; // setup as SP using (SqlDataReader reader = command.ExecuteReader( CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) { return reader.GetSchemaTable(); } The resultant DataTable will contain the output schema. If I remember correctly, you do not have to pass parameters for this work....

SQL Syntax error #1064 cursors (mysql)?

mysql,sql,if-statement,stored-procedures,cursor

The END IF of the second IF is missing.

Delete multiple records from different tables with SQL stored procedure

Try this.. First fetch the UserID's that you have to delete and store them in a Temp table. Select a.UserID into #UserToDelete from aspnet_users a inner join UserProfile b on a.UserID = b.aspnetUserID where IsTemp=1 and LastActivityDate < DATEADD(mi, -15, CURRENT_TIMESTAMP) Now delete all these users from all your transaction...

How can I use IF statement in sql stored-procedure

sql-server,regex,stored-procedures,non-ascii-chars

You can combine AND and OR conditions to get the result you desire. You can use below query: SELECT * FROM tablename AS CMS_ENCOUNTER WHERE (@PATIENT_STATUS = 'all') OR (@PATIENT_STATUS = 'out' AND CMS_ENCOUNTER.ENCOUNTER_END_TIME IS NOT NULL) OR (@PATIENT_STATUS <> 'out' AND CMS_ENCOUNTER.ENCOUNTER_END_TIME IS NULL) In this case first you...

Replacing non-ascii characters in MSSQL

You cannot do it any other way than the old-fashioned, "hard" way (in any language, even, not only SQL). Since in many (spoken/written) languages, accented characters are not the same as non-accented ones, it's actually just a visual similarity, so there is no true correspondance. Some letters can look like...

SQL - Looping through ever row of table in mysql?

mysql,sql,loops,stored-procedures,cursors

Let's see if I can point you in the right direction using cursors: delimiter $$ create procedure findClosestTimeStamp() begin -- Variables to hold values from the communications table declare cFromId int; declare cTimeStamp datetime; -- Variables related to cursor: -- 1. 'done' will be used to check if all the...

PostgreSQL: conditional statements in functions

sql,postgresql,stored-procedures

Is it required that functions are written using SQL language? There is a solution with PLPGSQL procedure if you accept PLPGSQL. CREATE OR REPLACE FUNCTION user_login (auth_email VARCHAR(254), auth_password VARCHAR(72)) RETURNS SETOF users AS $$ DECLARE found_user users; BEGIN SELECT u.* FROM users u WHERE u.email=auth_email INTO found_user; -- Check...

Return all values or search query

sql,sql-server-2008,stored-procedures

If you control the queries, then you can do something like: HAVING @Search IS NULL OR @Search in (FirstName, LastName, CAST(EmployeeID as nvarchar(25)) ...

Stored procedure for pagination is not working

mysql,sql,stored-procedures,phpmyadmin,cursor

Try to this ALTER PROCEDURE [dbo].[CommunityPostLoadAllPaged] ( @PageIndex int = 0, @PageSize int = 50, @TotalRecords int = null OUTPUT ) AS BEGIN Declare @inEndRow Int ,@inStartRow Int CREATE TABLE #DisplayOrderTmp ( [Id] int IDENTITY (1, 1) NOT NULL, [inRowNum] Int Primary Key, [CommunityPostId] int NOT NULL ) INSERT INTO...

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

In a SELECT statement, assignment to user defined variables use the (pascal-like) assignment operator (:=) rather than just an equals sign. For example, this performs an assignment of the value 'foo' to the user defined variable: SELECT @var := 'foo' ^ That is much different than this: SELECT @var =...

Finding a gap in dates

sql,sql-server,sql-server-2008,tsql,stored-procedures

Here is how you can do this: DECLARE @t TABLE ( Date SMALLDATETIME NOT NULL , Val1 DECIMAL NOT NULL , Val2 DECIMAL NOT NULL ) INSERT INTO @t VALUES ('20150101', 1, 1), ('20150104', 1, 1), ('20150109', 1, 1), ('20150201', 1, 1), ('20150305', 1, 1), ('20150506', 1, 1) ;WITH cte...

MSSQL stored procedure select all columns

java,mysql,sql,hibernate,stored-procedures

You can use dynamic query: CREATE PROCEDURE GET_FICONFIG @IssuerKey INT, @KeyName NVARCHAR(100) AS declare @s varchar(500) = 'SELECT ' + @KeyName + ' FROM dbo.FiConfig WITH (NOLOCK) WHERE IssuerKey = ' + CAST(@IssuerKey as VARCHAR(10)) exec(@s) You should be careful here. Possible Sql Injection. But you can not pass column...

Is it possible to add Stored procedure dynamically from hibernate?

So I found out what was wrong in the baove stored procedure: The use of DELIMITER is not required, begin with CREATE PROCEDURE the use of "\n" is also not required, but it will not prompt an error if used. You can't use DROP and CREATE in the same query,...

Select unexist date yyyy/mm

sql,tsql,stored-procedures

Try this: (I think BOOK_YM is a varchar field if not CAST it) SELECT Y.YYYY + M.MM AS BOOK_YM, COUNT(R.BOOK_YM) AS CNT FROM (SELECT SUBSTRING(BOOK_YM, 1, 4) As YYYY FROM t GROUP BY SUBSTRING(BOOK_YM, 1, 4)) AS Y CROSS JOIN (SELECT '01' As MM UNION ALL SELECT '02' UNION ALL...

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

sql,xml,stored-procedures,sql-insert

You have a few options: You can read the XML nodes before inserting into the database, and insert normalized table column values. You can shred the XML in a stored procedure, by passing the XML string as an input parameter. You can read up on how to shred the XML...

Oracle Stored Procedure varray beyond count

oracle,stored-procedures,plsql,varray

You've created an empty array so the count will be 0 when you get to the loop. As Bob Jarvis points out, if you want to iterate through the loop 13 times, you'd want to use the limit, not the count of the array. SQL> ed Wrote file afiedt.buf 1...

SQL, missing end, but why?

mysql,stored-procedures

You must declare all the variables before using SET. Alternatively, you can drop SET and use that subquery as a default value: declare circuitid INT DEFAULT ( SELECT IDCIRCUIT FROM RESERVATION WHERE IDRESERVATION=idResa ); ...

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

c#,sql-server-2008,stored-procedures

Are you passing the same parameters? I believe the procs receive a query plan the first time they are compiled, and that plan is based on the parameters passed. So they may have different query plans depending on how they were initially called. As a test, run sp_recompile on them...

Issue running stored procedure from .net

asp.net,sql-server,visual-studio-2012,stored-procedures,sql-server-2008-r2

VARCHAR() without a specified length often defaults a length of 1 - the value you are passing is then silently truncated. So CREATE PROCEDURE [dbo].[GetTransactionFeeType_Pager] @PageIndex INT = 1 ,@PageSize INT = 10 ,@ProgramName VARCHAR(99) = '' ,@RecordCount INT OUTPUT AS ... does the trick by defining the length of...

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

oracle,stored-procedures,dblink

This is too long for a comment. There is the possibility that the error is here: select col2, col3 into v_var2, v_var3 from table1 where col1 = var1; Perhaps col2 and col3 can be longer than 100/200 characters. You might go for: select substr(col2, 1, 25), substr(col3, 1, 75) into...

How to fix ORA-01427

sql,oracle,stored-procedures,plsql

The only place (that I notice) where you have a "single row subquery" is: UPDATE t_name_match nm SET nm.DESCRIPT = (SELECT x.DESCRIPT from CHAR_FEATURE_XRF x where x.KBID = nm.KBID and x.SYMBOLID = nm.SYMBOLID ); How you fix it depends on what you want to do. Two simple ways are MAX()...

Use data separated by delimiters in Sql Procedure

sql,sql-server,sql-server-2008,stored-procedures

You can use following function: CREATE FUNCTION Split ( @InputString VARCHAR(8000), @Delimiter VARCHAR(50) ) RETURNS @Items TABLE ( Item VARCHAR(8000) ) AS BEGIN IF @Delimiter = ' ' BEGIN SET @Delimiter = ',' SET @InputString = REPLACE(@InputString, ' ', @Delimiter) END IF (@Delimiter IS NULL OR @Delimiter = '') SET...

Procedure call function with table fields

mysql,function,stored-procedures

You can do something like this; Remove the valped param from sp and update the table after row insert using your function calculation. CREATE PROCEDURE ItemPedido (numped int, int codtab, codpro int (4), qtdped int, decimal valitem (11.2), datped date) BEGIN insert into itens_pedido (numped, codtab, codpro, qtdped, valitem, datped)...

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

php,mysql,stored-procedures

If your procedure1 has 4 parameters you should: $query_text = "CALL procedure1(?,?, @mobNum, @firstInsert);"; $stmt = $this->pdo->prepare($query_text); $stmt->bindParam(1,$number); $stmt->bindParam(2,$code); $result = $stmt->execute(); print_r($stmt->fetchAll(PDO::FETCH_ASSOC)); If just 2 change first line to $query_text = "CALL procedure1(?,?);"; then. EDIT If you still ahve your error, try to handle sql error: if ($result) {...

SQL Stored Procedure with Dynamic Column Names

c#,.net,sql-server,stored-procedures,sql-server-2012

You can wrap your query in a procedure just as it is. Then you can execute it from from your app/web and get a DataTable as the result. When you bind the DataTable to the DataGrid it should automatically render the columns in the DataGrid CREATE PROCEDURE GetDynamicReport @StartDate as...

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

sqlparameter reads zero as an object. Be careful to used zeroes, read this post link Must convert zero to integral type first . Your code should look like this. new SqlParameter("@IsPublic", Convert.ToInt32(0)); ...

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

xml,web-services,tsql,stored-procedures

Your XML has two namespaces that matter; soap namespace declared at the root element and default namespace declared at <RegisterUserResponse> element. So you need to pass namespace prefixes mapping as parameter for sp_xml_preparedocument : declare @nsmap varchar(200) = '<root xmlns:d="http://tempuri.org/" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"/>' declare @iXDoc int EXEC sp_xml_preparedocument @iXDoc OUTPUT, @Response, @nsmap...

SQL - Stored Procedures - get values from subquery

sql,stored-procedures,view

Why cant you DECLARE three variables and use it as parameter to the procedure. DECLARE @sch_id INT,--change the datatype based on your schema. @Vendor_ID INT, @Sch_TaskID INT SELECT @sch_id = ScheduleID FROM Schedule WHERE Job_No = 'ABC' SELECT @Sch_TaskID = ScheduleTaskID FROM ScheduleTasks ST INNER JOIN Schedule S ON St.ScheduleID...

Alias case statement

SELECT REPLACE (ID, '-', 'A') MyID Or if you were wanting to append the 'A' SELECT REPLACE (ID, '-', '') + 'A' MyID ...

passing parameter values to stored procedure when calling from another procedure

sql,sql-server-2008,stored-procedures

As simple as,for example: insert into @temp_tbl exec sp1 @id=1, @type=2, @orderno=3 but you obviously need the values to pass (which could come from other parameters, etc). DECLARE @OrderNoSource INT = 33; insert into @temp_tbl exec sp1 @id=1, @type=2, @[email protected]; So, a more complete example: BEGIN TRANSACTION GO -- necessary...

SQL SERVER Select MAX Procedure

sql-server,database,tsql,stored-procedures

If i understand correctly, you can have many rows per "iClic" and only want to update the row if the most recent has a status = 9. It would help if the table had a true PK. But I believe this will work, given: CREATE TABLE T_STATUS_CLIC (iClic INT ,...

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?

You are using a cursor, to start with... I would go with an update statement using a join to a derived table. Something like this should do the trick: UPDATE t1 SET TInterest = SumTInterest FROM PersonalLedgerForTFundAndShareClone t1 INNER JOIN ( SELECT EmNo, SUM(TInterest) AS SumTInterest FROM PersonalLedgerForTFundAndShareClone WHERE TDate...

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

sql-server,tsql,date,stored-procedures,cursor

This can easily be done with a recursive CTE: ;WITH cte AS ( SELECT @Start AS [Month] UNION ALL SELECT DATEADD(MONTH, 1, [Month]) FROM cte WHERE [Month] < @End ) SELECT [Month] FROM cte OPTION (MAXRECURSION 0) ...

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

asp.net,vb.net,stored-procedures,hash,password-protection

A very simple aproach is to use a MD5 hash. public class MD5 { public static string Hash(string message) { // step 1, calculate MD5 hash from input System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create(); byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(message); byte[] hash = md5.ComputeHash(inputBytes); // step 2, convert byte array to hex string StringBuilder...

SAS: getting the filesize of created DBF file

stored-procedures,sas,sas-macro

filename() is restricted in environments with OPTION NOXCMD, which is by default set for server environments. This is for security reasons (as XCMD allows shell access). You can enable this with by enabling OPTION XCMD, though your server admin (if this is not you) would have to enable it on...

Filtering data in a table for a required condition

php,mysql,stored-procedures

My colleague helped me to figure out the quick response for the above scenario. The query is select make,model,group_concat(year ORDER BY year ASC) as yearList from apd_cars group by model,make having yearList NOT like '%2014%' order by make,model; ...

creating stored procedure in mysql calculate profit from product table

mysql,sql,database,stored-procedures

You forgot the () after the procedure name. Also, you should set a delimiter: DELIMITER // CREATE PROCEDURE sp_profit() BEGIN SET @v1:= (select sum( cost_price * current_stock) from product); SET @v2:= (select sum( selling_price * current_stock) from product); SELECT (@v2 - @v1); END; // ...

SET NOCOUNT ON is not working under ubuntu

php,sql-server,stored-procedures,yii

below is example for setting nocount on in stored procedure SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1>...

SSRS Average Time in Minutes and then Format to HH MM

sql,stored-procedures,reporting-services,formatting

You could add your own formatting function to the Report Code section: Public Function MinsToHHMM (ByVal Minutes As Decimal) Dim HourString = Floor(Minutes/60).ToString() Dim MinString = Floor(Minutes Mod 60).ToString() Return HourString.PadLeft(2, "0") & ":" & MinString.PadLeft(2, "0") End Function and then call it in the cell expression like this: =Code.MinsToHHMM(Avg(Fields!test.Duration.Value))...

Disallow NULL parameters to stored procedures in MySQL/MariaDB

mysql,stored-procedures,mariadb,stored-functions

You would need to validate passed parameter values yourself. If you're using MySQL 5.5 and up you can make use of SIGNAL. DELIMITER // CREATE PROCEDURE my_procedure (IN param1 INT) BEGIN IF param1 IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'NULL is not allowed.'; END IF; -- do...

Execute a Stored Procedure with different missing parameter calls

You would call the stored procedure with named parameters. For instance: exec [dbo].[insertIntoTableX] @column_3 = 3, @column_5 = 5; ...

Sql server - stored procedure - concatenated results

tsql,stored-procedures,sql-server-2012

It seems like what you need is to group by ID and State SELECT ID_Agency ,State ,Count(*) [ID_Count] ,AVG(Duration) [Avg_Duration] FROM Transaction group by ID_Agency ,State This will give you one row per ID and State with the 2 values you need....

Retrieve the Return Result with Chronological Order Based on Parameter

Without an explicit ORDER BY Statement, SQL Server will determine the order using a variety of means e.g. collation\indexes\order of insert etc. This is arbitrary and will change over time! No Seatbelt - Expecting Order without ORDER BY If you want to guarantee the order of your output, you need...

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tbl2')) BEGIN -- tbl2 exists, so just copy rows INSERT INTO tbl2 SELECT * FROM tbl1; END ELSE BEGIN -- tbl2 doesn't exist, so create new table tbl2 and copy rows SELECT * INTO tbl2 FROM tbl1; DROP tbl1; END This...

Oracle stored procedure wrapping compile error with inline comments

database,oracle,stored-procedures,plsql

If you execute, show errors after the call, you will see a message like this: 1/70 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior Here is a simplified version of your...

TSQL Updating Table with Counts from Other

sql-server,stored-procedures,datatable,syntax-error,sql-insert

No need for a temp table: select reqQueue, count(t.reqId) as TotalRecords from apsSupport_queues q left join apsSupport_tickets t on q.reqQueue = t.ReqQueue group by q.reqQueue ...

Stored Procedure for insert

You need the form of insert that takes a select rather than a values: insert into theTable (col1, col2, ...) select col1, col2, ... from ... where... All the features of a select are available (column aliases, grouping, joins, sub queries, ...)....

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

mysql,sql,stored-procedures

You can get the worse user using: select r.SifKorisnikPK from Rezervacija r where status = 'P' group by SifKorisnikPK order by count(*) desc limit 1; You can then use this in a query to get more information: select k.* from PremiumKorisnik k join (select r.SifKorisnikPK from Rezervacija r where status...

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

oracle,stored-procedures,plsql

Designing procedures requires a bit of planning and thought. You need to understand the requirements and implement them in code. So the first requirement is: the user is asked to insert a department they wish This means your procedure should take a single parameter: CREATE OR REPLACE PROCEDURE outputBuilding (i_building_type...

PHP with stored procedure - cursor output in Oracle

php,oracle,stored-procedures,oci

If it's usefull for someone else: because of the use of CLOB columns and parameters I needed to add... Binding variables: $clob['apple'] = oci_new_descriptor($conexion, OCI_D_LOB); oci_bind_by_name($rs, 'apple', $clob['apple'], -1, OCI_B_CLOB); $clob['apple']->writeTemporary($params_values['apple']); oci_bind_by_name($rs, 'banana', $params_values['banana']); Fetching result: while (($row = oci_fetch_array($cursor, OCI_ASSOC + OCI_RETURN_LOBS + OCI_RETURN_NULLS)) != false) { ...

DB2 Z/OS 10 Equivalent of AUTONOMOUS procedure

stored-procedures,db2

You could simulate (Actually, this is the way autonomous SPs works in LUW) the autonomous option by calling an external stored procedure (in C or Java) that creates another connection to the database and call the "autonomous" SP. By recreating the connection from an external SP, you will have the...

MySQL Procedure IN variable don't work properly

mysql,stored-procedures

You need dynamic sql. Solution for returning count of any table passed as a parameter to sp DELIMITER $$ CREATE PROCEDURE `countRows`(IN v varchar(30)) BEGIN SET @t1 =CONCAT("SELECT COUNT(*) FROM ",V); PREPARE stmt3 FROM @t1; EXECUTE stmt3; DEALLOCATE PREPARE stmt3; END$$ DELIMITER ; Execution call countRows('sometable'); Update: Solution for returning...

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

sql-server,tsql,stored-procedures,nested-queries,nested-query

The CASE statement returns a value, it does not act as an IF statement by changing the SQL query (if this, then do that). You would need to modify your where statement to something like the following: AND ( (@status = 99 AND v.idStatus NOT IN (5, 1, 4, 20))...

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

I've added Chart_id in the query since you need it there to update it. This creates a common table expression that you can use to update records. ;WITH Update_Complex_Query AS ( SELECT TOP 400 c.pat_id AS cpPatId , (LEFT(c.fname, 1) + LEFT(c.lname, 1) + @EnvironmentKey + + RIGHT('00000000' + convert(VARCHAR,...

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

powershell,stored-procedures,output-parameter

Modify your script to be like below $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=myserver;Database=mydb;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "testsp3" $SqlCmd.Connection = $SqlConnection $SqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'; <-- Missing $outParameter = new-object System.Data.SqlClient.SqlParameter; $outParameter.ParameterName = "@answer"; $outParameter.Direction =...

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

sql,sql-server,tsql,stored-procedures,sql-server-2012

I am looking for a standard piece of code that i can stick into the procedure that can loop through all parameters for the proc and retrieve the current values passed in-- You can get all values passed in for an sp using below query Example : I have below...

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

Replacing this (SELECT [reservationno] FROM roombookingdetails) from first insert with this (SELECT Isnull(Max(reservationno) + 1, 1) FROM roombookingguestdetails) and this (SELECT [reservationno] FROM roombookingdetails) from second insert with this (SELECT Isnull(Max(reservationno) + 1, 1) FROM (RoomBookingOccupancy) solved my problem...

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

Just add a justment before the execute statement. open v_result_cur; repeat fetch v_result_cur into v_id; IF NOT v_done THEN select v_id; END IF; until v_done end repeat; close v_result_cur; ...

Stored procedure with dynamic query “Error”

I think you want to append your parameters to your query, like below. Also for dates and strings you need to put them inside single quotes like this '2014-01-01' IF(@pFromDate != '') BEGIN SET @strSQL += ' and o.RequestDateTime >= ''' + @pFromDate + '''' END Note: I'm not positive...

SQL Server Stored procedure declaring variable in different way

php,mysql,stored-procedures

@UPA nvarchar(30) = NULL is the procedure argument/parameter (in this case, an optional parameter though since it's being declared like that having NULL as the default value. So while calling procedure if you don't supply @UPA, it will assume NULL) that you need to supply while calling the procedure like...

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

mysql,stored-procedures

I created a small test that can be useful: DELIMITER // CREATE PROCEDURE `createPost`( IN `_value` VARCHAR(50), OUT `_out_post_id` BIGINT UNSIGNED ) BEGIN INSERT INTO `post` (`value`) VALUES (`_value`); SET `_out_post_id` := LAST_INSERT_ID(); END// CREATE PROCEDURE `createPostMedia`( IN `_in_post_id` BIGINT UNSIGNED ) BEGIN INSERT INTO `postmedia` (`post_id`) VALUES (`_in_post_id`); END//...

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

delimiter $$ create procedure return7 () BEGIN select 7; END $$ call return7(); you are free to re-use this code...

Ignore other results if a resultset has been found

sql,sql-server,tsql,stored-procedures

Maybe something like this? SELECT * FROM StatsVehicle WHERE ( -- Removed the following, as it's not clear if this is beneficial -- (@referenceModeleId IS NOT NULL) AND (ReferenceModelId = @referenceModeleId) ) OR (@referenceModeleId IS NULL AND ( (ReferenceMakeId = @referenceMakeId) OR (@referenceMakeId IS NULL) ) ) ...

Generic Calls to stored procedure for data download from C#

c#,sql-server,generics,stored-procedures

There are other ways to do it, however I'm not so sure that they are better. One way is to use SqlCommandBuilder.DeriveParameters method to populate your SqlCommand.Parameters dynamically. The main advantage of using this method is that you don't need to keep the parameters list in another place besides the...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures

The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...

SQL Procedure: Generating random password and storing it in database

sql,sql-server,stored-procedures,casting

Essentially, by generating your hash using this PWDENCRYPT method, your salt is already stored in the db. If you look at the formula to create the hash1 value, the plain salt value is included in "plaintext" within the hash1 value itself, outside of the actual HASHBYTES call. set @hash1 =...

Cast sql_variant into data_type provided as varchar

Yes, you can pass sql_variants as parameters to sp_executesql, but you'll need to continue down the dynamic SQL route with the "Cast to" type, and use the name of the Type that you've determined for the column to be used in a CAST. Take this for example: CREATE TABLE Foo...

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

sql-server,stored-procedures,dynamic-sql,sqlxml,sql-function

The problem is not the local-name function. It is entirely the fact that you are concatenating in the @cmd variable into your Dynamic SQL without properly escaping the embedded single-quotes. This line: EXEC(N'USE '[email protected]+'; EXEC sp_executesql N''' + @cmd + '''; USE master') should be: SET @cmd = REPLACE(@cmd, N'''',...

Fire trigger from DocumentDB stored procedure

stored-procedures,triggers,azure-documentdb

Triggers can not be called from the server-side SDK (e.g. from inside another trigger or sproc).

Insert Into two Tables with Foreign Key relationship

sql-server,asp.net-mvc,entity-framework,sql-server-2008,stored-procedures

Try something like this (this is simplified, of course - but it shows the basic mechanism that should be used - instantiating objects and connecting them via their navigation properties, and saving only once for the whole object graph): // establish the DbContext using (TicketModel ctx = new TicketModel()) {...

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

sql-server,sql-server-2008,stored-procedures

Consider using extended properties to store meta-data. This is much cleaner than parsing the module text. EDIT: The example below returns all parameters plus descriptions for those parameters with extended properties. In your code, you can pass the schema and object names as parameters instead of the local variables used...

Weird Behaviour of MySQL stored procedure

c#,mysql,stored-procedures

Try following code. 0 is treated in different manner in MySql while passing as parameter value. cmd.Parameters.Add(new MySqlParameter("lastItem",MySqlDbType.Int32).Value=0); cmd.Parameters.Add(new MySqlParameter("lastValue", MySqlDbType.Int32).Value=0); No need to external call cmd.Dispose(); .Net Clr will take care of this internally. You are just passing 0 value to these parameters so, just create two variable inside...

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

c#,entity-framework,variables,stored-procedures

This is pretty straight forward. // Initialize dependency to data entities private Entities _dataContext; public ClassName() { _dataContext = new Entities(); } public IQueryable<EntityName> MethodName(string filter) { // Initialize var records = _dataContext.Entity // Constrain your results .Where( x => filter == null || x.Property1.Contains(filter) ); return records; } If...

Converting Datasets and Shared Datasets to t-sql stored procedure

tsql,stored-procedures,ssrs-2012

There's not an automatic way to do this. You might be able to cobble something together to do it though. SSRS reports are in an XML format. The Datasets are in a < DataSets > element. Unfortunately, I don't know how helpful it would be since the parameters would need...

Multiply time with a decimal value in mysql?

mysql,database,stored-procedures

SEC_TO_TIME( TIME_TO_SEC( '00:04:18' ) * 1.7 ) = '00:07:18.6' SEC_TO_TIME( TIME_TO_SEC( '00:04:18' ) * 1.7 ) = '00:07:44.4'

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