Fetch into array - 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 2008 Forums

Transact-SQL (2008)

Fetch into array

Author

Topic

wiss.dev
Starting Member

5 Posts

Posted - 2012-05-23 : 22:24:46

hi all
im writing a trigger and need to do the following
e.g.


if exists (select 1 from names where id=1 )
  begin
  select @job=(select job from names where id=1)
  select @ age=(select age from names where id=1)
select @city=(select city from names where id=1)
-- and so on
  end

is there a better way, like fetching inot a rowset and then have like @job=row.job
@age=row.age ....

much appreciated for any suggestions

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-05-23 : 23:04:35

[code]
select @job= job ,
@ age= age,
@city= city
from names where id=1

[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 16:16:02

i dont think you should be fetching from names table. If you're looking for only records that involved in DML operation you should be using inserted and deleted tables instead

Also you need to use table variable rather than variables to hold values to account for batch DML operations

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wiss.dev
Starting Member

5 Posts

Posted - 2012-06-01 : 01:45:45

not in that particular example. but i defenatly need a cursor in the trigger im trying to write now. i need to loop through a set of records, i googled and found this example

DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

but, this only exposes one field into the cursor,, very tidious to go and repeat this

FETCH NEXT
FROM @getAccountID INTO @AccountID
END

for every field

?? any better suggestions
Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-01 : 08:07:09

quote:
but i defenatly need a cursor in the trigger im trying to write now. i need to loop through a set of records

What makes you think you need a cursor? This one statement:

SELECT Account_ID FROM Accounts

Provides the exact same results as the rest of the code wrapped around it. That additional code does nothing (nothing useful anyway).

What exactly do you need to do in the trigger? (hint: it's not cursoring/looping through rows)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 11:41:20

quote:
Originally posted by wiss.dev

not in that particular example. but i defenatly need a cursor in the trigger im trying to write now. i need to loop through a set of records, i googled and found this example
DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

but, this only exposes one field into the cursor,, very tidious to go and repeat this
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
for every field

?? any better suggestions
Thanks

Again this is not a good representation of scenario which calls for use of cusror. Unless you've some specific row by row complex processing which you cant do efficiently using a set based approach, there's no need for using a cursor like above. And I cant understand what row by row processing you're trying to do inside a trigger

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources