We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Dynamic Select and Where Clauses

Saney
Saney asked
on
Medium Priority
508 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
Comment
Watch Question

gabesoSolution Architect

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

Author

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?
Solution Architect
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.