-----------------REFERENCE SITES for SQL-------------------
Monday, 19 August 2013
DYNAMIC SQL
-------------DYNAMIC SQL----------------
alter proc emp_ins (@name varchar(100) , @empno int out )
as
begin
declare @query nvarchar(500)=( 'select @i_empno=empno from emp where ename =@in_name ')
exec sp_executesql @query,N'@in_name varchar(100),@i_empno int out' ,@in_name=@name,@i_empno=@ empno out
end
declare @c int
exec emp_ins 'smith',@c out
print @c
TABLE VALUED PARAMETERS (CREATE TYPE)
---------------table valued parameters-----------------
Create table xx(id int identity primary key, name varchar(10),gender bit)
CREATE TYPE [dbo].xx_type AS TABLE(
id int , name varchar(10),gender bit,isModified bit
)GO insert into xx select 'c',1
declare @my_type xx_type
insert into @my_type
select *,0 as isModified from xx
update @my_type set name='cc',ismodified=1 where id=3
insert into @my_type select 0,'d',1,0 -- is modified should be zero for newly inserted records
insert into @my_type select 0,'e',1,0 -- is modified should be zero for newly inserted records
exec USP_XX_Modify @my_type
Create PRoc USP_XX_Modify(@my_type xx_type readonly) AS
BEGIN -- updating modified recordsupdate At set name=t.name , gender=t.gender
from @my_type t inner join xx AT on t.id=at.id
where isModified=1
-- inserting newly passed recordsInsert into xx
select name,gender from @my_type where id=0
END select * from xx
CREATE TYPE [dbo].xx_type AS TABLE(
id int , name varchar(10),gender bit,isModified bit
)GO insert into xx select 'c',1
declare @my_type xx_type
insert into @my_type
select *,0 as isModified from xx
update @my_type set name='cc',ismodified=1 where id=3
insert into @my_type select 0,'d',1,0 -- is modified should be zero for newly inserted records
insert into @my_type select 0,'e',1,0 -- is modified should be zero for newly inserted records
exec USP_XX_Modify @my_type
Create PRoc USP_XX_Modify(@my_type xx_type readonly) AS
BEGIN -- updating modified recordsupdate At set name=t.name , gender=t.gender
from @my_type t inner join xx AT on t.id=at.id
where isModified=1
-- inserting newly passed recordsInsert into xx
select name,gender from @my_type where id=0
END select * from xx
Subscribe to:
Posts (Atom)
EMP, DEPT Sample script
/****** Object: Table [dbo].[DEPT] Script Date: 19-05-2016 06:58:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET A...
-
TRUNCATE Data From All TABLES of a DataBase in SQL Server Here the following script help full to you to truncate data from all tables in...
-
Convert Delimited XML value in a column into Column (More than 8000 characters) To handle more than 8000 characters, convert that data in...
-
Different types of temp tables Step 1: We have different types of temporary data storage objects, probably we call as temp tables. ...