Solved

The query sometimes works correctly< sometimes not

Posted on 2008-10-07
26
197 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CREATE DATABASE ENCRYPTION KEY 1 82
how to eliminate duplicates in a string variable in t-sql? 30 89
Help Required 2 56
Format Date fields 11 64
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

732 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