DECLARE @DSID NVarchar(50),@XMLPath Nvarchar(100),@RoldId int,@Debug varchar(max)
set @Debug=‘<Roles><Role code=”111″ name=”RoleName”><Persons><Person FName=”FName” LName=”LName” DSID=”564″ Email=”P@email.com” /><Person FName=”1FName” LName=”1LName” DSID=”1234″ Email=”P1@email.com” /></Persons></Role></Roles>’
set @RoldId = 111
if ( (convert(XML,@Debug)).exist(‘(/Roles/Role[@code= sql:variable("@RoldId")])’) =1 )
Begin
SELECT @DSID=(convert(XML,@Debug)).value(‘(/Roles/Role[@code= sql:variable("@RoldId")]/Persons/Person/@DSID)[1]‘,‘nvarchar(50)’)
print @DSID
select * from [Partner] where [ID]=@DSID
End
How to work with XML In SQL Server 2005
How to debug store procedure through VS2005
Please select view and select server explorer, and connect database then select the procedure.
Then click the procedure and open it, then select the line and put the debug when you want to debugging. After that you select the procedure and right click and select option of ’step into store procedure’.
Deleting duplicate rows when there is no primary key
set nocount on
create table #tbFruit (Fruit varchar(20))
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘orange’)
insert into #tbFruit (Fruit) values (‘orange’)
insert into #tbFruit (Fruit) values (‘orange’)
create table #tbFruitsWithDupes (DupeID int identity, Fruit varchar(20), DupeCount int)
insert into #tbFruitsWithDupes (Fruit, DupeCount)
select [...]
Top 10 new features in SQL Server 2005
Top 10 features
Other Reference http://www.developer.com/db/article.php/3512126
Current identity value is reset after delete the record
Create procedure DeleteTest(@ID int)
as
begin
declare @cnt int
– delete current record
delete from newTest where id=@ID
– reset identity value to the total records in the table
select @cnt = isnull(max(ID),0) from newTest
DBCC CHECKIDENT ( ‘dbo.newTest’, RESEED, @cnt)
————-
For your reference http://msdn2.microsoft.com/en-us/library/ms176057.aspx
