Dynamic WHERE caluse for this little problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Dynamic WHERE caluse for this little problem?

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 10:33:34

Hi all, thanks in advance for reading this and trying to help me with finding a solution this little problem.

I have the following table:


if object_id('tempdb..#VehMakeVehModel') > 0
drop table #VehMakeVehModel

create table #VehMakeVehModel (
	VehMake varchar(20)
	,VehModelId int identity(1, 1)
	,VehModel varchar(50)
	,VehTransmission char(1)
	,VehFuelType char(1)
	,NumberOfDoors int
	,ManufacturedFrom int
	,ManufacturedTo int
	)

insert into #VehMakeVehModel
select 'VOLKSWAGEN', 'GOLF GT TDI (110)', 'A', 'D', 4, 1980, 1995 union all
select 'VOLKSWAGEN', 'GOLF GT TDI (110)', 'A', 'P', 4, 1996, 1997 union all
select 'VOLKSWAGEN', 'GOLF GT TDI (110)', 'M', 'P', 5, 1998, null

As you can see, this table holds information about various vehicle makes and models. I've only included 3 sample records as I believe that should be sufficient for this exercise.

I am trying to develop a stored procedure where I pass a table holding vehicle histories of people and update the missing VehModelId in that table. Sounds simple enough right? Well not quite..
The table that I pass to the stored procedure looks something like this:


if object_id('tempdb..#myStagingTable') > 0
drop table #myStagingTable

create table #myStagingTable (
	PersonId int
	,VehMake varchar(20)
	,VehModel varchar(50)
	,VehModelId int
	,VehTransmission char(1)
	,NumberOfDoors int
	)

insert into #myStagingTable
select 101, 'VOLKSWAGEN', 'GOLF GT TDI (110)', null, 'A', 4 union all
select 102, 'VOLKSWAGEN', 'GOLF GT TDI (110)', null, 'M', 5

To update the missing VehModelId in the second table I can write an update statement like so:


update a
set VehModelId = b.VehModelId
from #myStagingTable as a
inner join #VehMakeVehModel as b
	on a.VehMake = b.VehMake
and a.VehModel = b.VehModel

That is obviously wrong as this update assigns the same VehModelId for both records. We can include the VehTransmission and NumberOfDoors columns in our update like so:


update a
set VehModelId = b.VehModelId
from #myStagingTable as a
inner join #VehMakeVehModel as b
	on a.VehMake = b.VehMake
and a.VehModel = b.VehModel
and a.VehTransmission = b.VehTransmission
and a.NumberOfDoors = b.NumberOfDoors

This looks better but if you notice, the update has assigned the same VehModelId for the first two rows which is incorrect. Obviously the more information we have, for example Yearmanufactured etc.. will allow for better matches but in this instance, I would reject the update as more than one record is matching.

I hope that so far, this part of my problem makes sense. Now on to the serious bit. How can I make the WHERE clause dynamic?! What if my staging table varies? So sometimes, I might have a Staging table that has VehTransmission only, or sometimes I have VehTransmission and FuelType AND NumberOfDoors?

Can anyone help please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 10:42:01

What do you mean you staging table varies? do you mean its structure wil vary or is it that fields will contain null values sometimes?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 10:44:14

quote:
Originally posted by visakh16

What do you mean you staging table varies? do you mean its structure wil vary or is it that fields will contain null values sometimes?

I mean the structure will vary, so sometimes you will get a table that has VehTransmission, on other occasions the table will have VehTransmission AND VehFuel etc.. By the way, I am referring to #myStagingTable here.

I hope it made sense what I need to do. Ultimately I need update the VehModelId with a value from #VehMakeVehModel.

Let me know if this doesn't make sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 10:51:40

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

What do you mean you staging table varies? do you mean its structure wil vary or is it that fields will contain null values sometimes?

I mean the structure will vary, so sometimes you will get a table that has VehTransmission, on other occasions the table will have VehTransmission AND VehFuel etc.. By the way, I am referring to #myStagingTable here.

I hope it made sense what I need to do. Ultimately I need update the VehModelId with a value from #VehMakeVehModel.

Let me know if this doesn't make sense.

Why is it that structure varies? how are you generating the staging table each time?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 10:54:53

quote:
Originally posted by visakh16

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

What do you mean you staging table varies? do you mean its structure wil vary or is it that fields will contain null values sometimes?

I mean the structure will vary, so sometimes you will get a table that has VehTransmission, on other occasions the table will have VehTransmission AND VehFuel etc.. By the way, I am referring to #myStagingTable here.

I hope it made sense what I need to do. Ultimately I need update the VehModelId with a value from #VehMakeVehModel.

Let me know if this doesn't make sense.

Why is it that structure varies? how are you generating the staging table each time?

Don't worry about how the staging table is generated!

Imagine it's a stored procedure where you pass it the table #myStagingTable. It could have different Vehicle info, sometimes it will only have the VehMake, VehModel and VehFuelType. Other time it might have VehMake, VehModel and VehTransmission. The goal is to find a match for it the #VehMakeVehModel table and assign the correct ID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 11:19:39

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

What do you mean you staging table varies? do you mean its structure wil vary or is it that fields will contain null values sometimes?

I mean the structure will vary, so sometimes you will get a table that has VehTransmission, on other occasions the table will have VehTransmission AND VehFuel etc.. By the way, I am referring to #myStagingTable here.

I hope it made sense what I need to do. Ultimately I need update the VehModelId with a value from #VehMakeVehModel.

Let me know if this doesn't make sense.

Why is it that structure varies? how are you generating the staging table each time?

Don't worry about how the staging table is generated! Imagine it's a stored procedure where you pass it the table #myStagingTable. It could have different Vehicle info, sometimes it will only have the VehMake, VehModel and VehFuelType. Other time it might have VehMake, VehModel and VehTransmission. The goal is to find a match for it the #VehMakeVehModel table and assign the correct ID.

Ok even in that case you could have all the columns in your staging table so that whatever values returned by stored procedure will be populated to staging table and rest all will have null values and then you can use like this

update a
set VehModelId = b.VehModelId
from #myStagingTable as a
inner join #VehMakeVehModel as b
	on a.VehMake = b.VehMake
and a.VehModel = b.VehModel
and (a.VehTransmission = b.VehTransmission OR a.VehTransmission IS NULL) 
and (a.NumberOfDoors = b.NumberOfDoors OR a.NumberOfDoors IS NULL) 
...

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-06-11 : 11:37:42

quote:
Originally posted by visakh16

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

quote:
Originally posted by Abu-Dina

quote:
Originally posted by visakh16

What do you mean you staging table varies? do you mean its structure wil vary or is it that fields will contain null values sometimes?

I mean the structure will vary, so sometimes you will get a table that has VehTransmission, on other occasions the table will have VehTransmission AND VehFuel etc.. By the way, I am referring to #myStagingTable here.

I hope it made sense what I need to do. Ultimately I need update the VehModelId with a value from #VehMakeVehModel.

Let me know if this doesn't make sense.

Why is it that structure varies? how are you generating the staging table each time?

Don't worry about how the staging table is generated! Imagine it's a stored procedure where you pass it the table #myStagingTable. It could have different Vehicle info, sometimes it will only have the VehMake, VehModel and VehFuelType. Other time it might have VehMake, VehModel and VehTransmission. The goal is to find a match for it the #VehMakeVehModel table and assign the correct ID.

Ok even in that case you could have all the columns in your staging table so that whatever values returned by stored procedure will be populated to staging table and rest all will have null values and then you can use like this

update a
set VehModelId = b.VehModelId
from #myStagingTable as a
inner join #VehMakeVehModel as b
on a.VehMake = b.VehMake
and a.VehModel = b.VehModel
and (a.VehTransmission = b.VehTransmission OR a.VehTransmission IS NULL)
and (a.NumberOfDoors = b.NumberOfDoors OR a.NumberOfDoors IS NULL)
...

But that query assumes my staging table will have all the columns. What if I pass a staging table that doesn't have all these columns?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources