Wednesday 1 January 2014

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))


0 comments:

Post a Comment