Hi, I'm quite new to subject of writting stored function for mySQL database, hence i'm not sure if what i'm trying to do here is possible at all. I need a function that will return a column from random row from a table. I don't want to use ORDER BY RAND() method to do that, rather i would do this like this: DECLARE MAX_COUNT INT DEF...
SQL Server 2005 has great sys.XXX views on the system catalog which I use frequently. What stumbles me is this: why is there a "sys.procedures" view to see info about your stored procedures, but there is no "sys.functions" view to see the same for your stored functions? Doesn't anybody use stored functions? I find them very handy for e...
What is the largest size text usable variable in a MSSQL Stored Procedure. I'm seeing that largest size you can use is a varchar(8000). We can use ntext, text, etc. So does it require stitching varchars together? What about if I used a CLR Stored Procedure?? ...
It's just for test, but pretty strange: DELIMITER | DROP FUNCTION IF EXISTS get_idiscussion_ask| CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DETERMINISTIC BEGIN DECLARE done INT DEFAULT 0; DECLARE body varchar(600); DECLARE created DATETIME; DECLARE anonymous TINYINT(1); DECLARE screen_name va...
Description: the query actually run have 4 results returned,as can be see from below, what I did is just concate the items then return, but unexpectedly,it's null. I think the code is self-explanatory: DELIMITER | DROP FUNCTION IF EXISTS get_idiscussion_ask| CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DE...
I have a stored function in MySQL: CREATE FUNCTION `login_count`(o INT, start_date DATE, end_date DATE) RETURNS INT READS SQL DATA BEGIN DECLARE total_count INT; SELECT COUNT(*) INTO total_count FROM logins as l WHERE `order_id` = o && modified BETWEEN start_date AND end_date; RETURN total_count; END Pretty basic, takes an id, start...
I've got a vb.net codebase using ado to connect to an Oracle database. We have lots of stored procedures that we call, some with multiple out parameters. However, I now need to call a stored function, and it's not clear to me how to get the result of the function back into my VB code. Edit: I'm returning an integer. How do I properly c...
Selecting the return value of an Oracle stored function that doesn't contain DML can be done by simply selecting the function: select function_name() from dual; If the function contains DML (in this case some inserts to log the arguments passed to the function), the above query is not allowed. (ORA-14551) How can I select/view the re...
Hi all, I need some opinions. I'm going to develop a POS and inventory software for a friend. This is a one man small scale project so I want to make the architecture as simple as possible. I'm using Winform to develop the GUI (web interface doesn't make sense for POS software). For the database, I am using Postgresql. The program wil...
I'm defining a simple stored function which works fine in my local testing database, but when I try to create it on the "real" server, it just doesn't get added to the 'proc'-table. I'm getting no errors or anything, it just doesn't get added (hence, the function can't be used of course). Both servers are using the same version, and I c...
I'm working with code I'm converting to Pgsql working with .NET. I want to call a stored function that has several parameters, but I'd like to bind the parameters by name, like so: NpgsqlCommand command = new NpgsqlCommand("\"StoredFunction\"", _Connection) command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("para...
I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged. I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate). I would like to provide a Default Binding for...
Hi all, I keep receiving an error with the following code. I am trying to make a function that will format a field (content=0.0032) into a varchar/percent (content=0.32%). At the moment i'm just trying to get format to work, and it throws up an error "Error Code : 1064 You have an error in your SQL syntax; check the manual that corres...
I'd like to do had a dynamic number of one start/end time pairs passed to a function as an input parameter. The function would then use the list instead of just one start, and one end time in a select statement. CREATE FUNCTION [dbo].[GetData] ( @StartTime datetime, @EndTime datetime ) RETURNS int AS BEGIN SELECT @EndTime =...
Hi, How can I make a MS-SQL stored function availabe in LINQ expressions if using the Entity framework? The SQL function was created with CREATE FUNCTION MyFunction(@name) ...). I was hoping to access it similarly to this: var data = from c in entities.Users where MyFunction(c.name) = 3; Unfortunately I have only .NET 3.5 available....
This works on MySQL 5.0.41, but on 5.1.31 it just says "failed to create function". I type this in the console: delimiter | <press enter> CREATE DEFINER=`root`@`localhost` FUNCTION `ucwords`( str VARCHAR(128) ) RETURNS varchar(128) CHARSET utf8 BEGIN DECLARE c CHAR(1); DECLARE s VARCHAR(128); DECLARE i INT DEFAULT 1; DECLARE boo...
Hi to all. I'm trying to add a sql (stored) function to Entity Framework, but unsuccessfully. I tried with right clicking on a table in .edmx and "Function Import", but the function is not shown there, although I added it (updated model from the database). Does anyone have solution for this? ...
I was wondering if it is possible in a mysql stored-function or stored-procedure compose a mysql query as a string variable you can execute later? I have a stored-function get_district_part ((district_id INT,county_id INT,city_id INT,zip_id INT)) RETURNS INT that references the table: CREATE TABLE IF NOT EXISTS `modx`.covereage_distric...
I've written a stored FUNCTION that calls itself, recursively. However when I run it in a query I get this shameless error: Error: 1424 SQLSTATE: HY000 (ER_SP_NO_RECURSION) Message: Recursive stored functions and triggers are not allowed. "Not allowed"? Right. Why don't we just disable WHILE loops also, while we're at it? ...
Hi all, I have just managed to enable full-text searching to make searching for products in my system easier. However, I am using an sp that was built by someone else and it is not returning any results for "Duty Call". In actual fact I am searching for "Call of Duty", a product in the system. If I type "Call of Duty" it does return a r...