Database

CREATE FUNCTION [dbo].[f_GenerateRows]
	(@NumRows INT)
RETURNS 
	@Tbl TABLE (pkID INT PRIMARY KEY NOT NULL)
AS
BEGIN
	DECLARE @i INT;
	
	-- SELECT * FROM dbo.f_GenerateRows((SELECT 10))
	
	SET @I = 1
	WHILE @I <= @NumRows BEGIN
		INSERT INTO @Tbl VALUES(@I)
		
		SET @I = @I + 1
	END
	
	RETURN
END

ALTER PROC sp_Create_Staff_Cycle_Clash
AS

--CHECK IF THE TABLE EXISTS
IF NOT exists(select * from sys.databases where name = 'REC_TERM_CYCLE')
BEGIN
   EXEC CREATE_TABLE_REC_TERM_CYCLE 
END
ELSE
  BEGIN 
   TRUNCATE TABLE DBO.REC_TERM_CYCLE
   DECLARE @ROWS int
   SET @ROWS = (SELECT COUNT(*) FROM tbl_term)
   INSERT INTO REC_TERM_CYCLE (term, cycle)
   SELECT t.ID, i.pkID
   FROM TBL_TERM AS t
   CROSS JOIN dbo.f_GenerateRows(@ROWS) AS i
   WHERE SUBSTRING(t.CYCLETEMPLATES, i.pkID, 1) = '1'
   ORDER BY t.ID, i.pkID
END

Msg 2714, Level 16, State 6, Procedure CREATE_TABLE_REC_TERM_CYCLE, Line 4
There is already an object named 'REC_TERM_CYCLE' in the database.

DECLARE
N NUMBER(10);
BEGIN
    SELECT MAX(ID)+1 INTO N FROM TEST;
-- CREATING SEQUENCE
Execute immediate ('DROP SEQUENCE SEQ_TEST');
Execute immediate ('CREATE SEQUENCE SEQ_TEST
    MINVALUE 1
    START WITH '||N||'
    INCREMENT BY 1
    CACHE 20
    ORDER');
END;
/

CREATE OR REPLACE TRIGGER TRG_TEST_AUTONUMBER 
BEFORE INSERT ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
  SELECT SEQ_TEST.NEXTVAL
  INTO   :NEW.ID
  FROM   DUAL;
END;
/

INSERT INTO TEST(NAME,YEAR)
SELECT NAME,2009 FROM TEST WHERE YEAR = 2008
/

DECLARE
  ID NUMBER;
BEGIN
  SELECT MAX(ID) INTO ID FROM TEST;
  
  EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_TEST
  MINVALUE 1
  START WITH ' || ID || '
  INCREMENT BY 1
  CACHE 20
  ORDER';
END;
/