satya - 3/18/2020, 12:33:42 PM
From SOF an image
sql quick journal
satya - Wed Nov 21 2012 15:34:11 GMT-0500 (Eastern Standard Time)
sql server distinct syntax
sql server distinct syntax
satya - Wed Nov 21 2012 15:37:47 GMT-0500 (Eastern Standard Time)
Example
select distinct folder_name, folder_id, f_tag_owner_userid from v_taggedFolders
where f_tagname in ('android')
order by folder_name
satya - 12/30/2019, 12:43:35 PM
SQL Server t-sql data types
SQL Server t-sql data types
satya - 12/30/2019, 12:44:21 PM
These data types are documented here
satya - 12/30/2019, 12:46:25 PM
quick surprises
long is not a type
double is not a type
satya - 12/30/2019, 12:46:56 PM
float and real are documented here
satya - 1/3/2020, 11:16:31 AM
On Identity
@@IDENTITY gives you the last identity value generated by the most recent INSERT statement for the current connection, regardless of table or scope.
satya - 1/3/2020, 11:17:02 AM
SOF: Best way to get last identity inserted in a table
satya - 2/7/2020, 2:10:57 PM
System functions where @@rowcount is one of them are documented here
System functions where @@rowcount is one of them are documented here
satya - 2/7/2020, 2:20:42 PM
On throw
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.
satya - 2/7/2020, 2:21:01 PM
Example
THROW 51000, 'The record does not exist.', 1;
satya - 2/7/2020, 2:26:50 PM
If statement surprises
if (@@ROWCOUNT != 1)
begin
declare @errmessage varchar(100);
set @errmessage = 'Invalid number of rows: ' + @@ROWCOUNT;
THROW 51000, @errmessage, 1;
end;
satya - 2/7/2020, 4:52:31 PM
Declare variables are essential and described here
satya - 2/7/2020, 5:56:31 PM
setting multiple variables using select in tsql
setting multiple variables using select in tsql
satya - 2/8/2020, 2:31:51 PM
if else
IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
begin
SELECT 'Weekend';
end;
ELSE
begin
SELECT 'Weekday';
end;
satya - 2/8/2020, 2:41:10 PM
Execute a proc with params
exec sp_someproc a1,a2,a3
satya - 3/18/2020, 12:32:10 PM
prevent changes from recreating a table setting in sqlserver
prevent changes from recreating a table setting in sqlserver
Search for: prevent changes from recreating a table setting in sqlserver
satya - 3/18/2020, 12:33:42 PM
From SOF an image
satya - 3/18/2020, 12:34:03 PM
Path
Tools
Options
Designers
Prevent saving...
satya - 3/22/2020, 6:33:35 PM
how to concatenates strings in tsql
how to concatenates strings in tsql
satya - 3/22/2020, 6:37:45 PM
Perhaps this will work
declare @plantLike varchar(50)
set @plantLike = '%' + @plantname + '%'
satya - 3/22/2020, 6:55:36 PM
This seem to work
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
satya - 3/23/2020, 11:29:02 AM
Dynamic Like Statement in SQL
Dynamic Like Statement in SQL
satya - 3/23/2020, 11:30:50 AM
You can do this
select * from t1, t2
where
t1.c1 = t2.c2
t1.column1 not like '%' + t2.column6 + '%'
satya - 3/23/2020, 11:07:00 PM
How do I update with a join projection in SQL
How do I update with a join projection in SQL
satya - 3/24/2020, 2:21:33 PM
Update multiple rows in a table from another table
Update multiple rows in a table from another table
Search for: Update multiple rows in a table from another table
satya - 3/24/2020, 2:37:17 PM
General approach seems to be
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