SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
Jan 22, 2005, 16:38 #1
- Join Date
- May 2000
- Location
- Victoria, Australia
- Posts
- 1,661
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Sql Server thinks nvarchar is money?
Hi
I have the followinf SPROC
Code: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
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!.NET Code Monkey
-
Jan 22, 2005, 16:47 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 22, 2005, 17:03 #3
- Join Date
- May 2000
- Location
- Victoria, Australia
- Posts
- 1,661
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?.NET Code Monkey
-
Jan 22, 2005, 17:23 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 22, 2005, 22:06 #5
- Join Date
- May 2000
- Location
- Victoria, Australia
- Posts
- 1,661
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Rudy, will give that a go
(Wonder what the IOU Beer count is up to)
.NET Code Monkey
Bookmarks