serving the solutions day and night

Pages

Saturday, December 3, 2011

TSQL Row Constructors (also knows as TABLE Values Constructors), Compound assignment operators

TSQL Row Constructors (also knows as TABLE Values Constructors)
DECLARE @Employees TABLE(
EmpID int,
Name varchar(200),
Email varchar(250),
Wage dec(10,2)
)

INSERT INTO @Employees(EmpID, Name, Email) VALUES(1, 'Noor', 'noor@email.com')
INSERT INTO @Employees(EmpID, Name, Email) VALUES(2, 'Kader', 'kader@email.com')

-- Using Row Constructors
INSERT INTO @Employees(EmpID, Name, Email, wage)
VALUES(1, 'Noor', 'noor@email.com', 100), (2, 'Kader', 'kader@email.com',200)

--pseudo table
SELECT *
FROM (VALUES(1, 'Noor', 'noor@email.com', 100), (2, 'Kader', 'kader@email.com', 200))
Employees(EmpID, Name, Email, wage)

--sub query expression
INSERT INTO @Employees(EmpID, Name, Email, wage)
VALUES((SELECT 1+MAX(EmpID) FROM @Employees), 'Hassan', 'hassan@email.com',75)

--Statment variable declaration/assignment
DECLARE @id int = 5, @bookname varchar(100) =N'SQL SERVER';
DECLARE @minwage dec(20,2) = (SELECT min(wage) FROM @Employees);

select @minwage

Compound assignment operators - +=, -=, *=, /=, %=, &=, !=, ^=
set @minwage=@minwage*10
set @minwage*=10
select @minwage

UPDATE @Employees SET wage*=10

SELECT * FROM @Employees

No comments: