?
Solved

The query sometimes works correctly< sometimes not

Posted on 2008-10-07
26
Medium Priority
?
200 Views
Last Modified: 2013-11-05
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?
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

Open in new window

0
Comment
Question by:exceter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
26 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666525
replace:
      IF @code=(SELECT code FROM prize_codes WHERE code=@code)
by:

      IF EXISTS( SELECT code FROM prize_codes WHERE code=@code)
0
 
LVL 3

Author Comment

by:exceter
ID: 22666618
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666630
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?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 3

Author Comment

by:exceter
ID: 22666674
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,@length) FROM Anekdots WHERE id = @Random)
                  SET @RetText =  'Kod otpravljat ne bolee 1 raz.' +CHAR(13)+ @str2

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666752
ok:
@code char(6),

what is the data type in the table? is it also char(6)? or varchar(...) ?
0
 
LVL 3

Author Comment

by:exceter
ID: 22666793
hmm, it is NCHAR(10)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22666827
that is the problem, then.
please use NVARCHAR / VARCHAR instead of NCHAR/CHAR, as CHAR data types do right-padding with spaces.
0
 
LVL 3

Author Comment

by:exceter
ID: 22667042
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.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22667065
could you add the SET NOCOUNT ON as the first line of the procedure?
maybe that's the problem of "sometimes" not getting the results?
0
 
LVL 3

Author Comment

by:exceter
ID: 22667096
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22667116
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-')
0
 
LVL 3

Author Comment

by:exceter
ID: 22667153
look at above lines, it cant be NULL:
...
            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  
...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22667165
>look at above lines, it cant be NULL:
i have seen those lines, and it still could be NULL if the id=@random does not return a line, for example ...

as I don't know the context, I can't be sure that the value generated into @random is indeed found in that table...
0
 
LVL 3

Author Comment

by:exceter
ID: 22667221
yep, great help man.
unfortunately I found there some ID's which return NULL values.

THANK YOU.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22667238
glad I could help +++
maybe we reopen the question to accept the "right" answer?
angel eyes
0
 
LVL 3

Author Comment

by:exceter
ID: 22667294
if that is possible, I would reaccept the "right" answer with a great pleasure.
How to do that? I could not find..
0
 
LVL 3

Author Closing Comment

by:exceter
ID: 31504117
Excellent help +++
0
 
LVL 3

Author Comment

by:exceter
ID: 22675978
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 :(
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22676150
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?
0
 
LVL 3

Author Comment

by:exceter
ID: 22676174
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]

Open in new window

0
 
LVL 3

Author Comment

by:exceter
ID: 22676183
insert statements are in procedure
0
 
LVL 3

Author Comment

by:exceter
ID: 22676205
one more table:
CREATE TABLE [dbo].[Anekdots](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Anekdot] [nvarchar](130) NOT NULL
) ON [PRIMARY]

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22677158
but there must be some starter data in the tables, no?
what parameter values will I have to test with?
0
 
LVL 3

Author Comment

by:exceter
ID: 22677418
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22677458
ok!
wish you a nice future!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question