IN SQL

$3.$ With a script, GenericTree.sql$,$ in below, please implement a stored procedure, usp$\_$BFS$,$ which does

Breadth$-$First$-$Search. The tree structure & data in the file should be used.

The GenericTree.sql script is below:

CREATE TABLE GenericTree $($

NodeID INT NOT NULL,

ParentNodeID INT NULL,

CONSTRAINT PK$\_$GenericTree PRIMARY KEY $($NodeID$),$

CONSTRAINT FK$\_$GenericTree$\_$Parent FOREIGN KEY $($ParentNodeID$)$ REFERENCES

GenericTree $($NodeID$)$

$)$;

GO

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(1,$ NULL$)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(2,1)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(3,1)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(4,1)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(5,2)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(6,2)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(7,3)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(8,3)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(9,3)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(10,6)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(11,6)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(12,6)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(13,6)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(14,7)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(15,8)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(16,8)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(17,11)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(18,11)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(19,16)$;

INSERT INTO GenericTree $($NodeID$,$ ParentNodeID$)$ VALUES $(20,16)$;

GO

CREATE PROCEDURE usp$\_$DFS$\_$PreOrder

@NodeID INT $=$ NULL

AS

BEGIN

SET NOCOUNT ON;

$/*$

EXECUTE usp$\_$DFS$\_$PreOrder;

$*/$

DECLARE @v$\_$NodeID INT;

DECLARE c$\_$CUR CURSOR FAST$\_$FORWARD LOCAL FOR

SELECT NodeID

FROM GenericTree

WHERE ISNULL$($ParentNodeID$,0)=$ ISNULL$($@NodeID, $0)$

ORDER BY NodeID;

OPEN c$\_$CUR;

FETCH FROM c$\_$CUR INTO @v$\_$NodeID;

WHILE @@FETCH$\_$STATUS $=0$

BEGIN

PRINT @v$\_$NodeID;

EXECUTE usp$\_$DFS$\_$PreOrder @v$\_$NodeID;

FETCH FROM c$\_$CUR INTO @v$\_$NodeID;

END;

CLOSE c$\_$CUR;

DEALLOCATE c$\_$CUR;

END;

GO