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