Procedure question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Procedure question

Author

Topic

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-16 : 05:50:42

Hi,

I am in the process of writing a procedure that takes a table name, does some updates on the table and then open a cursor using the table we provide the cursor. The idea is to update three other tables with the data in the table that we pass to the procedure.

For example, table TEST_MEMBERS has the following structure...

NI NUMBER, EMPLOYER, SCHEME, BENEFIT
ZT372765A AAA AAA AAA
YW019319A CCC CCC CCC
YB599433D AAA AAA AAA
YT198826D CCC CCC CCC

I pass the table name to the procedure which then updates the columns by replacing the codes, AAA and CCC with UIDs from other tables.
What I want to do then is update THREE more tables with the information from the table above. Still not making sense?!

Well, I have a table called SchemeMember.. the table hold a line of information for the member with NI number YT198826D, I need to change one value scheme in the SchemeMember table with the new one from the table above and so on.

Here is some of my code which you might help explain my problem.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='TEST_MEMBERS')
BEGIN
DROP TABLE TEST_MEMBERS
END
CREATE TABLE [DBO].[TEST_MEMBERS] (
[NI_NUMBER] [VARCHAR] (100) NULL,
[EMPLOYER] [VARCHAR] (100) NULL,
[SCHEME] [VARCHAR] (100) NULL,
[BENEFIT] [VARCHAR] (100) NULL
) ON [PRIMARY]

INSERT INTO TEST_MEMBERS (NI_NUMBER, EMPLOYER, SCHEME, BENEFIT)
SELECT 'ZT372765A', 'AAA', 'AAA', 'AAA'
UNION ALL
SELECT 'YW019319A', 'CCC', 'CCC', 'CCC'
UNION ALL
SELECT 'YB599433D', 'AAA', 'AAA', 'AAA'
UNION ALL
SELECT 'YT198826D', 'CCC', 'CCC', 'CCC'

and the procedure is...

CREATE PROC AMMAR @TABLE_NAME VARCHAR(20)
AS

DECLARE @EMPUID VARCHAR(20),
@SCHUID VARCHAR(20),
@BENUID VARCHAR(20),
@SQL_STATMENT VARCHAR(1000)

SET @SQL_STATMENT = 'UPDATE ' + @TABLE_NAME + ' SET EMPLOYER = EMPLOYERUID FROM EMPLOYER WHERE SHORTDESC = EMPLOYER'
EXEC (@SQL_STATMENT)

SET @SQL_STATMENT = 'UPDATE ' + @TABLE_NAME + ' SET SCHEME = SCHEMEUID FROM SCHEME WHERE SHORTDESC = SCHEME'
EXEC (@SQL_STATMENT)

SET @SQL_STATMENT = 'UPDATE ' + @TABLE_NAME + ' SET BENEFIT = BENEFITUID FROM BENEFIT WHERE SHORTDESC = BENEFIT'
EXEC (@SQL_STATMENT)

SET @SQL_STATMENT = 'SELECT * FROM ' + @TABLE_NAME

RETURN

EXEC AMMAR 'TEST_MEMBERS'

How can I put a cursor in the PROC that would go through my table TEST_MEMBERS and carry out the necessary updates on the tables?

Regards.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-16 : 06:22:12

As always I don't see as need for a cursor here - has someone given a requirement that a cursor has to be created? Maybe you could create one then drop it without using it.

Also you might want to have a serious discussion with whoever designed this system as it doesn't look very sensible.

Your SP seems to be updating the passed @TableName from EMPLOYER, SCHEME and BENEFIT. Do you want to do these updates from TEST_MEMBERS?

If you want to do row by row processing on TEST_MEMBERS you can use the NI NUMBER, or you can loop through the other fields individually if the update is for distinct values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Crespo24
Village Idiot

144 Posts

Posted - 2003-01-16 : 06:25:15

quote:

As always I don't see as need for a cursor here - has someone given a requirement that a cursor has to be created? Maybe you could create one then drop it without using it.

Also you might want to have a serious discussion with whoever designed this system as it doesn't look very sensible.

Your SP seems to be updating the passed @TableName from EMPLOYER, SCHEME and BENEFIT. Do you want to do these updates from TEST_MEMBERS?

If you want to do row by row processing on TEST_MEMBERS you can use the NI NUMBER, or you can loop through the other fields individually if the update is for distinct values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Exactly.... I am not going to bother with the cursor...
Something like,

SET @SQL_STATMENT = 'UPDATE MEMBERBENEFIT SET BENEFITUID = BENEFIT FROM '+@TABLE_NAME+' A, SCHEMEMEMBER B
WHERE A.NI_NUMBER = EMPLOYEEUID AND B.MEMBERUID = MEMBERBENEFIT.MEMBERUID'
EXEC (@SQL_STATMENT)

woudl do the updates on the tables without a cursor.

Thanks nr.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources