Tag Info

Hot answers tagged

3

You say that your procedure has two parameters: an input INTEGER and an output VARCHAR, however, you call a procedure with only one argument, therefore DB2 is looking for the procedure that has only one INTEGER argument. You should call it like so: call ADMINIST.LOG_MSG_TEST(1,?); where the question mark is a placeholder for the output argument.


3

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan. If you are ...


2

Is it possible to execute stored procedure in another stored procedure in oracle Yes. Demo Let's say I have two procedures, P1, and P2, such that Procedure P1 takes an input as employee number, and gives the employee name as output. Procedure P2 will be called inside P1 which will take the output of procedure P1 as input, and give it as output. ...


2

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by. The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query ...


2

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as DELIMITER $$ USE `smsdev`$$ DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$ CREATE /*!50017 DEFINER = 'smsdev'@'%' */ TRIGGER `pageprivilege_update` ...


2

LOG_CONF_BP is a bufferpool. That is a chunk of memory dedicated to doing something (usually servicing one or more tablespaces, which is where tables reside). So what is happening is your stored procedure is trying to create an object that already exists. So what is going to need to change is the stored procedure. I would ask if your stored procedure needs ...


1

You can use prepare statement for this and also need a delimiter for the procedure something as delimiter // create procedure table_create (in tablename varchar(100)) begin set @table_ = concat(tablename, '_meta'); set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;"); prepare stmt from @qry; execute stmt; ...


1

If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server. Exporting as a CSV can be achieved via BCP. There's more details in this answer, but the main idea is: bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T You would then attach the file ...


1

Yes you can send the report via HTML format, for example as listed in MS: Scenario: This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail ...


1

To my knowledge, there are no tools like doxygen that can make doc from parsing SQL annotated code. About keeping comments, note that comments in a stored procedure can be saved with the procedure code. When a CREATE PROCEDURE statement is sent from the mysql command line client to the server, make sure the mysql client is using the -comments option. See ...


1

I can't for the life of me understand why this question received a flag for being off-topic. I think it is interesting and very relevant for the working DBA. Unfortunately, as far as I can ascertain, it is impossible to do this automatically. You can run two commands SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'yourdb' ...


1

If you are looking to set the ID variable parameter, you need to specify it as an OUTPUT parameter, and set it's value in your query: Create procedure addFaculty @ID int OUTPUT as begin IF NOT EXISTS (Select AccountType from UserTable where AccountType = 'Faculty') begin Insert into CredentialsTable values ('iamafaculty'); Select ...


1

Credits to @sabin bio for this Using the Scope_Identity() returns the last identity in any table in the current session. The scope of the Scope_Identity() is limited only to the current scope which is where the last insert statement has been made. https://msdn.microsoft.com/en-us/library/ms190315.aspx


1

Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons So there are good reasons to use prepared statements: Save on query parsing Save on data conversion and copying Avoid SQL Injection Save memory on handling blobs There are also drawbacks and chewats of using prepared statements: Query cache ...



Only top voted, non community-wiki answers of a minimum length are eligible