SQL procedure issue - 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 2005 Forums

Transact-SQL (2005)

SQL procedure issue

Author

Topic

uchennavt
Starting Member

4 Posts

Posted - 2009-11-17 : 13:23:52

Hi All,

I have two tables. Table 1 is called DATA, which has several columns, most important of them are DATA_ID and METRIC_ID. Table 2 is called PARAMETERS, which also has several columns, most important of them are DATA_ID, PARAMETER_ID, and Client_Platform. I want to traverse through every record in the DATA table, and compare it with its respective record in the PARAMETERS table, matching their DATA_IDs. Once I find a match, I would like to update the PARAMETER table's Client_Platform column. This task is ran until the end of the DATA table. Below is you will find my algorithm that should accomplish the above description, however, it fails to do that. Please let me know where I have made a mistake.

ALTER

PROCEDURE [dbo].[UPDATE_PLATFORM]

-- Add the parameters for the stored procedure here

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @data_id INT

DECLARE @parameter_id INT

DECLARE @new_client_name varchar(255)

DECLARE @metric_id INT

DECLARE datacursor CURSOR FOR SELECT DATA_ID,METRIC_ID FROM DATA

DECLARE parametercursor CURSOR FOR SELECT DATA_ID FROM PARAMETERs

OPEN datacursor

OPEN parametercursor

SET @new_client_name = '.a.'

DECLARE @flag INT

SET @flag = 0

FETCH NEXT FROM datacursor into @data_id, @metric_id

while (@@FETCH_STATUS = 0)

begin

if(@metric_id >= 1 and @metric_id<=72) /*iometer*/

begin

FETCH NEXT FROM parametercursor into @parameter_id

while (@flag = 0)

begin

if (@parameter_id = @data_id) /*found the parameter */

begin

/*UPDATE PARAMETERs SET Client_Platform = @new_client_name*/

SET @flag = 1 /*update done*/

end

else

begin

SET @flag =0

FETCH NEXT FROM parametercursor into @parameter_id

end

end

end

SET @flag = 0 /*reset update*/

FETCH NEXT FROM datacursor into @data_id, @metric_id

end

-- Insert statements for procedure here

END

Thanks,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-17 : 14:51:55

I got kinda lost on the cursor stuff. Here is a sample that may or may not work:

UPDATE
	P
SET
	Client_Platform = ??
FROM
	Data AS D
INNER JOIN
	Parameters AS P
	ON
	D.Data_ID = P.Data_ID
WHERE
	METRIC_ID >= 1
	AND METRIC_ID <= 72

I'd suggest you post some sample data and expected output and we can help you better. Here is a link that describes how to do that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 14:56:25

Whoa...nested cursors

Listen

Just tell us what you are trying to accomplish

NO CODE

Just business terms

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

uchennavt
Starting Member

4 Posts

Posted - 2009-11-17 : 21:41:44

Hi Guys,

Thanks for avail. to help me. I guess I will clearify what I said a little earlier.

Table 1 has a column (DATA_ID) name and value that is exactly the same as a column (DATA_ID) in Table 2. I want to traverse through each record in table 1 and match its column value with the one on second table, and then do an update on another column on table 2. I guess you can think of this as hashing (onto table 2) onto table 2 then update on a member of the record.
Please let me know how I can write my code .. Really stuck on this

Thanks,

uchennavt
Starting Member

4 Posts

Posted - 2009-11-17 : 21:54:07

The following will give you an example of what I envision the code doing...

Sample data would be :

Table 1
DATA_ID, METRIC_ID
1,34
2,100
3,66
4, 72

Table 2
DATA_ID, Client_Platform
1, OS1
2, OS3
3, OS6
4, OS7

Ouptut:
*for every record in table 1, that is between 1 and 72, inclusive, the algorithm will find the matching data_id in table 2 and then update the client_platform to Linux. Here is the output sample:

Table 2
DATA_ID, Client_Platform
1, Linux
2, OS3
3, Linux
4, Linux

however, my code, the one from the previous post, just sets all records' client_platform to LINUX, which is WRONG .. PLEASE HELP.

Thanks,

xpandre
Posting Yak Master

212 Posts

Posted - 2009-11-18 : 10:28:11

isnt this as simple as

update t2 set Client_Platform = 'LINUX'
from t2 inner join t1 on t1.DATA_ID = t2.DATA_ID
where t1.METRIC_ID >= 1 and t1.METRIC_ID <= 72

or am I missing something here?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-18 : 11:10:17

Little point.

You should have a os table rather than storing multiple 'Linux', 'OS2' whatever.

No need for cursor here. as illustrated you can do this with simple joins.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

uchennavt
Starting Member

4 Posts

Posted - 2009-11-18 : 14:06:22

Thanks a lot guys! The simple SQL statement worked just fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources