SQL
Transaction Rollback
-- create sample table
CREATE TABLE result
(
s_id int NULL,
s_name varchar(50) NULL,
m1 int NULL,
m2 int NULL,
m3 int NULL
)
-- whenever we update the data using s_id,if the count of record is more than one,the transaction will be rollback
create proc test
(
@s_id int,
@s_name varchar(20),
@m1 int,
@m2 int,
@m3 int
)
as
begin transaction
begin
update result set s_name=@s_name,m1=@m1,m2=@m2,m3=@m3 where s_id=@s_id
if @@rowcount >= 2
begin
rollback transaction
raiserror('Puttukichu',16,1)
return
end
end
commit transaction
Grouping with select case
--calculate the total like wise Horizantal and Vertical and also status(Pass or fail)
select
case grouping(s_name) when 0
then s_name else 'Total' end as s_name,
sum(m1)as m1,sum(m2)as m2,sum(m3) as m3,
sum(m1)+sum(m2)+sum(m3) as Total,
case when sum(m1)<50 or sum(m2)<40 or sum(m3)<30
then 'fail'else
'pass' end as status
from result
group by s_name with rollup
-- create table for delimiter
CREATE TABLE empmast
(
eid varchar(50) NULL,
e_name varchar(50) NULL
)
CREATE TABLE emp
(
eid varchar(50) NULL,
desig varchar(50) NULL
)
CREATE PROCEDURE sp_Txt_Split
(
@depname varchar(50)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@Item Varchar(100),
@sInputList varchar(50)
set @sInputList=(select eid from emp where desig=@depname)--(11,22,33)
CREATE TABLE #List(Item varchar(100))
WHILE CHARINDEX(',',@sInputList,0) <> 0 --11,22,33 (charindex=3 != 0 )
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(',',@sInputList,0)-1))),--get no of char before ','.therefore 11
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(',',@sInputList,0)+1,LEN(@sInputList))))--get remaining char.therefore 22,33
IF LEN(@Item) > 0
INSERT INTO #List SELECT e_name from empmast where eid=@Item --insert the value
END-- go to while @sInputList=22,33
IF LEN(@sInputList) > 0
INSERT INTO #List SELECT e_name from empmast where eid=@sInputList --insert last value
SELECT * FROM #List
DROP TABLE #List
RETURN
END
EXEC sp_Txt_Split 'programmer'
--cryptograpy
--How to Encrypt and decrypt the specific field
create table user1
(
uname varchar(50),
pword binary(200)
)
-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'SQLAuthority'
-- Create Encryption Certificate
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
-- Create Symmetric Key
CREATE SYMMETRIC KEY userKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE EncryptTestCert
Trigger
create trigger
triginsert
on empdet
for insert
as
print('Inserted one Record')
create trigger
trigdelete
on empdet
for delete
as
print('deleted one Record')
drop trigger
trig
insert into
empdet values(7,'prakash',24)
delete empdet where empid=6
--drop proc p2
create proc
p2
( @p_id varchar(20), @qty int
)
as
declare
@n int
begin transaction
select @n=count(*)
from raw_materials
where product_id=@p_id
update mm set mm.available_unit=mm.available_unit-rm.unit*@qty
from material_master mm,raw_materials rm
where rm.material_name=mm.material_name
and rm.vendor_name=mm.vendor_name and rm.product_id=@p_id
if @@rowcount !=
@n
begin
rollback
transaction
raiserror('material not in material master',10,1)
return
end
if (select
count(*)
from raw_materials
where product_id=@p_id
and
material_name in(select material_name
from material_master
where available_unit
< 0))
> 0
begin
rollback
transaction
select
mm.material_name,r.unit*@qty
as Required_Unit,mm.Available_Unit from material_master mm, raw_materials r
where
r.material_name=mm.material_name
and
r.vendor_name=mm.vendor_name
and
r.product_id=@p_id
and
mm.available_unit