Tag Info

Hot answers tagged

6

You can use WEEKDAY and DATE_ADD function to calculate the next weekday incoming. Here what you have to do: SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY); The query meaning: With DATE_ADD you will add an interval between the parameter NOW() and one condition assuming my weekday for Saturday is 5, you ...


5

Stop using things like varchar without specifying the length. Watch what happens here: CREATE PROCEDURE [dbo].[Reconciliation_broken] @Param1 varchar AS BEGIN SET NOCOUNT ON; PRINT @Param1; END GO EXEC dbo.Reconciliation_broken @Param1 = 'what happened?'; Results: w When you don't specify the length, you get silent truncation - in some ...


4

I have another variation of the formula guaranteed to work (unless there is a power failure) SET @now = NOW(); SET @nextsat = DATE(DATE(@now) + INTERVAL (5-WEEKDAY(@now)) DAY + INTERVAL 570 MINUTE + INTERVAL IF((HOUR(@now)*3600+MINUTE(@now)*60+SECOND(@now))>34200, IF(WEEKDAY(@now)=5,1,0),0) WEEK); In case you are wondering, 09:30:00 AM is ...


4

From here: (My emphasis below) The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other ...


4

PRINT @String; will yield that it says PhoneNumber = 123-4567 (which treats it like an expression, 123 subtract 4567, which equals -4444), not PhoneNumber = '123-4567' like in the example you hard-coded. So you should be escaping the values you're appending with two single-quotes: SELECT @String = ' UPDATE ' + @Location + ' SET PhoneNumber ...


3

Insert query: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type) SELECT Id, CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))), username, firstname, lastname, email, 'www.some_static_url.com', '1' FROM veeva.user ...


3

In SQL Server, a stored procedure's argument can be either a string literal or a variable. It certainly cannot be a string expression, like in your attempt. To resolve your issue, you could declare a variable, assign your expression to it and then pass the variable to the sp_send_dbmail stored procedure: DECLARE @myfilename nvarchar(255); SET @myfilename = ...


3

Declare a variable first - but use format 112 for this. Like this: DECLARE @query_attachment_filename VARCHAR(100) = 'pca-test-' + CONVERT(VARCHAR(10), GETDATE(), 112) + '.csv'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'support', ... @attach_query_result_as_file = 1, @query_attachment_filename = @query_attachment_filename, @query_result_header =0, ...


2

I've made a function where you just put the string and the output will be in Capital Letters: DELIMITER // CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1 DETERMINISTIC BEGIN DECLARE vPOSICION INT DEFAULT 0; DECLARE vTMP VARCHAR(150) DEFAULT ''; DECLARE vRESULTADO ...


2

For the unused stored procedures you can make use of dmv's especially sys.dm_exec_procedure_stat. To find unused stored procs, please see the answer as explained by Aaron Bertrand. Also, there is an good read to identify as explained here in the article How to find unused Stored Procedures in SQL Server 2005/2008 To tack down unused views: a) ...


2

Yes that's possible. Create the function with the user owning all those tables and use the SECURITY DEFINER modifier. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it. create or replace function foo() returns void as $body$ ... $body$ language plpgsql security definer; Also see the ...


2

But why the recompile didn't help? Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits. ...


1

Although it's all opinion I strongly favour using pull techniques for the following reasons: I think it's easier to keep track of the dependencies by using a purely pull-approach. In a way it's similar to the whole concept of queries on tables or queries on views on tables. It's all "pull", not "push". Most DBMSes have better control of transactions etc in ...


1

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table. Table lotteryTBL and resultsTBL: mysql> SELECT * FROM test.lotteryTBL; +----+------+------+------+------+------+------+-------------+ | ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew | +----+------+------+------+------+------+------+-------------+ | 1 | 1 | ...


1

Since this seems to be a data warehouse, is / can the recovery model be bulk logged (or simple)? That in addition with using TABLOCK hint on table_b could help a lot since the inserts could be minimally logged. I found a blog post by Itzik Ben-Gan on minimally logged inserts that could help.


1

CREATE Table dbo.[#TMP] (UId uniqueidentifier, Points int, DC datetime, CId int, Username nvarchar(50)) BEGIN insert into #TMP(UId, Points, DC, CId, Username) Select x.UId, sum(x.Points), x.DC, x.CId, x.Username from ( SELECT c.UId, (l.pointsup - l.pointsdown) as ...


1

Had a situation where a production environment had DB schema updated The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why. Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ...


1

One ad hoc improvement: poor: for root in all_fitting_roots LOOP FITTING := FITTING + 1; END LOOP; better: SELECT COUNT(DISTINCT ROOT_ID) into FITTING FROM ( SELECT UNIQUE_ID, ROOT_ID, FIRST_NAME, LAST_NAME, POSTAL_CODE, FSA FROM (SELECT p.*, CONNECT_BY_ROOT UNIQUE_ID AS ROOT_ID FROM UNIQUE_PHYSICIAN p START WITH PARENT_PHYS_ID IS ...


1

I did this once as an exercise in dynamic SQL. It's NOT a good idea for any production system. Primarily you add a huge risk of SQL Injection. This is where someone would pass an actual SQL command in as part of one of the parameters and you end up running it without realizing it. You can minimize this but even so there is the risk. Also there is a ...


1

CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)) You missed one more closed parenthesis in your statement CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1))) 2.The query you wrote for concatenation returns only first letter in capital letters of both fist and last names and it doesn't print space between them. example :First ...



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