Dynamic Select and Where Clauses

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
SaneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gabesoSolution ArchitectCommented:
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
SaneyAuthor Commented:
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
gabesoSolution ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

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.