Stored Procedure INSERT Help??

[jkl]

I'm new to Stored Procedures.
This is kind of what I want to do:

=======================================

DECLARE @iCount int
iCount = 0

SELECT DISTINCT Title FROM newMovies WHERE isNew = 1
?? assign the value of each Title to a variable (i.e. newTitle)

IF NOT EXISTS (SELECT tableID from MyCollection WHERE Title = newTitle)
?? then for each record returned, check to see if it exists in my collection
BEGIN
?? if the title doesn't exist, add it to the collection
INSERT INTO MyCollection (Title) VALUES (newTitle)
iCount = iCount + 1
END

UPDATE newMovies SET isNew = 0 WHERE isNew = 1

RETURN(iCount)

=======================================

Thanks for any help you can give this newbie.

.jkl.

 

[Tim1]

Hiya,

What you want to do, I think, is :

DECLARE @iCount int,
@title VARCHAR(50),
@lv_rowcount = 1
SELECT iCount = 0

SET ROWCOUNT 1
WHILE @lv_rowcount != 0
BEGIN

SELECT @title = Title FROM newMovies WHERE isNew = 1
SELECT @lv_rowcount = @@rowcount

IF @lv_rowcount != 0
BEGIN

IF NOT EXISTS (SELECT tableID from MyCollection WHERE Title = @title)

BEGIN
INSERT INTO MyCollection (Title) VALUES (@title)
SELECT iCount = iCount + 1
END

UPDATE newMovies SET isNew = 0 WHERE isNew = 1
END
END

RETURN(iCount)

HTH

Tim

 

[rajeevnandanmishra]

hi,
your problem can also be solved by doing this.
--------------------------
DECLARE @iCount int

INSERT MyCollection
SELECT DISTINCT title FROM newMovies WHERE isNew = 1
AND title NOT IN (SELECT title FROM MyCollection)

SELECT @iCount = @@rowcount

UPDATE newMovies
SET isNew = 0 WHERE isNew = 1

RETURN(@iCount)
----------------------------

I hope this will help you.