Sql Server thinks nvarchar is money?

[big_al]

Hi

I have the followinf SPROC

CREATE PROCEDURE SearchPropertyListByGroup2
(
@WhereClause nvarchar(2000) = '',
@State varchar(500) = '',
@OrderBy varchar(50) = '',
@PriceMin money = 0,
@Pricemax money = 99999999
)
AS
	SET NOCOUNT ON 
 
  
	SELECT 
	   *
	FROM 
		PropertyView WITH (NOLOCK) 
	WHERE 
 PropertyId IN
 (
  SELECT 
   PropertyId 
  FROM 
   PropertyAttributes 
  WHERE
   AttributeId IN(SELECT number FROM int_list_to_table(@WhereClause))
 )
	AND State IN (SELECT txt FROM string_list_to_table(@State))
	AND (LongPrice Between @PriceMin AND @PriceMax)
	ORDER BY 
 CASE @OrderBy 
  WHEN 'PropertyType' THEN PropertyType 
  WHEN 'PropertyId' THEN Id 
  WHEN 'PropertyPrice' THEN LongPrice
  WHEN 'Location' THEN LocationId
  ELSE Id
 END 
	ASC 
 
GO

NOTE: int_list_to_table & string_list_to_table are functions that parse a delimited list.

Now the line that is causing the error is WHEN 'PropertyType' THEN PropertyType, the error I get is
'Error 257: Implicit Conversion from datatype varchar to money is not allowed, use the CONVERT function to run this query'

The SPROC runs fine if I do not allow for the 'PropertyType' sort.

I am searching a VIEW, could this be the cause of the problem?

Thanks in advance!

[r937]

pretty hard to tell what's happening without knowing the datatypes involved

also, "functions that parse a delimited list" gives me the willies, but you didn't ask about that, did you


try CAST in the ORDER BY to make sure each possibility is explicitly a VARCHAR

[big_al]

Hi rudy!

PropertyType = Nvarchar,
PropertyId = Int,
PropertyPrice = money,
LocationId = int

When you mean CAST the order by, did you mean somthing like CASE CAST(@OrderBy as int) -- so we get the int / index of the column instead of the name?

Also, what do you do to pass a delimited list to a SPROC?

[r937]

no, i meant cast each of the individual columns as varchar -- PropertyType, Id, LongPrice, LocationId

that's where your error is coming from, it thinks the order by has to be a money column

the only alternative i can think of is to write the ORDER BY to use an ordinal column position, e.g. ORDER BY 2 (using the appropriate CASE to generate the ordinal, e.g. 2) -- but i haven't tested this

as for passing a list to a stored proc, i think this is a pretty decent tutorial -- http://vyaskn.tripod.com/passing_arr...procedures.htm

[big_al]

Thanks Rudy, will give that a go

(Wonder what the IOU Beer count is up to )