SQL2
select * from cgn_user for xml auto
<cgn_user i_UserID="1" s_UserName="Ashok" c_Status="Y" i_LocationID="4"/>
<cgn_user i_UserID="3" s_UserName="Sunil" c_Status="N" i_LocationID="2"/>
<cgn_user i_UserID="4" s_UserName="A.Ashok" c_Status="Y" i_LocationID="1"/>
<cgn_user i_UserID="5" s_UserName="Kiran" c_Status="N" i_LocationID="2"/>
<cgn_user i_UserID="6" s_UserName="Ram" c_Status="Y" i_LocationID="4"/>
select * from cgn_user for xml raw
<row i_UserID="1" s_UserName="Ashok" c_Status="Y" i_LocationID="4"/>
<row i_UserID="3" s_UserName="Sunil" c_Status="N" i_LocationID="2"/>
<row i_UserID="4" s_UserName="A.Ashok" c_Status="Y" i_LocationID="1"/>
<row i_UserID="5" s_UserName="Kiran" c_Status="N" i_LocationID="2"/>
row i_UserID="6" s_UserName="Ram" c_Status="Y" i_LocationID="4"/>
------------------------------------------------
--Temporary tables..
select * into #tmp from cgn_user
select * from #tmp
select * into ##tmp from cgn_user
select * from ##tmp
----------------------------------------
select * into temp from cgn_user --schema and data are copied..
select * from temp
-----------------------------------------
delete from temp
insert into temp select * from cgn_user where i_locationID=4 --copying the data....
////////////////////////////////////////////////////////////////////////////////////
alter procedure sp_UserDetails_string(@i_UserId int,@i_LocationId int)
as
declare @sql varchar(max)
declare @sUserName varchar(max) --Variable Declaration
set @sql='select i_UserID,s_UserName from Cgn_User'+
' where i_UserID='+Convert(varchar,@i_UserId)+' and i_LocationId='+Convert(varchar,@i_LocationId)
print @sql
exec (@sql)
exec sp_UserDetails_string 1,4
//////////////////////////////////////////////////////////////////////////////////////
//Cursor
declare @LocaitonId int
declare @cur cursor
set @cur = for select i_LocationId from Cgn_mstLocation
Open @cur
fetch from @cur into @LocaitonId
while @@fetch_status=0
begin
Print @LocaitonId
fetch from @cur into @LocaitonId
end
close @cur
Deallocate @cur
----------------------------------------------
declare @LocaitonId int
declare @strLocationName varchar(max)
declare @sql varchar(max)
declare @cur cursor
set @cur = for select i_LocationId,s_Location from Cgn_mstLocation
Open @cur
@sql='select'
fetch from @cur into @LocaitonId,@strLocationName
while @@fetch_status=0
begin
@sql=@sql+ (selecty count(*) from cgn_user where i_locationId=@LocaitonId) as @strLocationName
fetch from @cur into @LocaitonId
end
close @cur
Deallocate @cur
//Linked server
create procedure sp_UserDetails_tbl(@i_LocationId int)
as
declare @tbl table (i_userId int ,s_UserName varchar(50))
declare @sUserName varchar(max) --Variable Declaration
insert into @tbl select i_userId,s_userName from cgn_user where i_LocationId=@i_LocationId
select * from @tbl
sp_UserDetails_tbl 4
--------------------------------------
select i_locationID,count(*)as 'User Count' from cgn_user group by i_locationID having count(*)>1
select * from cgn_user
select * from cgn_mstLocation
select * from cgn_user,cgn_mstLocation
select cgn_user.* from cgn_user,cgn_mstLocation where cgn_user.i_locationID=cgn_mstLocation.i_locationID
and cgn_mstLocation.i_locationID =4
select cgn_user.* from cgn_user
left join cgn_mstLocation loc on cgn_user.i_locationID=loc.i_locationID
left join cgn_mstLocation on cgn_user.i_locationID=cgn_mstLocation.i_locationID
where cgn_mstLocation.i_locationID =4
--Linked server query...
select * from
select * from [lab4].[trail].[dbo].[cgn_user]
select cgn_user.* from
[lab3].[trail].[dbo].[cgn_user] user1
,[lab4].[trail].[dbo].[cgn_mstLocation] loc
where cgn_user.i_locationID=cgn_mstLocation.i_locationID
and cgn_mstLocation.i_locationID =4
select * from [trail].[dbo].[cgn_user]
select * from [trailtest].[dbo].[cgn_user]
select usr.*,loc.s_location from
[trail].[dbo].[cgn_user] usr,[trailtest].[dbo].[cgn_mstLocation] loc
where loc.i_locationId=5