exceter
asked on
The query sometimes works correctly< sometimes not
Hi,
the following code does not work correctly, that is, sometimes works as needed(correctly), sometimes gives incorrect query results.
The Query [ IF @code=(SELECT code FROM prize_codes WHERE code=@code) ] may say that there is a code in table, or not, even if there exists.
What is wrong? something with DB or the OS? or my query?
the following code does not work correctly, that is, sometimes works as needed(correctly), sometimes gives incorrect query results.
The Query [ IF @code=(SELECT code FROM prize_codes WHERE code=@code) ] may say that there is a code in table, or not, even if there exists.
What is wrong? something with DB or the OS? or my query?
USE [textsms]
GO
/****** Object: StoredProcedure [dbo].[sp_getSMvalue] Script Date: 10/08/2008 10:52:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_getSMvalue]
@sender char(12),
@code char(6),
@RetText nchar(158) OUTPUT
AS
DECLARE
@maxVal int, @cVal int, @codeID int, @tmp int, @winner bit,
@Upper int, @Random int, @repeatingCode bit, @repCnt int, @str1 nchar(150),
@str2 nchar(150), @strPrize nchar(20),@length int
SET @Upper = (SELECT MAX(id) FROM Anekdots)
--SET @Random = ROUND(((@Upper -1) * RAND() + 1), 0)
SET @Random = 1 + CONVERT(INT, (@Upper)*RAND())
SET @repCnt = (SELECT COUNT(code) FROM prize_sender WHERE code=(SELECT code FROM prize_codes WHERE code=@code ))
SET @tmp = (SELECT val FROM sm_value WHERE id=1)
IF (@tmp < (SELECT max_val FROM sm_value WHERE id=1))
SET @winner = 0
ELSE SET @winner = 1
IF @winner = 0 -- A;8 =5 ?>1548B5;L
BEGIN
IF @code=(SELECT code FROM prize_codes WHERE code=@code)
-- A;8 ?>;CG5= ?@028;L=K9 :>4, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, C25;8G8BL 7=0G5=85 VAL
BEGIN
SET @codeID=(SELECT id FROM prize_codes WHERE code=@code)
INSERT INTO prize_sender(sender, code, prizeCode_id) VALUES(@sender, @code, @codeID)
-- @83>B>28BL B5:AB >B25B=>3> !!. !! A 0=5:4>B><
IF (@repCnt > 0)
BEGIN
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
SET @RetText = 'Kod otpravljat ne bolee 1 raz.' +CHAR(13)+ @str2
END
ELSE BEGIN
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
SET @RetText = 'Spasibo za SMS. '+@str2
UPDATE sm_value
SET val = val + 1,
total_sms = total_sms + 1
END
END
ELSE -- A;8 :>4 =5?@028;L=K9, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, => =5 C25;8G820BL 7=0G5=85 VAL
BEGIN
INSERT INTO prize_sender(sender, code) VALUES(@sender, @code)
-- @83>B>28BL B5:AB >B25B=>3> !!. !! A 0=5:4>B><
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
SET @RetText = 'Kod nepravilni. ' +@str2
END
END
ELSE -- A;8 ?>1548B5;L
BEGIN
IF @code=(SELECT code FROM prize_codes WHERE code=@code)
-- A;8 ?>;CG5= ?@028;L=K9 :>4, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, C25;8G8BL 7=0G5=85 VAL
BEGIN
SET @codeID=(SELECT id FROM prize_codes WHERE code=@code)
INSERT INTO prize_sender(sender, code, prizeCode_id) VALUES(@sender, @code, @codeID)
IF (@repCnt > 0)
BEGIN
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
SET @RetText = 'Kod otpravljat ne bolee 1 raz. ' + @str2
END
ELSE BEGIN
SET @Upper = (SELECT MAX(id) FROM Prize WHERE Total>0)
SET @Random = ROUND(((@Upper -1) * RAND() + 1), 0)
SET @RetText = 'Vy viigrali priz-'+ ' ' +(SELECT name FROM prize WHERE id=@Random)-- +'. Prosba szjazatsja po nomeru '
UPDATE prize
SET total = total - 1 WHERE id=@Random
UPDATE sm_value
SET val = 0,
cnt_prize = cnt_prize + 1,
total_sms = total_sms + 1
--WHERE id = 1;
END
END
ELSE -- A;8 :>4 =5?@028;L=K9, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, => =5 C25;8G820BL 7=0G5=85 VAL
BEGIN
INSERT INTO prize_sender(sender, code) VALUES(@sender, @code)
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@length) FROM Anekdots WHERE id = @Random)
SET @RetText = 'Kod nepravilni. '+@str2
END
END
ASKER
angelIII
Why
IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
is better than
IF @code=(SELECT code FROM prize_codes WHERE code=@code)
???
what is the difference? I made those changes, testing now
Why
IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
is better than
IF @code=(SELECT code FROM prize_codes WHERE code=@code)
???
what is the difference? I made those changes, testing now
actually, the question is: can @code by NULL or not?
and in case @code is null is there a row in prize_codes with NULL also?
and in case @code is null is there a row in prize_codes with NULL also?
ASKER
No, @code cant be NULL. it must exist.
Look here:
IF @winner = 0 -- A;8 =5 ?>1548B5;L
BEGIN
IF @code=(SELECT code FROM prize_codes WHERE code=@code)
sometimes returns that the @code does not exist, and does following:
ELSE -- A;8 :>4 =5?@028;L=K9, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, => =5 C25;8G820BL 7=0G5=85 VAL
BEGIN
INSERT INTO prize_sender(sender, code) VALUES(@sender, @code)
and if query the same @code value, it does following:
IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
--IF @code=(SELECT code FROM prize_codes WHERE code=@code)
-- A;8 ?>;CG5= ?@028;L=K9 :>4, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, C25;8G8BL 7=0G5=85 VAL
BEGIN
SET @codeID=(SELECT id FROM prize_codes WHERE code=@code)
INSERT INTO prize_sender(sender, code, prizeCode_id) VALUES(@sender, @code, @codeID)
-- @83>B>28BL B5:AB >B25B=>3> !!. !! A 0=5:4>B><
IF (@repCnt > 0)
BEGIN
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@lengt h) FROM Anekdots WHERE id = @Random)
SET @RetText = 'Kod otpravljat ne bolee 1 raz.' +CHAR(13)+ @str2
Look here:
IF @winner = 0 -- A;8 =5 ?>1548B5;L
BEGIN
IF @code=(SELECT code FROM prize_codes WHERE code=@code)
sometimes returns that the @code does not exist, and does following:
ELSE -- A;8 :>4 =5?@028;L=K9, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, => =5 C25;8G820BL 7=0G5=85 VAL
BEGIN
INSERT INTO prize_sender(sender, code) VALUES(@sender, @code)
and if query the same @code value, it does following:
IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
--IF @code=(SELECT code FROM prize_codes WHERE code=@code)
-- A;8 ?>;CG5= ?@028;L=K9 :>4, 225AB8 2 B01;8FC PRIZE_SENDER 40==K5, C25;8G8BL 7=0G5=85 VAL
BEGIN
SET @codeID=(SELECT id FROM prize_codes WHERE code=@code)
INSERT INTO prize_sender(sender, code, prizeCode_id) VALUES(@sender, @code, @codeID)
-- @83>B>28BL B5:AB >B25B=>3> !!. !! A 0=5:4>B><
IF (@repCnt > 0)
BEGIN
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@lengt
SET @RetText = 'Kod otpravljat ne bolee 1 raz.' +CHAR(13)+ @str2
ok:
@code char(6),
what is the data type in the table? is it also char(6)? or varchar(...) ?
@code char(6),
what is the data type in the table? is it also char(6)? or varchar(...) ?
ASKER
hmm, it is NCHAR(10)
that is the problem, then.
please use NVARCHAR / VARCHAR instead of NCHAR/CHAR, as CHAR data types do right-padding with spaces.
please use NVARCHAR / VARCHAR instead of NCHAR/CHAR, as CHAR data types do right-padding with spaces.
ASKER
I think that the problem is in my PERL.
I call this procedure there, and sometimes it returns empty value. But the procedure itself should return always non-empty value.
I call this procedure there, and sometimes it returns empty value. But the procedure itself should return always non-empty value.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What is the help of that?
I am now sure that the problem is in my PERL code. It time by time looses connection to DB.
I am now sure that the problem is in my PERL code. It time by time looses connection to DB.
sorry, you are right, as you work with OUTPUT parameter...
question:
SET @RetText = 'Kod nepravilni. '+@str2
is @str2 non-null in all the cases?
you might try to ensure it is not:
SET @RetText = 'Kod nepravilni. '+ ISNULL(@str2 , '-n/a-')
question:
SET @RetText = 'Kod nepravilni. '+@str2
is @str2 non-null in all the cases?
you might try to ensure it is not:
SET @RetText = 'Kod nepravilni. '+ ISNULL(@str2 , '-n/a-')
ASKER
look at above lines, it cant be NULL:
...
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@lengt h) FROM Anekdots WHERE id = @Random)
SET @RetText = 'Kod nepravilni. '+@str2
...
...
SET @length = (SELECT LEN(Anekdot) FROM Anekdots WHERE id = @Random)
SET @str2 = (SELECT substring(Anekdot,1,@lengt
SET @RetText = 'Kod nepravilni. '+@str2
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yep, great help man.
unfortunately I found there some ID's which return NULL values.
THANK YOU.
unfortunately I found there some ID's which return NULL values.
THANK YOU.
glad I could help +++
maybe we reopen the question to accept the "right" answer?
angel eyes
maybe we reopen the question to accept the "right" answer?
angel eyes
ASKER
if that is possible, I would reaccept the "right" answer with a great pleasure.
How to do that? I could not find..
How to do that? I could not find..
ASKER
Excellent help +++
ASKER
I am sad.
I continue getting wrong query results. that the code does not exist.
I did changes that you suggested, but the problem still there :(
I continue getting wrong query results. that the code does not exist.
I did changes that you suggested, but the problem still there :(
can you post the CREATE TABLE and the INSERT statements for the sample data, so I could try to reproduce the problem on my side?
ASKER
my tables are:
CREATE TABLE [dbo].[prize_codes](
[id] [int] IDENTITY(1,1) NOT NULL,
[code] [nvarchar](6) NULL,
[name] [nvarchar](50) NULL,
[type] [nvarchar](50) NULL,
[datetime] [datetime] NULL CONSTRAINT [DF_prize_codes_datetime] DEFAULT (getdate()),
CONSTRAINT [PK_prize_codes] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[prize_sender](
[id] [int] IDENTITY(1,1) NOT NULL,
[sender] [nvarchar](50) NULL,
[code] [nvarchar](50) NULL,
[prizeCode_id] [int] NULL,
[winner] [bit] NULL CONSTRAINT [DF_prize_sender_winner] DEFAULT ((0)),
[prize_id] [int] NULL,
[datetime] [datetime] NULL CONSTRAINT [DF_prize_sender_datetime] DEFAULT (getdate()),
CONSTRAINT [PK_prize_sender] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[prize](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](20) NULL,
[Total] [int] NULL,
[datetime] [datetime] NULL CONSTRAINT [DF_prize_datetime] DEFAULT (getdate()),
CONSTRAINT [PK_prize] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ASKER
insert statements are in procedure
ASKER
one more table:
CREATE TABLE [dbo].[Anekdots](
[id] [int] IDENTITY(1,1) NOT NULL,
[Anekdot] [nvarchar](130) NOT NULL
) ON [PRIMARY]
but there must be some starter data in the tables, no?
what parameter values will I have to test with?
what parameter values will I have to test with?
ASKER
sorry, at the moment I have no access to DB.
:( and will be in 10 days.
I am going to marry in a few days.
:( and will be in 10 days.
I am going to marry in a few days.
ok!
wish you a nice future!
wish you a nice future!
IF @code=(SELECT code FROM prize_codes WHERE code=@code)
by:
IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)