Query based on record set of another query

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

SQL Server Development (2000)

Query based on record set of another query

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2005-04-06 : 13:57:59

I’m trying to do something in SQL that I’ve never done before and I need a little assistance. I'm using SQL Query Analyzer to write this.


select a.AttributeValue as GUID, b.CodeName
from SKU.dbo.t_DpAttribute a inner join SKU.dbo.t_Dp b
   on a.DpId = b.DpId
where a.DpAttributeKeyId = @attributeid

That query is complicated enough, but it simply returns 2 columns: a GUID and a codename. I’d like to step through the above query and check each GUID that is returned with a GUID from another database. I’m not sure how to step through it. So the pseudo-code would be something like:

--while not at the end of the result set (of the above query)
begin
Set the value of @codename to the current codename
Set the value of @guid to the current guid

IF EXISTS (select dp_GUID from ECommerce.dbo.ECM_DistributionPartners where dp_BrandingPrefix = @codename)
IF dp_GUID = @guid
--pass
ELSE
--fail. GUIDS don’t match
ELSE
--fail. CodeName not found.

--Next record
end

Is there an easy way to do this?

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 14:17:35

You don't want to get into cursoring through the resultset row-by-row unless there's no alternative.

Here's a set-based approach:


Select 
  DT.GUID,
  DT.CodeName,
  Case
  When E.dp_BrandingPrefix Is Null Then 'Fail: Codename not found'
  When E.dp_GUID = DT.GUID Then 'Pass'
  Else 'Fail: GUIDs dont match'
  End   As MatchResult
From 
(
  select a.AttributeValue as GUID, b.CodeName
  from SKU.dbo.t_DpAttribute a inner join SKU.dbo.t_Dp b
    on a.DpId = b.DpId
  where a.DpAttributeKeyId = @attributeid
) DT
Left Outer Join ECommerce.dbo.ECM_DistributionPartners As E
  On ( E.dp_BrandingPrefix = DT.CodeName )

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2005-04-06 : 14:49:37

Thank you! That is much more efficient than using a cursor.

The cursor solution is very inefficient when it's in a production environment. For testing purposes, it works well enough. But it shouldnt' be used if it's going to be run by many users. Here's the cursor solution:


declare @attributeid int

set @attributeid = (select DpAttributeKeyId from SKU.dbo.t_DpAttributeKey where DpAttributeKey='GUID')

declare my_cursor cursor for 
	select a.AttributeValue as GUID, b.CodeName
	from SKU.dbo.t_DpAttribute a inner join SKU.dbo.t_Dp b
		on a.DpId = b.DpId
	where a.DpAttributeKeyId = @attributeid

declare @attribute nvarchar(512)
declare @codename varchar(64)

open my_cursor

fetch next from my_cursor into @attribute, @codename

while (@@fetch_status = 0)
begin
	if EXISTS (select dp_GUID from ECommerce.dbo.ECM_DistributionPartners where dp_BrandingPrefix = @codename and dp_GUID = @attribute)
	begin
		print(@codename + ': pass')
	end
	else
	begin
		print(@codename + ': fail')
	end
	fetch next from my_cursor into @attribute, @codename
end

close my_cursor
deallocate my_cursor

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-06 : 15:47:31

it shouldn't be used at all. it's longer, slower, and harder to read. Do your best to get out of the habit of using them as soon as you can ... even in a "testing" environment.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources