Solved

Modifying dynamic search query to return different fields

Posted on 2008-06-24
13
183 Views
Last Modified: 2010-04-21
Hi guys,

I have a .net app communicating with SQL Server 2005 on the backend. This database houses products for a client, and there is some code that will generate all products contained within a category and build a gridview on the front end dynamically. When I say dynamically, lets say we have a total of 7 fields in the database (for arguments sake, there are many more than that), as such: A (identity), B, C, D,  E, F, and G. We have product x and product y (values for A 1 and 2, respectively). Product x may only have 3 of these fields, minus the identity, wherein Product y has all 7.

I have a linking table in the database that sort of "sets up" the grid. Product x will have bit fields turned on for B, C and D whereas product y has all available bit fields turned on.

This doesnt have THAT much to do with my issue, as I have a stored procedure which builds this on the fly. Now here is my problem. I am not a big SQL guy, and I had some help (from EE actually) to build the stored proc I am listing below, that controls the dynamic build. My issue is, that the grid on the front end is directly bound to the output of the stored procedure. So if a variable is not coming out from the stored proc, its not bound to the grid.

Using my theoretical fields above, lets say that I need to output Fields B, C and D as one field, combining it into one returned column. So we're not using completely obtuse variables here, i'll explain the exact fields im trying to combine, so it makes more sense. Each product in the database has dimensions. So Product x might be a cube shape, with Dimensions Length 4 (Field B), Width 5-1/5 (Field C), and Height 3 (Field D). I want to output this like so : 4" x 5-1/2" x 3". So the raw numbers from those fields should have the added stuff on it (the ", the x) and then be returned as one column (we'll call it Dimensions)

I hope this explanation makes sense. Its a pretty simple idea that is sort of hard to put across in words without the database to reference. Let me know if I can provide any more detail. PLEASE BE SPECIFIC IN YOUR HELP, I am not a DBA and my t-sql is shaky at best. I really appreciate it!

Listed below is the query from the stored procedure (SearchParams is the dynamic fields to look for, using the examples above it would be 'A,B,C,D,E,F,G', comma delimited):

@SearchParams varchar(1000),
      @ProductCategoryID int
AS
BEGIN

      SET NOCOUNT ON;

declare @pos int
declare @piece varchar(500)
declare @sql varchar(4000)

set @sql = 'SELECT '
if right(rtrim(@SearchParams),1) <> ','
set @SearchParams = @SearchParams  + ','

set @pos =  patindex('%,%' , @SearchParams)
while @pos <> 0
begin
 set @piece = left(@SearchParams, @pos - 1)

 set @SearchParams = stuff(@SearchParams, 1, @pos, '')
 set @pos =  patindex('%,%' , @SearchParams)
      if @pos <> 0
      begin
      set @sql = @sql + cast(@piece as varchar(500)) +  ','
      end
      else
      begin
set @sql = @sql + cast(@piece as varchar(500))
      end
end
set @sql = replace(@sql, right((@sql), 0), '')

set @sql = @sql + ' FROM tblProducts'
set @sql = @sql + ' WHERE ProductCategoryID = ' + cast(@ProductCategoryID as varchar)


execute(@sql)
0
Comment
Question by:nuvium
  • 7
  • 6
13 Comments
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
Can you specify your table names, view, datatype?  For an example, Product Table has

ProductId                 int(100,1) Primary Key,
ProductName           varchar(150),
ProductDate             Datetime,
ProductDescription   varchar(500)

then I can see the database relationship so people can help you here.
0
 

Author Comment

by:nuvium
Comment Utility
Sure, these are close enough for the example:

ProductID int PK,
ModelNumber varchar(20),
DimLength varchar(10),
DimWidth varchar(10),
DimHeight varchar(10)

I want to modify the above listed stored proc query, where if the SearchParams that go into the stored proc contain DimLength,DimWidth,DimHeight, it will combine the values from all three fields (which are simple numerics, such as 5, 4-1/4. 3), add in the appropriate stuff, and return it as ONE column, in this case "Dimensions". The "Dimensions" column will contain something that looks like this (note the descriptions):

5" x 4-1/4" x 3"

This, broken down is DimLength(") space "x" space DimWidth(") space "x" space DimHeight(")
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
Create Procedure dbo.usp_Search
(
       @paraSearchCriteriaA       varchar(150),
       @paraSearchCriteriaB       varchar(150)
)


---------- CASE only work in here if the data are coming from the same table otherwise use different methods ----
Select
                DimLength + 'X'+DimWidth+'X'+DimHeight as ItemDimension
From
       Product
Where
       Case WHEN @paraSearchCriteriaA<>''   THEN DimLength
                WHEN @paraSearchCriteriaA=''     THEN 'Eliminate' END Like
       Case WHEN @paraSearchCriteriaA<>''   THEN @paraSearchCriteriaA + '%'
                WHEN @paraSearchCriteriaA=''     THEN 'Eliminate' END Like

and
       Case WHEN @paraSearchCriteriaB<>''   THEN DimWidth
                WHEN @paraSearchCriteriaB=''     THEN 'Eliminate' END Like
       Case WHEN @paraSearchCriteriaB<>''   THEN @paraSearchCriteriaB + '%'
                WHEN @paraSearchCriteriaB=''     THEN 'Eliminate' END Like
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
Create Procedure dbo.usp_Search
(
       @paraSearchCriteriaA       varchar(150),
       @paraSearchCriteriaB       varchar(150)
)

if @paraSearchCriteriaA <>''
   Begin
--------------------------- If something enter into search criteria a then perform Begin Here -----------------------
Select
                DimLength + 'X'+DimWidth+'X'+DimHeight as ItemDimension
From
       Product, ProductDetail
Where
       Product.ProductId = ProductDetail.ProductId
and
       Product.DimLength = @paraSearchCriteriaA
--------------------------- If something enter into search criteria a then perform Ending Here -----------------------

   End

Else
   Begin
             --- Use different search criteria and where clause --------
   End

     
0
 

Author Comment

by:nuvium
Comment Utility
I see your logic, however what i really need help with is integrating a solution like this into my existing stored procedure (listed in the main question above). That stored procedure works dynamically exactly the way I want it to. What I need to do is integrate something into it where if the SearchParams variable entering into the sp contains DimLength,DimWidth, and DimHeight return them all as a single column called 'Dimensions'. Part of the reason why I am looking for help here is the fact that my T-SQL is rather weak, and I don't know how to make one segment of the query play nice with the other.
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
You need to test the SQL script I posted.  That's part of been in IT!  Play around with different conditioning.  You need to test the stored procedure in SQL Query Analyzer first to see if the output meet your expectation.  

For an example,  Create a tested stored procedure first then run it.  

Exec dbo.usp_Search 3, 5
                      --- Execute Stored Procedure dbo.usp_Search with input parameter 3 and 5
                            then you see what the output results are and fine tune the where clause
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:nuvium
Comment Utility
I honestly appreciate the help, but youre missing a massive factor of my issue. Your SQL script could work perfectly, or I could toy with it until the cows come home, and it wouldnt matter in the slightest. I don't know how to integrate it into the stored procedure I listed above.

The reason why I pay for this service is to get newbie-level help on time sensitive projects. Not to mention I never said I was in IT :)

I'll take your suggestions into consideration, but what I need help with is modifying the above stored procedure to return three separate columns as one column return into a gridview. Not just the mechanism behind it, but the actual integration into an existing working stored proc.
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
Let me email you the complete code here!
0
 

Author Comment

by:nuvium
Comment Utility
Ill certainly look at anything you send me, I can use all the help I can get on this!
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
Here is the complete code of what I had for search function
Should have presentation layer of the html code here
0
 
LVL 7

Accepted Solution

by:
60MXG earned 500 total points
Comment Utility
what is your email.  I will email you the my source code here
0
 

Author Comment

by:nuvium
Comment Utility
you can email it brian@nuvium.com

Thanks, I'll take a look at it immediately.
0
 

Author Closing Comment

by:nuvium
Comment Utility
Wasnt exactly what I needed, but good enough that I was able to adapt it myself.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now