New answers tagged

0

Not quite what you asked for, but this query may help: mysql> SELECT db, name FROM mysql.proc WHERE body LIKE '%temporary%'; +---------------+-----------------------------------+ | db | name | +---------------+-----------------------------------+ | common_schema | rdebug_compile_routine | | common_schema ...


1

You cannot use @variable_name to declare variable. @ can be used only for session variables. Change your code to this, it will work DELIMITER // CREATE PROCEDURE one_off_update_user_info_view_type() BEGIN DECLARE batchsize BIGINT DEFAULT 1; END //


2

You use DECLARE in compound-statement syntax to declare a local variable, cursor, condition or handler. However, your variable @batchsize is a session global variable due to the presence of the @ prefix, and it is an error to attempt to redeclare a global variable. Change the name of your variable from @batchsize to batchsize, and then read up on MySQL ...


3

There is no contradiction in that documentation. The confusion here seems to be that there is an implied assumption regarding the reader's expectations of how variable scope works. In many other languages, variables declared in an outer scope are visible to subroutines / functions. For example (and this is not how it works in T-SQL; I am just illustrating ...


1

As DBNull pointed out, you are experiencing this challenge because the Team column is breaking First Normal Form. The values in that column should be atomic. You have two options here. The first is to follow DBNull's recommendation of having the teams registered in their own Team table and having a separate TeamGroup table to store Team Groups. While you're ...


4

In SQL Server 2014 & up, new cardinality estimation logic was introduced. From BOL : The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance. The new cardinality estimator incorporates assumptions and algorithms that work ...


3

You cannot refer to an alias defined in the same SELECT list. There are various workarounds. Assuming your query is something like this: select nome, -- various complex expression / subqueries <complex_expression_or_subquery> as Laudos_Expedidos, <complex_expression_or_subquery> as Laudos_Expedidos2 ...


0

Try this : DELIMITER $$ DROP PROCEDURE IF EXISTS `do_thing` $$ CREATE PROCEDURE `do_thing`() BEGIN -- DECLARE STATEMENTS first DECLARE how_many INT; DECLARE `_rollback` BOOL DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1; -- SELECT COUNT(*) INTO how_many FROM things; -- ...


1

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks. Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/...


0

You are missing the error handling piece (i.e. TRY...CATCH). Please see the Stored Procedure template that I posted in the following DBA.StackExchange answer: Are we required to handle Transaction in C# Code as well as in Store procedure


1

If there is a single SQL statement of moderate size (so it fits in a single network packet) then executing that statement from the application or packaging it as an SP and executing the SP will be identical network-wise. However, a pattern I often see is that an application function requires several SQL statements to produce a result. Let's say we are ...


15

A) Scale The middle tier can be scaled easily - hence the web farm concept. Scaling out the DB tier is much more difficult. While some products can do this it is not yet trivial and mainstream. B) Cost Typically web servers are common-or-garden boxes. DB servers, however, tend to be larger, more complex and more resilient. This all translates to "expensive....


0

Look I do it this way in an audit table, each time this function is executed I put it at the foot of the procedure and called each time the query is run. and consultation is : update audit set calls = calls + 1 where func = 'sptest' ; In this way you can already know how many times was called and the date


1

You can create a mapping table with two columns, one key (a string containing the called procedure name plus arguments if you need that) and one value (an integer containing the number of times the corresponding procedure has been called). The first thing you do in your procedure you want to monitor is to update the mapping table to record the procedure ...


2

The core problem is that your query can potentially match on two different procedures in two different databases that happen to have the same object_id. While there isn't a reliable way to reproduce it on demand, I've seen it enough times to know it is common, and your query needs to protect against it. You can do that with a WHERE clause: WHERE s....


2

We have all been there. It sounds like you don't have a specific question, but just need some help getting started. You need to learn about the sys tables available in SQL Server and the DMV's. They are insanely useful. Ones that will be helpful in your case: sys.procedures - gets you a list of your sprocs sys.object - gets a list of all objects ...


0

I would look at these two additional posts at stackoverflow: http://stackoverflow.com/questions/14506871/how-to-execute-a-stored-procedure-inside-a-select-query http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure As aasim.abdullah has said, I would look into Table valued functions more too. Information about them ...


1

Don't do the SELECT MAX thing; you can run into concurrency problems there. If the patterns are simple enough, and have only a single increasing numeric portion, you can probably get away with using an identity column plus a persisted computed column to create the formatted string value: CREATE TABLE IDTest ( row_id int IDENTITY(1,1) NOT NULL, ...


3

Contradiction Detection could kick in to make sure only one of the statements is run, and in my simple test it did as long as there was a statement-level recompile hint, but why risk it? For example: USE tempdb GO -- CREATE SCHEMA agg --DROP TABLE agg.DataMin --DROP TABLE agg.DataMedian --DROP TABLE agg.DataWeightedAverage --GO CREATE TABLE agg.DataMin ( ...


1

This question actually came up 2.5 years ago on StackOverflow, and I posted an Answer there: SQL print line number in comment of dynamically created stored procedure? The relevant portion of that Answer is copied below: You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE(...



Top 50 recent answers are included