Modifying dynamic search query to return different fields

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)
nuviumAsked:
Who is Participating?
 
60MXGConnect With a Mentor Commented:
what is your email.  I will email you the my source code here
0
 
60MXGCommented:
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
 
nuviumAuthor Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
60MXGCommented:
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
 
60MXGCommented:
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
 
nuviumAuthor Commented:
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
 
60MXGCommented:
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
 
nuviumAuthor Commented:
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
 
60MXGCommented:
Let me email you the complete code here!
0
 
nuviumAuthor Commented:
Ill certainly look at anything you send me, I can use all the help I can get on this!
0
 
60MXGCommented:
Here is the complete code of what I had for search function
Should have presentation layer of the html code here
0
 
nuviumAuthor Commented:
you can email it brian@nuvium.com

Thanks, I'll take a look at it immediately.
0
 
nuviumAuthor Commented:
Wasnt exactly what I needed, but good enough that I was able to adapt it myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.