SQL SERVER : การตั้งค่า Result Query , Grid Query , Result Sql

Post by Goborijung at 2023-07-18 09:38:16 | ID: 1950

การตั้งค่า Result Query , Grid Query , Result Sql
1. Tools > Option
2. Environment > Fonts and Colors
3. Show settings for: เลือก Grid Results > เลือก Size ตามขนาดที่เราต้องการ

IF - ELSE

Post by Goborijung at 2023-03-22 09:21:20 | ID: 1868

IF exists (Select CON_BRNCODE From vPalletInProcessPack_floor Where CONFLAG = 'Y')
BEGIN
	Select CON_BRNCODE From vPalletInProcessPack_floor Where CONFLAG = 'Y'
END 
ELSE
BEGIN
	Select CON_BRNCODE From vPalletInProcessPack_floor_REC Where CONFLAG = 'Y'
END

SQL SERVER : ADD Date , ADD Year , Year + 1 , Year - 1

Post by Goborijung at 2022-08-22 17:08:16 | ID: 1744

Declare @sDate datetime = '08/20/2022'
SELECT DATEADD(year, 2, @sDate) AS DateAdd
SELECT DATEADD(year, 1, @sDate) AS DateAdd
SELECT DATEADD(year, -0, @sDate) AS DateAdd
SELECT DATEADD(year, -1, @sDate) AS DateAdd
SELECT DATEADD(year, -2, @sDate) AS DateAdd

SQL SERVER : Create Temp Table

Post by Goborijung at 2021-04-03 15:43:11 | ID: 1110

-- Create Temp Table
IF OBJECT_ID('tempdb..#vNAVCode') IS NULL
BEGIN
CREATE TABLE #vNAVCode
(
	ItemCode nvarchar(30),
	CuttingNo nvarchar(30),
	CreatedDate datetime
)
END

-- Drop Temp Table
IF OBJECT_ID('tempdb..#vNAVCode') IS NOT NULL
BEGIN
     DROP TABLE #vNAVCode;
END

SQL SERVER : Drop Temp Table

Post by Goborijung at 2021-04-03 15:44:02 | ID: 1111

-- Drop Temp Table
IF OBJECT_ID('tempdb..#vNAVCode') IS NOT NULL
BEGIN
     DROP TABLE #vNAVCode;
END

-- Create Temp Table
IF OBJECT_ID('tempdb..#vNAVCode') IS NULL
BEGIN
CREATE TABLE #vNAVCode
(
	ItemCode nvarchar(30),
	CuttingNo nvarchar(30),
	CreatedDate datetime
)
END

SQL SERVER : Charindex and Substring and Len

Post by Goborijung at 2020-08-01 16:15:56 | ID: 693

>> Examples

SELECT CHARINDEX('s', 'Customer') AS MatchPosition;
// OUTPUT : 3

SELECT SUBSTRING('Customer',1,2) AS String
// OUTPUT : Cu

SELECT LEN('Customer') AS LenCustomer
// OUTPUT : 8

SQL SERVER : Create And Delete Users in Database

Post by Goborijung at 2020-10-16 16:00:25 | ID: 844

>> How To: Create a SQL Server Authenticated user in Microsoft SQL Server Management Studio 
// How to Create an User Login in Sql Server

1. Connect Server by : sa user

2. +Security > Logins > Right-Click : New Login > 
General 		: Login name , Password
Server Roles 	: check sysadmin
User Mapping	: use DatabaseName , check db_owner > OK

3. +Databases > DatabaseName > Security > Users
dblClick AdminUser > Securables > Search > All object belonging to the schema > dbo > OK
check Permission List > OK

4. Goto +Security > Logins > AdminUser > dblClick > change Password OK
5. try Login by new Users


==================================================

>> How To: Drop Users in Microsoft SQL Server Management Studio 
USE [DatabaseName]
GO

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserName');
GO

// ทำการแก้ไข SCHEMA ทั้งหมดที่ค้นเจอให้เป็น dbo ซะ

ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_accessadmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_securityadmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_ddladmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_backupoperator TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;
GO

DROP USER [UserName]
GO

SQL SERVER : Show Columns Name From Table , get columns :: Recommend

Post by Goborijung at 2021-02-08 11:46:12 | ID: 521

// Recommend
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'WEB_Claim'

// Get Data Type (Recommend)
SELECT ROW_NUMBER() OVER(ORDER BY COLUMN_NAME) AS [No.] , COLUMN_NAME , IS_NULLABLE , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'ItmProblem'

SELECT COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,CHARACTER_SET_NAME,COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'InterfaceNAVJournal'

EXEC sp_help InterfaceNAVJournal

SELECT name FROM sys.dm_exec_describe_first_result_set('SELECT* FROM Branch', NULL, 0)

SQL SERVER : Backup Database (Backup,Export) and Import Database (Restore)

Post by Goborijung at 2022-04-06 06:03:07 | ID: 1446

- SQL Server :: Export Database (Backup)
1. เลือก Database Name
2. Right Click > Tasks > Backup...
3. กดปุ่ม Add > เลือก Location Save
4. ตั้งชื่อ FileName.BAK (.BAK ตัวใหญ่จ้า)
5. กด OK เป็นอันเสร็จเรียบร้อยจ้า

-- SQL Server :: Import Database (Restore)
1. เลือกที่ Folder Databases
2. Right Click > Restore Database...
3. Source : ให้เลือกเป็น Device > Add
4. ทำการเลือกไฟล์ .BAK ที่เรา Backup เอาไว้
5. กด OK เป็นอันเสร็จเรียบร้อยจ้า

SQL SERVER : CASE WHEN THEN ELSE

Post by Goborijung at 2020-08-17 16:20:00 | ID: 686

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

>> เปรียบเทียบ [ CASE X WHEN VS CASE WHEN X ]
>> แทนค่า X = 10

SELECT CASE X 
WHEN < 0 THEN 'A' 
WHEN > 100 THEN 'B' 
ELSE 'C' 
END

//คำอธิบาย
เคส 10 
เมื่อ < 0 แล้ว A
เมือ > 100 แล้ว B
นอกนั้น C
จบนะ

----------------------

SELECT CASE
WHEN X < 0 THEN 'A'
WHEN X > 100 THEN 'B'
ELSE 'C' 
END

//คำอธิบาย
เคส 
เมื่อ 10 < 0 แล้ว A
เมื่อ 10 > 100 แล้ว B
นอกนั้น C
จบนะ

>> Example
SELECT JobTitle,
        CASE Gender
           WHEN 'M' THEN 'Male'
           WHEN 'F' THEN 'Female'
           ELSE 'Unknown Value'
        END
 FROM   HumanResources.Employee