Combining Multiple Fields Into One For Query 1

[sk1hotpepr]

I have a query to search for a person that worked on a project. I have five different fields that the persons name could be listed under, depending on what they did in that job. Ex. Person1 could be listed as the ProjectManager or could be listed as ProjectDesigner or as StudioDirector. I want to be able to find them for either position by their name only. I want to be able to combine the fields ProjectManager, ProjectDesigner and StudioDirector into one field (DTeam) so that when I run the stored procedure, I only have to check that one field - not an OR statement for each field. Is this possible?

 

[donutman]

There are several ways to do that, but the best is to just use the OR statements within the SP unless this table has more than 1M rows and you need to do this frequently.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

You might be able to concatenate the fields if they are all of the same or similar datatypes. Create a view with a new column that is all three added together, then run your SP against that.

Create View Names_vw as
Select ...,..,(ProjectManager + ' ' + ProjectDesigner + ' ' + StudioDirector) as Dteam, ...
from Table1

The ellipses represent the other columns you would be pulling before and after the concatenated columns. Then you could do a search in your SP with a Like '%PersonName%' as part of the Where statement.

Honestly, though, I don't know that this would be better performancewise then the Or statement Donutman mentions.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[sk1hotpepr]

I have tried the OR statement but my stored procedure takes those OR statements and automatically creates all of the different options which then gives me an error that I have too many parameters. I've tried to do this with its own select statement under the stored procedure but am unsure how to retrieve that information...

 

[donutman]

Do you mean that you are using dynamic SQL? Why not post the SP as is. Glad to help you fix it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[sk1hotpepr]

Now I'm not sure, but I just tried using IN
(@dt IN (ProjectManager, StudioDirector, InteriorDesigner, ProjectDesigner, ConstAdmin)) and it seemed to work without giving me the big crazy "OR" statement. Just doing a quick run, it seems like it's going to be okay, are there problems with doing it that way that you might know of? The stored procedure is treating the IN statement as one argument instead of five. I'll let you know how it goes! Thanks so much for your help!

 

[sk1hotpepr]

Now I'm not sure, but I just tried using IN
(@dt IN (ProjectManager, StudioDirector, InteriorDesigner, ProjectDesigner, ConstAdmin)) and it seemed to work without giving me the big crazy "OR" statement. Just doing a quick run, it seems like it's going to be okay, are there problems with doing it that way that you might know of? The stored procedure is treating the IN statement as one argument instead of five. I'll let you know how it goes! Thanks so much for your help!

 

[sk1hotpepr]

DonutMan,

BTW, This is what I mean about my stored procedure creating the different options:

 (dbo.ProjectClientInfo.State = @st) AND (dbo.ProjectClientInfo.County = @cnty) AND (@dt IN (dbo.ProjectDesignTeam.SD, 
                      dbo.ProjectDesignTeam.PAPM, dbo.ProjectDesignTeam.PD, dbo.ProjectDesignTeam.IDes, dbo.ProjectDesignTeam.SW, dbo.ProjectDesignTeam.CE, 
                      dbo.ProjectDesignTeam.QC, dbo.ProjectDesignTeam.CA) OR
                      @dt IS NULL) OR
                      (dbo.ProjectClientInfo.State = @st) AND (@dt IN (dbo.ProjectDesignTeam.SD, dbo.ProjectDesignTeam.PAPM, dbo.ProjectDesignTeam.PD, 
                      dbo.ProjectDesignTeam.IDes, dbo.ProjectDesignTeam.SW, dbo.ProjectDesignTeam.CE, dbo.ProjectDesignTeam.QC, dbo.ProjectDesignTeam.CA) OR
                      @dt IS NULL) AND (@cnty IS NULL) OR
                      (dbo.ProjectClientInfo.County = @cnty) AND (@dt IN (dbo.ProjectDesignTeam.SD, dbo.ProjectDesignTeam.PAPM, dbo.ProjectDesignTeam.PD, 
                      dbo.ProjectDesignTeam.IDes, dbo.ProjectDesignTeam.SW, dbo.ProjectDesignTeam.CE, dbo.ProjectDesignTeam.QC, dbo.ProjectDesignTeam.CA) OR
                      @dt IS NULL) AND (@st IS NULL) OR
                      (@dt IN (dbo.ProjectDesignTeam.SD, dbo.ProjectDesignTeam.PAPM, dbo.ProjectDesignTeam.PD, dbo.ProjectDesignTeam.IDes, 
                      dbo.ProjectDesignTeam.SW, dbo.ProjectDesignTeam.CE, dbo.ProjectDesignTeam.QC, dbo.ProjectDesignTeam.CA) OR
                      @dt IS NULL) AND (@cnty IS NULL) AND (@st IS NULL)

 

[donutman]

That's the same as

 [Gray]([/Gray]@dt [Blue]IN[/Blue] [Gray]([/Gray]DT.SD[Gray],[/Gray] DT.PAPM[Gray],[/Gray] DT.PD[Gray],[/Gray] 
          DT.IDes[Gray],[/Gray] DT.SW[Gray],[/Gray] DT.CE[Gray],[/Gray] DT.QC[Gray],[/Gray] DT.CA[Gray])[/Gray] [Gray]OR[/Gray] @dt [Blue]IS[/Blue] [Gray]NULL[/Gray][Gray])[/Gray] [Gray]AND[/Gray]
 [Gray]([/Gray]CI.State[Gray]=[/Gray]@st [Gray]OR[/Gray] @st [Blue]IS[/Blue] [Gray]NULL[/Gray][Gray])[/Gray] [Gray]AND[/Gray] 
 [Gray]([/Gray]CI.County[Gray]=[/Gray]@cnty [Gray]OR[/Gray] @cnty [Blue]IS[/Blue] [Gray]NULL[/Gray][Gray])[/Gray]

But you have to alias dbo.ProjectDesignTeam as DT
and alias dbo.ProjectClientInfo as CI.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[sk1hotpepr]

Good suggestion, I just made my changes. Thank you Karl!