Hot answers tagged

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

merge production as target using raw as source on target.refno = source.refno when matched and souce.date > target.date then update set target.date = souce.date, target.value= souce.value when not matched then insert ... merge


3

This example code should work, but there are some things to watch out for. Since this is updating the tables while (presumably) another process is using that table to schedule jobs, concurrency could be an issue. For example, I'd assume there is another key field not mentioned that the scheduler process is using to identify the jobs. If it's the xNumber/...


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 ...


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 ...


3

You're just missing the keyword THEN. Read more about the correct syntax here. Try it like this: use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(128) ) BEGIN IF (TRIM(_licenseName) = '') THEN select * from SystemLicensing; ELSE select * from SystemLicensing where LicenseName=_licenseName; END IF; END $...


2

You can see a count of all user-defined functions being called in the system view pg_stat_user_functions. There is no facility to write all function calls to the log. That would be quite bulky. If you're feeling adventurous, you can play with the settings debug_print_parse and debug_print_plan to get more detailed information about what is being called. ...


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 ...


2

Step 1: Get the SQL script when the function / procedure was created SHOW CREATE FUNCTION my_schema_1.my_function; We will get an result looks like CREATE DEFINER=`mysqladmin`@`%` FUNCTION `my_function` (i_paramter1 char(255), i_paramter2 int) RETURNS char(3) CHARSET utf8 COLLATE utf8_bin READS SQL DATA BEGIN DECLARE result_value char(3); ...


2

Thanks for the suggestion, but I want to replace rows -- not update field by field in every modified row. So after some research I think that the best thing I would have to do is to first delete all the old rows that will be replaced with new data. DELETE FROM TableA WHERE EXISTS (SELECT * FROM TableB WHERE TableB.refno = TableA....


2

Starting from SQL Server 2008,use can sys.dm_exec_procedure_stats which gives you the status on when was the stored procedure last executed select db_name(database_id) as dbname,last_execution_time from sys.dm_exec_procedure_stats


1

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command? DECLARE @RunStoredProcSQL VARCHAR(1000); SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]'; --SELECT @RunStoredProcSQL --Debug EXEC (@RunStoredProcSQL) AT [LinkedServerName]; Print 'Procedure Executed'; That's what I have used successfully in the ...


1

At the very basic level (and this is for SQL Server only as each vendor handles security slightly differently), IF others were not creating their own database objects (Stored Procedures, Functions, etc) and relying solely upon your API, then this is handled inherently through "Ownership Chaining". Ownership Chaining allows for implied permissions on objects ...


1

(emphasis added) Is there any way to definitively prove it was/wasn't No, there is no way to prove in any definite way that it has been executed, outside of coding the stored procedure to do something like insert a record into a log table showing that it had been executed. In your particular case, this would also require a time machine in order to put ...


1

Don't use FIND_IN_SET ( SELECT GROUP_CONCAT ... ). Instead JOIN the two tables together.


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 //


1

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

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 ...



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