Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedures

Author

Topic

abidba
Starting Member

26 Posts

Posted - 2009-11-02 : 10:35:23

There are three tables: Table1 , Table2 and Table3.

I have to write a stored procedure to do the following things:
Look at the key words like Keyword1, keyword2, keyword3 , etc. from table1. If there is Keyword1, keep it and if it is not there, select from table2.

I have a stored prcedure to do this but i am not able to make this working. Please help me out on this.

CREATE PROC TabPopulate

AS
DECLARE @tabID AS INT
DECLARE @name AS VARCHAR(25)
DECLARE @descr AS VARCHAR(150)
DECLARE @indiv AS VARCHAR(50)
DECLARE @nextComma AS VARCHAR(5)
DECLARE @newKWID AS INT
DECLARE @newTabID AS INT
DECLARE @firstComma AS VARCHAR(5)

DECLARE TabCurs CURSOR FOR SELECT TabID, TabName, TabDescr

FROM Table_OLD ORDER BY TabID

OPEN TabCurs

FETCH TabCurs INTO @tabid, @tabname, @descr

WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO Table1 SELECT
Column1, column2, column3, column4, column5,....

FROM Table_old WHERE tabID = @tabID

SET @newtabID = SCOPE_IDENTITY()

SET @firstComma = 1

SET @nextComma = (SELECT CHARINDEX(@descr, ',', 1))

IF @nextComma = 0
BEGIN

INSERT INTO Table2 (keyword) VALUES (@descr)

SET @newKWId = SCOPE_IDENTITY ()

INSERT INTO Tab2 (TabsID, keywordsId) VALUES ( @newTabID, @newKWId)
END

ELSE

WHILE (countWords > 0)

BEGIN

SET @indiv = (SELECT SUBSTRING(@descr, @firstComma, @firstcomma + @nextComma)

END

END

CLOSE TabCurs

DEALLOCATE TabCurs

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-02 : 11:16:02

You've got a lot of syntax and logic problems in this code. I suggest you post the DDL (create table code) for you tables and provide DML (insert statements to populate your starting table with sample data). Finally post your desired results if the code runs successfully.

Be One with the Optimizer
TG

abidba
Starting Member

26 Posts

Posted - 2009-11-03 : 09:04:52

I am new in the SQL Programming and am trying to learn. So any guidance at the initial steps would be appreciated. Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-03 : 10:45:50

Happy to help but I think the best guidance I can offer is to post what I asked for so you can see a better way to achieve your objective. You've got to get out of the mode of procedural, row-by-row processing and start thinking in terms of set-based statements.

Be One with the Optimizer
TG

abidba
Starting Member

26 Posts

Posted - 2009-11-05 : 16:57:09

I got it. Was able to figure it out. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources