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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy