SQL Common Scripts - Code Scripts

Some SQL Scripts Resource

---

Q : How to Reset Identity Column ?

A : Simply Type in Query Analyzer :
DBCC CHECKIDENT (table_name, RESEED, startingindex)

---

Q : What is Do While Syntax in SQL?

A : declare @i int

set @i = 1while @ i<501

begin

/* Type Some Code Here */

@i = @i + 1

end

---

Q : How do I work with SQL Cursors ?

A : DECLARE @cId tinyint,@wStr NVARCHAR(20) /*Cursor Variables */

DECLARE curor_name CURSOR FOR SELECT * FROM table_name

OPEN curor_name

FETCH NEXT FROM curor_name INTO @cId,@wStr

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cId,@@wStr FROM WHERE Column_Name = @cId

FETCH NEXT FROM curor_name INTO @cId,@wStr

END

CLOSE curor_name

DEALLOCATE curor_name

---

Q : How to Send XML String to Stored Procedures ?

A :

XML String - root node , WCat - First Level , WCatId,WCatSO - Second Level

This XML STring is sent to Stored procedure, Which Traverses it and Displays it

Declare @xmlString VARCHAR(2000) /* Input Parameter to Store Procedure */

DECLARE @xmlHandle INT /*XML Handle */

/* Built in Stored Proc Execution */

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlString

SELECT * FROM OPENXML (@xmlHandle, '/root/WCat', 2) /*Root Node */

WITH(

wCatId tinyint './WCatId', /* Node in XML String */

wCatSO tinyint './WCatSO' /* Node in XML String */

)

EXEC sp_xml_removedocument @xmlHandle