May 5, 2009 at 8:41 am
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
May 5, 2009 at 3:10 pm
Hi
If you just want to know how to convert VARCHAR to BIGIN please have a look to BOL for "CONVERT function"
Greets
Flo
May 6, 2009 at 4:43 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply