November 24, 2008 at 10:26 pm
Hi, I have written an Stored Procedure and have problem for converting varchar Type to bigint, for making it more clearer I'll write the codes that I have problem with it :
declare @CurrentDate as char(8)
declare @FromDate as char(8)
declare @ToDate as char(8)
declare @InShMainNo as varchar(1000)
declare @AssignedVoucherNo as bigint --- is an output variable in my sp
declare @Result as bit ---- is an output variable in my stored procedure
set @CurrentDate = '13870904'
set @FromDate = '13870730'
set @ToDate = '13870730'
set @InShMainNo = '1404,2'
--------
Declare @s-2 as varchar(1000)
--------
declare @VoucherNO as bigint
declare @i as int
declare @Count as bigint
declare @SPSubAccDebit as varchar(50)
declare @SPUnqAccDebit as varchar(50)
declare @SPSubAccCredit as varchar(50)
declare @SPUnqAccCredit as varchar(50)
set @s-2 = (case when len(@InShMainNo)>1 then substring(@InShMainNo,1,len(@InShMainNo)) else @InShMainNo end)
set @Count = (select Count(*) as Counts from SharePayment
where ((PayDate >= @FromDate) and (PayDate 5) and (@InShMainNo = ''))
or
((PayDate >= @FromDate) and (PayDate '') and (SharePayment.ShMainNo in (@InShMainNo)))
)
-----
The variables that I have declared and then I have set them are the parameters of the Stored procedure.
The problem is that there is a field in the SharePayment table that it's name is ShMainNo with bigint type.
I have declared a variable @InShMainNo that we can pass the value to in three ways :
1. ''
2. a bigint number such as 2 or 3 , or 123
3. a combination of different numbers such as 1,2,10.123,500,...
because of that I have declared the @InShMainNo as Varchar(1000) but the problem is whenever I want to pass Combination of different numbers
an error will occure telling than : Server: Msg 8114, Level 16, State 5, Line 27 Error converting data type varchar to bigint.
I don't with what kind of trick I can pass them without any error.
Is there any way ?
November 24, 2008 at 11:09 pm
Everything you posted may, in your mind be helpful, but we also need to see your stored procedure.
November 24, 2008 at 11:31 pm
golbano0o (11/24/2008)
Hi, I have written an Stored Procedure and have problem for converting varchar Type to bigint, for making it more clearer I'll write the codes that I have problem with it :declare @CurrentDate as char(8)
declare @FromDate as char(8)
declare @ToDate as char(8)
declare @InShMainNo as varchar(1000)
declare @AssignedVoucherNo as bigint --- is an output variable in my sp
declare @Result as bit ---- is an output variable in my stored procedure
set @CurrentDate = '13870904'
set @FromDate = '13870730'
set @ToDate = '13870730'
set @InShMainNo = '1404,2'
--------
Declare @s-2 as varchar(1000)
--------
declare @VoucherNO as bigint
declare @i as int
declare @Count as bigint
declare @SPSubAccDebit as varchar(50)
declare @SPUnqAccDebit as varchar(50)
declare @SPSubAccCredit as varchar(50)
declare @SPUnqAccCredit as varchar(50)
set @s-2 = (case when len(@InShMainNo)>1 then substring(@InShMainNo,1,len(@InShMainNo)) else @InShMainNo end)
set @Count = (select Count(*) as Counts from SharePayment
where ((PayDate >= @FromDate) and (PayDate 5) and (@InShMainNo = ''))
or
((PayDate >= @FromDate) and (PayDate '') and (SharePayment.ShMainNo in (@InShMainNo)))
)
-----
The variables that I have declared and then I have set them are the parameters of the Stored procedure.
The problem is that there is a field in the SharePayment table that it's name is ShMainNo with bigint type.
I have declared a variable @InShMainNo that we can pass the value to in three ways :
1. ''
2. a bigint number such as 2 or 3 , or 123
3. a combination of different numbers such as 1,2,10.123,500,...
because of that I have declared the @InShMainNo as Varchar(1000) but the problem is whenever I want to pass Combination of different numbers
an error will occure telling than : Server: Msg 8114, Level 16, State 5, Line 27 Error converting data type varchar to bigint.
I don't with what kind of trick I can pass them without any error.
Is there any way ?
use QUOTENAME infront of ShMainNo in you query:
Below Query may help to you.
select'SELECT'
+QUOTENAME (OrderID, '''') + ', '
+CAST (ShipVia as VARCHAR) + ', '
+QUOTENAME (Freight, '''')
+
+' UNION ALL '
fromnorthwind.dbo.Orders
December 1, 2008 at 8:06 am
Are you converting different numbers like this?
1,2,3,4
Those are characters if you delimit them. SQL doesn't recognize arrays or delimited strings as a set of numbers. It's a character string.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply