tsql – Tarik Billa

Is there a trick for using TSQLMonitor with a TSQLConnection that uses the new ODBC dbExpress driver?

August 26, 2023 by Tarik

Try this out: procedure TForm2.Button1Click(Sender: TObject); begin try Connect; SQLMonitor1.SQLConnection := SQLConnection1; SQLMonitor1.Active := True; ExecuteQueries; SQLMonitor1.SaveToFile(‘D:\\Log.txt’); except on E: Exception do ShowMessage(‘Exception ocurred!: ‘ + E.Message); end; end; procedure TForm2.Connect; begin SQLConnection1 := TSQLConnection.Create(nil); SQLConnection1.ConnectionName := ‘odbcinterbaseconnection’; SQLConnection1.LoginPrompt := False; SQLConnection1.LoadParamsOnConnect := True; SQLConnection1.Connected := True; end; procedure TForm2.ExecuteQueries; var Query: String; begin try … Read more

How do you get the identity value after using MERGE when there is a match?

August 21, 2023 by Tarik

In the case when the record already exists, you can store the matched id into a variable like this: DECLARE @MatchedId INTEGER; MERGE MyTable as t …. …. WHEN MATCHED THEN UPDATE SET @MatchedId = t.MyTableId; UPDATE: Here’s a full example. This demonstrates one way: DECLARE @UpdateVariable bit DECLARE @ChangeResult TABLE (ChangeType VARCHAR(10), Id INTEGER) … Read more

T SQL merge example needed to help comprehension

August 14, 2023 by Tarik

If you aren’t familiar with join statements then that is where you need to start. Understanding how joins work is key to the rest. Once you’re familiar with joins then understanding the merge is easiest by thinking of it as a full join with instructions on what to do for rows that do or do … Read more

When to use Transactions in SQL Server

August 7, 2023 by Tarik

You use transactions when the set of database operations you are making needs to be atomic. That is – they all need to succeed or fail. Nothing in between. Transactions are to be used to ensure that the database is always in a consistent state. In general, unless there is a good reason not to … Read more

T-SQL Format integer to 2-digit string

August 3, 2023 by Tarik

select right (’00’+ltrim(str( <number> )),2 )

How to convert Seconds to HH:MM:SS using T-SQL

June 27, 2023 by Tarik

You want to multiply out to milliseconds as the fractional part is discarded. SELECT DATEADD(ms, 121.25 * 1000, 0) If you want it without the date portion you can use CONVERT, with style 114 SELECT CONVERT(varchar, DATEADD(ms, 121.25 * 1000, 0), 114)

Truncate Datetime to Second (Remove Milliseconds) in T-SQL

June 11, 2023 by Tarik

This will truncate the milliseconds. declare @X datetime set @X = ‘2012-01-25 17:24:05.784’ select convert(datetime, convert(char(19), @X, 126)) or select dateadd(millisecond, -datepart(millisecond, @X), @X) CAST and CONVERT DATEADD DATEPART

using Switch like logic in T-SQL

May 31, 2023 by Tarik

You need to use If/Else If structure, like this: If @Type = 1 Begin INSERT INTO dbo.Credit ( CompanyName, PhoneNumber, City, State ) VALUES ( @CompanyName, @PhoneNumber, @City, @State) End Else If @Type = 2 Begin INSERT INTO dbo.Debit ( CompanyName, PhoneNumber, City, State ) VALUES ( @CompanyName, @PhoneNumber, @City, @State) End Else If @Type … Read more

T-SQL Count rows with specific values (Multiple in one query)

May 21, 2023 by Tarik

SELECT IGrp, COUNT(CASE WHEN Value1 > 1 THEN 1 ELSE NULL END) AS V1High, COUNT(CASE WHEN Value2 > 1 THEN 1 ELSE NULL END) AS V2High FROM Tbl GROUP BY IGrp

Testing for inequality in T-SQL

May 16, 2023 by Tarik

These 3 will get the same exact execution plan declare @id varchar(40) select @id = ‘172-32-1176’ select * from authors where au_id <> @id select * from authors where au_id != @id select * from authors where not (au_id = @id) It will also depend on the selectivity of the index itself of course. I … Read more