samples commands in sql server

samples commands in sql server

insert into st_details values(101,'ph',20)

create procedure spinsert

as

begin 

insert into st_details values(102,'ph',20)

end

create procedure spinsert3

@ename nvarchar(50),@age int

as

begin

insert into st_details values(@ename,@age)

end

execute spinsert3 'ph7',22

create procedure spdelete

@empid int

as

begin 

delete st_details where emp=@empid

end

execute spdelete 5

create procedure spselect2

@empid int

as

begin 

select * from st_details where emp=@empid

end

execute spselect2 103

truncate table st_details

Explicit value must be specified for identity column in table 

'st_details' either when IDENTITY_INSERT is set to ON or when a 

replication user is inserting into a NOT FOR REPLICATION identity column.

set Identity_Insert st_details on

insert into st_details(ename,age) values('ph',23)

set Identity_Insert st_details off

create procedure spdeletest

as

begin

delete st_details

end

dbcc checkident (st_details,reseed,0)

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('st_details')

create trigger trinsert on st_details for Insert

as

begin

insert into tbl2 values(1,'ph',22)

end

drop trigger trinsert

create trigger trinsert on tbl2 for Insert

as

begin

select * from tbl2

end

insert into tbl2 values(10,'ph',25)

create procedure spselectident3

@ename nvarchar(50),@age int,@empid int out

as

begin

insert into st_details values(@ename,@age)

select @empid=SCOPE_IDENTITY()

end

declare @employeeid2 int

execute spselectident3 'ph4',26,@employeeid2 out

print 'Employee ID = '+cast(@employeeid2 as nvarchar(2))