Fetch Next From target_cursor and ColumnName

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)

Fetch Next From target_cursor and ColumnName

Author

Topic

aax
Starting Member

11 Posts

Posted - 2005-08-24 : 17:28:21

Does anyone know of a better way to group this so that it isn't so big. I currently have make and model but also need to add series, bodystyle, vin_full, vin_last8, miles, retailprice, internetprice and stocknum

So it might not be big now but after I it is complete it would be rather large. Not sure if there is an easier way.

-- Make
declare target_cursor CURSOR FOR
select
a.column_name, a.type_code
from
lnr_fields a inner join
lnr_sub_pub_field_rel b on a.field_id = b.field_id
where
a.column_name = 'vm.make' and b.sub_publication_id = @I_SubPubID
order by
b.field_order

Open target_cursor

Set @varMakeColumn = ''
Set @sql = ''
Fetch Next From target_cursor Into @cName, @typeCode
While @@FETCH_STATUS = 0
Begin
If len(@varMakeColumn) > 0
Begin
Set @varMakeColumn = @varMakeColumn + ' + ''|'' + '
End
Set @varMakeColumn = @varMakeColumn + '''' + cast(@typeCode as varchar(15)) + '|'' + ' + 'isnull(cast(' + @cName + ' as varchar(250)),'''')'
Fetch Next From target_cursor Into @cName, @typeCode
End
Close target_cursor
Deallocate target_cursor
--

-- Model
declare target_cursor CURSOR FOR
select
a.column_name, a.type_code
from
lnr_fields a inner join
lnr_sub_pub_field_rel b on a.field_id = b.field_id
where
a.column_name = 'vm.make' and b.sub_publication_id = @I_SubPubID
order by
b.field_order

Open target_cursor

Set @varModelColumn = ''
Set @sql = ''
Fetch Next From target_cursor Into @cName, @typeCode
While @@FETCH_STATUS = 0
Begin
If len(@varModelColumn) > 0
Begin
Set @varModelColumn = @varModelColumn + ' + ''|'' + '
End
Set @varModelColumn = @varModelColumn + '''' + cast(@typeCode as varchar(15)) + '|'' + ' + 'isnull(cast(' + @cName + ' as varchar(250)),'''')'
Fetch Next From target_cursor Into @cName, @typeCode
End
Close target_cursor
Deallocate target_cursor
--

KLang23
Posting Yak Master

115 Posts

Posted - 2005-08-24 : 18:49:35

Hi,

You may want to try this trick.


Set Concat_NULL_Yields_NULL OFF  -- This is needed!
Declare @ResultString varchar(8000)
Set @ResultString =
          cast(@typeCode as varchar(15)) + '|'' + ' 
       + 'isnull(cast(' + @cName + ' as varchar(250)),'''')'

Do this once for each group. If you are clever and have some time, you may be able to make it dynamic and get it done in one shot. It essentially eliminates the need for your cursor.

I don't have time, and I'm not too clever. I just wanted so show you the technique.

Good luck.

KLang23
Posting Yak Master

115 Posts

Posted - 2005-08-24 : 18:51:17

Sorry - I had to ammend the code:


Set Concat_NULL_Yields_NULL OFF  -- This is needed!
Declare @ResultString varchar(8000)
Set @ResultString = @ResultString +
          cast(@typeCode as varchar(15)) + '|'' + ' 
       + 'isnull(cast(' + @cName + ' as varchar(250)),'''')'

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-24 : 18:58:43

quote:

Originally posted by KLang23

Sorry - I had to ammend the code:


Set Concat_NULL_Yields_NULL OFF  -- This is needed!
Declare @ResultString varchar(8000)
Set @ResultString = @ResultString +
          cast(@typeCode as varchar(15)) + '|'' + ' 
       + 'isnull(cast(' + @cName + ' as varchar(250)),'''')'

KLang23......you could have just edited your reply instead.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources