Converting from Varchar to bigint

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Converting from Varchar to bigint

Converting from Varchar to bigint

Rate Topic

Display Mode

Topic Options

Author

Message

Vinay Theethira

Posted Tuesday, May 05, 2009 8:41 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 29, Visits: 105

Hi All,
Here i am having a SP where i will be passing ContestID And ContestOptions(Which is ContestOptionIds).
The ContestOptions will be somethin like this.. 37|38|39

Since it is like that m passing it as Varchar,but after splitting those Ids i want it to be converted to bigint and then inserted.

Here is my SP,

ALTER PROCEDURE CreateCorrectOpt
@ContestId bigint,
@Options varchar(500)
AS
DECLARE @Totaloption int
SET @Totaloption = (SELECT COUNT(*) AS TotalOpt
FROM ContestCorrectOption
WHERE (ContestId = @ContestId))
IF @Totaloption < 1
BEGIN

DECLARE @OptionId bigint, @Pos int

SET @Options = LTRIM(RTRIM(@Options))+ '|'
SET @Pos = CHARINDEX('|', @Options, 1)

IF REPLACE(@Options, '|', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OptionId = -1
print ':'+ LTRIM(RTRIM(LEFT(@Options, @Pos - 1))) + ':'
SET @OptionId = CONVERT(bigint, LTRIM(RTRIM(LEFT(@Options, @Pos - 1))))

IF @OptionId <> -1
BEGIN
INSERT INTO ContestCorrectOption
(ContestId, ContestOptionId)
VALUES (@ContestId,@Options)
END
SET @Options = RIGHT(@Options, LEN(@Options) - @Pos)
SET @Pos = CHARINDEX('|', @Options, 1)
END
END
END

Post #710298

Florian Reischl

Posted Tuesday, May 05, 2009 3:10 PM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Hi

If you just want to know how to convert VARCHAR to BIGIN please have a look to BOL for "CONVERT function"

Greets
Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #710678

Giacomo Degli Esposti

Posted Wednesday, May 06, 2009 4:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 03, 2013 1:38 AM
Points: 36, Visits: 110

IF @OptionId <> -1
BEGIN
INSERT INTO ContestCorrectOption
(ContestId, ContestOptionId)
VALUES (@ContestId,@Options)
END

Maybe here you mistyped and wanted to insert @OptionID in stead of @Options ?

regards
Giacomo

Post #710919

« Prev Topic | Next Topic »

Permissions