| Author |
 Topic  |
|
|
hirani_prashant
Yak Posting Veteran
93 Posts |
 Posted - 05/16/2008 : 08:53:33
|
Hi,
how to convert nvarchar datatype to float?
Regards
Prashant |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
|
|
hirani_prashant
Yak Posting Veteran
93 Posts |
Posted - 05/16/2008 : 09:08:35
|
Thanks for the reply....
I have tried my best bt didn't find any way to do it...
Please see the below procedure...
USE [TradeCapture]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
GO
IF (EXISTS (SELECT name FROM SYS.sysobjects
WHERE (name = N'Proc_Submit_PITS_Order_List')
AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN'))))
DROP PROCEDURE [dbo].[Proc_Submit_PITS_Order_List]
GO
-- =================================================================================================================
-- Author: Prashant Hirani
-- Create date: May 13, 2008
-- =================================================================================================================
-- EXEC Proc_Submit_PITS_Order_List ' 0,0,0,0, 55.55, 20, 'B'' , 0,0 ,0, 60.00, 15, 'B'
CREATE PROCEDURE [dbo].[Proc_Submit_PITS_Order_List]
@P_PitId nvarchar(4000),
@P_OrderId nvarchar(4000),
--@P_OrderDate DATETIME,
@P_Cust_id INT,
@P_Trader_Id INT,
@P_Price nvarchar(4000),
@P_Size nvarchar(4000),
@P_Buysell NVARCHAR(10)
--@p_TradeVision_List nvarchar(4000)
AS
BEGIN
-- =================================================================================================================
SET NOCOUNT ON
--DECLARE @P_TRADEVISION_ID INT
DECLARE @p_Cnt_Duplicate_Rec INT
DECLARE @p_Max_Version INT
DECLARE @p_Status NVARCHAR(50)
DECLARE @tbl table
(
PitId int,
OrderId int,
Price Float,
[Size] Float
)
DECLARE @p_Tmp_Str1 nvarchar(4000)
DECLARE @p_Tmp_Str2 nvarchar(4000)
DECLARE @p_Tmp_Str3 nvarchar(4000)
DECLARE @p_Tmp_Str4 nvarchar(4000)
Set @p_Tmp_Str1 = NULL
Set @p_Tmp_Str2 = NULL
Set @p_Tmp_Str3 = NULL
Set @p_Tmp_Str4 = NULL
While IsNull(Len(@P_PitId),0) > 0
BEGIN
IF IsNull(Len(@P_PitId),0) <= 0 BREAK
If IsNull(charindex(',',@P_PitId),0) > 0
Set @p_Tmp_Str1 = (select left(@P_PitId,IsNull(charindex(',',@P_PitId) - 1,0)))
ELSE
Set @p_Tmp_Str1 = @P_PitId
If IsNull(charindex(',',@P_OrderId),0) > 0
Set @p_Tmp_Str2 = (select left(@P_OrderId,IsNull(charindex(',',@P_OrderId) - 1,0)))
ELSE
Set @p_Tmp_Str2 = @P_OrderId
If IsNull(charindex(',',@P_Price),0) > 0
Set @p_Tmp_Str3 = (select left(@P_Price,IsNull(charindex(',',@P_Size) - 1,0)))
ELSE
Set @p_Tmp_Str3 = @P_Price
If IsNull(charindex(',',@P_Size),0) > 0
Set @p_Tmp_Str4 = (select left(@P_Size,IsNull(charindex(',',@P_Size) - 1,0)))
ELSE
Set @p_Tmp_Str4 = @P_Size
If (IsNumeric(@p_Tmp_Str1)= 1) AND
(IsNumeric(@p_Tmp_Str2)= 1) AND
(IsNumeric(@p_Tmp_Str3)= 1) AND
(IsNumeric(@p_Tmp_Str4))= 1
Begin
Insert Into @tbl
Values( Convert(INT, @p_Tmp_Str1), Convert(INT, @p_Tmp_Str2), Convert(float, @p_Tmp_Str3), Convert(float, @p_Tmp_Str4))
END
------ --------------
If IsNull(Len(@P_PitId),0) > IsNull(Len(@p_Tmp_Str1),0)
Set @P_PitId = (select right(@P_PitId, IsNull(Len(@P_PitId),0) - IsNull(Len(@p_Tmp_Str1) + 1,0)))
Else
Set @P_PitId = null
--------------------
If IsNull(Len(@P_OrderId),0) > IsNull(Len(@p_Tmp_Str2),0)
Set @P_OrderId = (select right(@P_OrderId, IsNull(Len(@P_OrderId),0) - IsNull(Len(@p_Tmp_Str2) + 1,0)))
Else
Set @P_OrderId = null
--------------------
If IsNull(Len(@P_Price),0) > IsNull(Len(@p_Tmp_Str3),0)
Set @P_Price = (select right(@P_Price, IsNull(Len(@P_Price),0) - IsNull(Len(@p_Tmp_Str3) + 1,0)))
Else
Set @P_Price = null
--------------------
If IsNull(Len(@P_Size),0) > IsNull(Len(@p_Tmp_Str4),0)
Set @P_Size = (select right(@P_Size, IsNull(Len(@P_Size),0) - IsNull(Len(@p_Tmp_Str4) + 1,0)))
Else
Set @P_Size = null
END
SELECT * FROM @tbl
-----------------------------------------------------
Try to execute with the below parameter
EXEC Proc_Submit_PITS_Order_List '116661,116662,166610', '154443,144454, 154448', 0, 0, '5555, 6546, 7457', '5558, 5659, 9599', 'B'
Before knowing from the opposite site never give advice to anyone....
Thanks
Prashant Hirani
|
Edited by - hirani_prashant on 05/16/2008 09:12:48 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 05/16/2008 : 10:44:54
|
| What was the problem when you used convert? DId you get any error? |
|
|
|
hirani_prashant
Yak Posting Veteran
93 Posts |
Posted - 05/17/2008 : 01:06:19
|
Ya, it gives me error like "Conversion failed when converting the nvarchar value to float"
Thanks
Prashant |
|
|
|
hirani_prashant
Yak Posting Veteran
93 Posts |
Posted - 05/17/2008 : 03:32:47
|
Now let me explain with the simple terminology...
Mine Below code works fine...
declare @v1 as nvarchar
set @v1='99922.6'
select cast(@v1 as int)
select cast(@v1 as money)
SELECT CONVERT(int, @v1)
SELECT CONVERT(money, @v1)
Bt at the same time mine below code gives me error...
declare @v1 as nvarchar(4000)
set @v1='99922.6'
select cast(@v1 as int)
select cast(@v1 as money)
SELECT CONVERT(int, @v1)
SELECT CONVERT(money, @v1)
Can any one help me out to solve this problem???
Thanks
Prashant
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 05/17/2008 : 04:47:29
|
do a PRINT @v1
and you will spot the error...
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
ganeshkumar08
Posting Yak Master
India
187 Posts |
Posted - 05/17/2008 : 05:30:27
|
declare @v1 as nvarchar(4000)
set @v1='99922.6'
select cast(@v1 as float)
The above is working in converting nvarchar to float, then wats your problem.. |
|
|
|
chandan_joshi80
Starting Member
India
30 Posts |
Posted - 05/17/2008 : 08:54:23
|
DECLARE @V1 NVARCHAR(20)
SET @V1='232'
PRINT @V1
SELECT CAST(@V1 AS NUMERIC(18,2))
chandan Joshi |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 05/17/2008 : 13:56:43
|
And Chandan wins the cheese.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
hirani_prashant
Yak Posting Veteran
93 Posts |
Posted - 05/19/2008 : 01:22:09
|
It was my mistake inside the below if loop.
There i mention the wrong parameter "@P_Size". In Actual i have mention "@P_Price" Parameter...
If IsNull(charindex(',',@P_Price),0) > 0
Set @p_Tmp_Str3 = (select left(@P_Price,IsNull(charindex(',',@P_Size) - 1,0)))
ELSE
Set @p_Tmp_Str3 = @P_Price
Any ways thanks for your valuable response.
Thanks
Prashant Hirani |
|
|
| |
Topic |
|
|
|
how to convert nvarchar datatype to float
