asked on

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

gabeso

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 ...

Saney

ASKER

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?

ASKER CERTIFIED SOLUTION

gabeso

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial