New answers tagged stored-procedures
3
You always need to compile the entire package body -- it is one item of code. You can create functions and procedures inside other procedures, but that does not change the need to recompile the package body.
What problem are you trying to solve here?
4
Answer is yes. :)
CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
RETURNS VOID AS
$func$
BEGIN
EXECUTE format('
CREATE TABLE IF NOT EXISTS %I (
id serial PRIMARY KEY,
customerid int,
daterecorded date,
value double precision
)', 't_' || t_name);
END
$func$ LANGUAGE plpgsql;
I am using format() with %I to ...
0
Tested it out myself and yes the stats are counted from an encrypted stored procedure if anyone else is looking for the answer.
1
Because the second one is, coincidentally, syntactically valid but not the correct way to do what you are doing. You are trying to do dynamic sql without using dynamic sql. What you are doing in #2 is actually putting the literal string ', param_CRITERIA ,' in the where clause, not the content of that variable.
The only way to do what you are trying to do ...
0
It depends a lot on what the data is, what you're doing with the data, what the bandwidth between client and server is, what stored procedure language you're using, etc.
Test it and see. You can:
Use the psql \timing command to get the final execution time including results transfer from the client perspective;
Use log_min_duration_statement = 0 in ...
3
The best thing you can do to understand this behaviour better is to ask the software vendor. If you don't want to grant those permissions, lodging a bug report might be appropriate.
I'm going to hazard a guess that EMS SQL Manager is trying to be too clever, and check whether you have Alter_routine_priv or Create_routine_priv so that it can return a warning ...
3
From BOL, on INFORMATION_SCHEMA.ROUTINES:
Column name: ROUTINE_DEFINITION
Data type: nvarchar(4000)
Description: Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL. To ensure you obtain the complete definition, query the ...
5
Use one of
sys.sql_modules
Red Gate SQL Search (free!)
OBJECT_DEFINITION
Never use
INFORMATION_SCHEMA
sys.syscomments
1
First of all, I don't think it's a good idea to use dynamic sql in the case when you have nothing really 'dynamic'.
If you still want to do it, it seems that you are missing an important point about prepared statements. You are preparing, executing, and then deallocating prepared statement in one procedure; thus, next time you call your procedure, you are ...
0
Dynamic queries are all about being dynamic - so can't be cached...
What I'd do is indexing the tables based on the fields you'll use in your WHERE clause, and create Views from affected tables with ORDER BY clause (and make the dyn-query SELECT work from the Views instead of the tables of course).
I'd also recommend reading this guy here: API Prepared ...
1
There is no way around it. You must use the DELIMITER command. Why ?
If you ever perform a mysqldump of the stored procedures, each stored procedure begins with
DELIMITER ;;
and ends with
DELIMITER ;
Here is a post where I mentioned this before : DROP PROCEDURE IF EXISTS not included in mysqldump
Try dumping one stored procedure with mysqldump and ...
1
It shouldn't matter where the DELETE happens, the trigger should fire from the stored procedure. This begs the question, are you sure the DELETE is actually happening within the stored procedure, ie does the COMMIT actually happen?
As an alternative to your approach, if the tables are InnoDB, you could use Foreign Keys on each of your tables that reference ...
4
Reformatting your code shows the problem. All I have changed is whitespace and added a comment:
HAVING
(
SELECT COUNT(*) FROM
(
SELECT *
FROM dbo.ContractDailyRoomAllocation da2 -- <-- da2 defined here
WHERE da2.ContractId = DA.ContractId
AND da2.RoomTypeId = DA.RoomTypeId
...
1
You need to change the delimiter to create a procedure, otherwise MySQL will try to commit your query on select 'hello pro';
Change your procedure to:
DELIMITER //
create procedure test_pro()
begin
select 'hello pro';
end
//
From MySQL Documentation:
If you use the mysql client program to define a stored program containing semicolon characters, a ...
3
You have declared the alias da2 inside a subquery,
then you referenced it outside of that subquery, where it is not recognized.
if you need "The last 2 AND's" as a filter of table T (the records that will be counted),
then try using this instead:
HAVING
(SELECT COUNT(*)
FROM dbo.ContractDailyRoomAllocation da2
WHERE da2.ContractId = ...
1
If the effect isn't required until a later point in time, I would execute the payload at that later point in time and not via trigger. This way you can collect multiple updates on the underlying table and avoid redundant calls of the DDL script.
Depending on your circumstances, a timestamp column or simple boolean flag in the underlying table might suffice ...
1
Thanks for your feedback. NuoDB provides standards compliant SQL support, which includes strong language integration with LINQ and the Entity Framework. Besides .NET language support, NuoDB also supports JDBC, ODBC, Node.JS, Ruby/JRuby ActiveRecord & RAILS, PHP/PDO, and Hibernate.
Most importantly we do that with elastic scale-out support, an ...
1
This is a misunderstanding. A function like this is perfectly valid:
CREATE OR REPLACE FUNCTION test()
RETURNS SETOF record AS
$func$
VALUES (1, 2), (3, 4); -- 2 rows with 2 integer columns
$func$ LANGUAGE sql;
However, since it returns anonymous records, you are required to provide a column definition list with every call , just like the error message ...
3
The manual for CREATE PROCEDURE states: "Not Yet Implemented"
The documentation of the SELECT statement neither shows a WITH nor an alternative like connect by (as e.g. Cubrid does).
For bulkloading there is nuoloader but there doesn't seem to be an API for it.
But you will probably get a better answer if you post directly to the NuoDB forum: ...
0
You shouldn't be seeing a perceptible performance difference from preparing a statement compared to having one hard-coded into your stored procedure. You also should see no difference at all from the fact that the procedures are stored in a different database from your data.
From the start, though you're presenting us with two different unknowns -- ...
Top 50 recent answers are included
