当前位置: 动力学知识库 > 问答 > 编程问答 >

sqldatatypes - error converting varchar to float ms sql

问题描述:

I am getting the above error in this SP. (Table Storage) Columns MaxWeight, Height, Width, & Depth are float. Anyideas?

 USE [Induction]

GO

/****** Object: StoredProcedure [dbo].[usp_GetStorageDetails1] Script Date: 06/14/2013 14:39:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_GetStorageDetails1]

@RowID int,

@WarehouseID int,

@query VARCHAR(100),

@sortcol VARCHAR(100),

@sortorder VARCHAR(100),

@total int OUTPUT

AS

BEGIN

Select * into #PAGING FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY

CASE WHEN @sortcol='WarehouseID' AND @sortorder = 'asc' THEN W.ID END ASC,

CASE WHEN @sortcol='WarehouseID' AND @sortorder = 'desc' THEN W.ID END DESC,

CASE WHEN @sortcol='Name' AND @sortorder = 'asc' THEN W.Name END ASC,

CASE WHEN @sortcol='Name' AND @sortorder = 'desc' THEN W.Name END DESC,

CASE WHEN @sortcol='Name' AND @sortorder = 'asc' THEN ST.Name END ASC,

CASE WHEN @sortcol='Name' AND @sortorder = 'desc' THEN ST.Name END DESC,

CASE WHEN @sortcol='Code' AND @sortorder = 'asc' THEN (ISNULL(S.IdentifierA,'')+ '-' + ISNULL(S.IdentifierB,'') + '-' + ISNULL(S.IdentifierC,'') + '-' + ISNULL(S.IdentifierD,'')+ '-' + ISNULL(S.IdentifierE,'')) END ASC,

CASE WHEN @sortcol='Code' AND @sortorder = 'desc' THEN (ISNULL(S.IdentifierA,'')+ '-' + ISNULL(S.IdentifierB,'') + '-' + ISNULL(S.IdentifierC,'') + '-' + ISNULL(S.IdentifierD,'')+ '-' + ISNULL(S.IdentifierE,'')) END DESC,

CASE WHEN @sortcol='Dimension' AND @sortorder = 'asc' THEN (ISNULL(Height,'')+ 'x' + ISNULL(Width,'') + 'x' + ISNULL(Depth,'')) END ASC,

CASE WHEN @sortcol='Dimension' AND @sortorder = 'desc' THEN (ISNULL(Height,'')+ 'x' + ISNULL(Width,'') + 'x' + ISNULL(Depth,'')) END DESC

) AS rowid

, W.ID

, ST.Name As [Type]

, W.Name As Warehouse

,(ISNULL(S.IdentifierA,'')+ '-' + ISNULL(S.IdentifierB,'') + '-' + ISNULL(S.IdentifierC,'') + '-' + ISNULL(S.IdentifierD,'')+ '-' + ISNULL(S.IdentifierE,'')) AS Code

,MaxWeight

,S.Height

,S.Width

,S.Depth

,(ISNULL(Height,'')+ 'x' + ISNULL(Width,'') + 'x' + ISNULL(Depth,'')) As Dimension

from Storage S Left join Warehouse W on S.WarehouseID = W.ID left join StorageType ST on

S.StorageTypeID = ST.ID

group by S.ID

,W.ID

,ST.Name

,W.Name

,S.IdentifierA

,S.IdentifierB

,S.IdentifierC

,S.IdentifierD

,S.IdentifierE

,MaxWeight

,S.Height

,S.Width

,S.Depth

HAVING

W.Name LIKE '%' + @query + '%'

OR ST.Name LIKE '%' + @query + '%') as Listing

Select * from #PAGING where [email protected] or rowid [email protected]

SELECT @total = COUNT(*) from Storage S Left join Warehouse W on S.WarehouseID = W.ID left join StorageType ST on S.StorageTypeID= ST.ID

WHERE W.Name LIKE '%' + @query + '%'

OR ST.Name LIKE '%' + @query + '%'

drop table #PAGING

END

网友答案:

your isnulls should be floats not '' empty strings

(ISNULL(Height,0.0)+ 'x' + ISNULL(Width,0.0) + 'x' + ISNULL(Depth,0.0)) As Dimension

I would check your other isnulls as well, make sure they are returning the value type you want.

分享给朋友:
您可能感兴趣的文章:
随机阅读: