Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Dynamic Select and Where Clauses

Posted on 2006-04-06
3
Medium Priority
?
495 Views
Last Modified: 2012-06-21
I'm trying to create a stored procedure to populate a report. This sp has three parms: @DrillLevel which equates to Country, State, City, etc., @Column which can indicate a column to filter the results by, and @List, which can supply criteria for @Column.

When I run the procedure as "spGet_Cust_Prod_Info @DrillLevel=0" I get the error:
Server: Msg 245, Level 16, State 1, Procedure spGet_Cust_Prod_Info, Line 26
Syntax error converting the varchar value 'Mail_Country' to a column of data type int.

When I run the procedure as "spGet_Cust_Prod_Info @DrillLevel=0, @Column='Brand_Desc', @List='Jet'" I get the error:
Server: Msg 245, Level 16, State 1, Procedure fnSplitter, Line 18
Syntax error converting the varchar value 'Jet' to a column of data type int.

What am I doing wrong?
Code:
ALTER       PROCEDURE spGet_Cust_Prod_Info
      (@DrillLevel            int, --Level of detail
       @Column            varchar(100)=NULL, --Column to sort data on
       @List                  varchar(200)=NULL) --List of values for sort column

AS
DECLARE @ColVal varchar(100)

-- if we only have the drill level, set the sort column = to the column of the detail level
If (@List IS NULL) AND (@Column IS NULL)
      Begin
            Set @ColVal =
                  Case       When @DrillLevel=0 Then Mail_Country
                        When @DrillLevel=1 then Mail_State
                        When @DrillLevel=2 Then Mail_City
                        When @DrillLevel=3 Then Mail_Zip
                  End
      End
-- start building select statement

Select
      Case @DrillLevel
            When 0 Then Mail_Country
            When 1 then Mail_State
            When 2 Then Mail_City
            When 3 Then Mail_Zip
      End AS Region,
      Count(Cust_Info.IRN) as Users

-- join statement
FROM      dbo.Cust_Info LEFT OUTER JOIN
      dbo.Purchases ON dbo.Cust_Info.IRN = dbo.Purchases.IRN LEFT OUTER JOIN
      dbo.Mat_Mstr ON dbo.Purchases.Mat_Num = dbo.Mat_Mstr.Mat_Num LEFT OUTER JOIN
      dbo.Brand_Xref ON dbo.Mat_Mstr.Brand_Cd = dbo.Brand_Xref.Brand_cd LEFT OUTER JOIN
      dbo.Mkt_Xref ON dbo.Mat_Mstr.Mkt_Cd = dbo.Mkt_Xref.Mkt_Cd LEFT OUTER JOIN
      dbo.Ph_Xref ON dbo.Mat_Mstr.Ph_Cd = dbo.Ph_Xref.Ph_cd LEFT OUTER JOIN
      dbo.Cat_Xref ON dbo.Mat_Mstr.Cat_Cd = dbo.Cat_Xref.Cat_Cd

-- set sort column criteria
-- If no list is passed, set sort column = to detail level column
WHERE @Column =

      Case WHEN @List IS NOT NULL Then (Select ID from fnSplitter(@List))
            When @List Is Null Then @ColVal
      End

-- group by appropriate level
Group By
      Case @DrillLevel
            When 0 Then Mail_Country
            When 1 then Mail_State
            When 2 Then Mail_City
            When 3 Then Mail_Zip
      End
0
Comment
Question by:Saney
  • 2
3 Comments
 
LVL 9

Expert Comment

by:gabeso
ID: 16392971
The sql wants to return a single column for region with a definite type - this it is inheriting from @DrillLevel

Try converting it to string:

convert( varchar(100), @drilllevel )

as in

Case convert( varchar(100), @DrillLevel )
          When 0 Then Mail_Country
          When 1 then Mail_State
          When 2 Then Mail_City
          When 3 Then Mail_Zip
     End AS Region

Or whatever size the other fields can be converted to ...
0
 

Author Comment

by:Saney
ID: 16393271
I tried it with no luck. I think my problem ultimately lies in this block:
WHERE @Column =

     Case WHEN @List IS NOT NULL Then (Select ID from fnSplitter(@List))
          When @List Is Null Then @ColVal
     End

I need @ColVal to be a column name, not a string value.

I changed the block to this:
WHERE @Column =
      Case WHEN Len(@List)= 0 THEN (Select ID from fnSplitter(@List))
            WHEN Len(@List) BETWEEN 1 and 9999 THEN      @Column
      END
and the sp will run with @DrillLevel=0. However, it does not return any rows because the WHERE clause evaluates to "WHERE NULL=NULL". When it is run with @DrillLevel=0, @Column='Brand_Desc', @List='Jet', I receive this error:
Server: Msg 245, Level 16, State 1, Procedure fnSplitter, Line 18
Syntax error converting the varchar value 'Jet' to a column of data type int.

Is my problem that I'm trying to pass column names?
0
 
LVL 9

Accepted Solution

by:
gabeso earned 1500 total points
ID: 16395080
No problem ...

Just pass in a code and use a case statement to map that code to the corresponding column.

I don't think you can pass in a column and the reason has to do with the need to build a known result set using fields from tables at 'compile time'.

There is something innately wrong with trying to construct variable results and I can't quite put my finger on it: I think you should by and large be building a known resultset and let the application do different things with it using objects etc.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Introduction to Processes

580 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