sql quick journal

sql quick journal

sql server distinct syntax

Search for: sql server distinct syntax


select distinct folder_name, folder_id, f_tag_owner_userid from v_taggedFolders 
where f_tagname in ('android')
order by folder_name

SQL Server t-sql data types

Search for: SQL Server t-sql data types

These data types are documented here


long is not a type
double is not a type

float and real are documented here

integer types are documented here

Identity columns are

@@IDENTITY gives you the last identity value generated by the most recent INSERT statement for the current connection, regardless of table or scope.

SOF: Best way to get last identity inserted in a table

on @@rowcount and updates

System functions where @@rowcount is one of them are documented here

TSQL Reference

Not equal to operators doced

Throw is documented here

It is like a RaiseError

The error_number parameter does not have to be defined in sys.messages.

There is no severity parameter. The exception severity is always set to 16.

error_number: Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

message: Is an string or variable that describes the exception. message is nvarchar(2048).

state: Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.


THROW 51000, 'The record does not exist.', 1;


if (@@ROWCOUNT != 1) 
begin
	declare @errmessage varchar(100);
	set @errmessage = 'Invalid number of rows: ' + @@ROWCOUNT;
	THROW 51000, @errmessage, 1;  
end;

Declare variables are essential and described here

comparing null values

Set and select are compared here

setting multiple variables using select in tsql

Search for: setting multiple variables using select in tsql


IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
begin
       SELECT 'Weekend';
end;
ELSE 
begin
       SELECT 'Weekday';
end;

docs language control flow


exec sp_someproc a1,a2,a3

prevent changes from recreating a table setting in sqlserver

Search for: prevent changes from recreating a table setting in sqlserver


Tools
Options
Designers
Prevent saving...

how to concatenates strings in tsql

Search for: how to concatenates strings in tsql


declare @plantLike varchar(50)
set @plantLike = '%' + @plantname + '%'


use [some-db]
go

declare @d DATE
set @d = GETDATE()

exec sp_someproc
   'arg1', -- comment
   'arg2', -- comment
   '2020-02-29', -- st date
   @d -- end date.today

go

Dynamic Like Statement in SQL

Search for: Dynamic Like Statement in SQL


select * from t1, t2
where
 t1.c1 = t2.c2
 t1.column1 not like '%' + t2.column6 + '%'

How do I update with a join projection in SQL

Search for: How do I update with a join projection in SQL

Update multiple rows in a table from another table

Search for: Update multiple rows in a table from another table


update table1

set table1.col1 = t2.col3, 
   table1.col4 = t2.col1

from table2 as t2 -- this can be an inner select with joins if need be

where table1.col7 =  t2.col7

update from syntax

Search for: update from syntax

update is documented here