Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Modifying dynamic search query to return different fields

Posted on 2008-06-24
13
Medium Priority
?
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 7

Expert Comment

by:60MXG
ID: 21860369
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
ID: 21860469
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
ID: 21860625
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
PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

 
LVL 7

Expert Comment

by:60MXG
ID: 21860650
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
ID: 21860692
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
ID: 21860743
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
 

Author Comment

by:nuvium
ID: 21860789
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
ID: 21860814
Let me email you the complete code here!
0
 

Author Comment

by:nuvium
ID: 21860823
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
ID: 21860844
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 2000 total points
ID: 21860854
what is your email.  I will email you the my source code here
0
 

Author Comment

by:nuvium
ID: 21860863
you can email it brian@nuvium.com

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

Author Closing Comment

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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