June 17, 2015 at 10:31 am
The SPROC shown below is returning an existing record when it shouldn't be. So i tested the IF EXISTS statement by writing it out both without using variables and with. Either way I write the query when run, it does not return a record which is what should be happening inside the SPROC.
It is as if it is only looking at two columns SecAccount and Spare1, and then ignoring totally the AssessmentID and name columns.
Can anyone tell me why the SPROC is returning a record when it shouldn't be??
the stored procedure
CREATE PROCEDURE CheckEvalInfo
@ApplicantID varchar(20),
@FirstName varchar(20),
@LastName varchar(20),
@Version varchar(10),
@AssessmentID int,
@master varchar(6),
@Secondary varchar(6)
AS
SET NOCOUNT ON
DECLARE @RecordID bigint
DECLARE @Section char(1)
DECLARE @Completed bit
DECLARE @MyAssessmentID int
DECLARE @Len int
DECLARE @min-2 tinyint
DECLARE @Range tinyint
DECLARE @Exclude varchar(50)
DECLARE @Char char
DECLARE @Output varchar(50)
SET @Len = 12
SET @min-2 = 35
SET @Range = 74
SET @Exclude = '0:;`0l1.,&=-()[]?/\_'''
SET @Output = ''
--For use in the web service
IF EXISTS (SELECT RecordID FROM dbo.EvalData WHERE SecAccount = @Secondary AND FName = @FirstName AND LName = @LastName AND AssessmentID = @AssessmentID AND Spare1 = @ApplicantID )
BEGIN
SELECT
@RecordID = RecordID,
@Section = Completed,
@MyAssessmentID = AssessmentID,
@Output = HashKey
FROM
dbo.EvalData
WHERE
SecAccount = @Secondary
AND Spare1 = @ApplicantID
IF @Section = 'Y'
BEGIN
SET @Section = 0
SET @Completed = 1
END
ELSE
BEGIN
SET @Completed = 0
END
END
ELSE
BEGIN
WHILE @Len > 0
BEGIN
SELECT @Char = char(ROUND(RAND() * @Range + @min-2,0))
IF CHARINDEX(@Char,@Exclude) = 0
BEGIN
SET @Output = @Output + @Char
SET @Len = @Len -1
END
END
INSERT INTO
dbo.EvalData
(
MasterAccount,
SecAccount,
AssessDate,
Completed,
Version,
FName,
LName,
Gender,
Phone,
Email,
JobPosition,
Company,
[Password],
Spare1,
Spare2,
Spare3,
Viewed,
AssessmentID,
HideConviction,
Hashkey
)
VALUES
(
@Secondary,
GETDATE(),
'0',
@Version,
@FirstName,
@LastName,
'N',
'', -- Phone
'', -- Job Title
'Any Company',
'N/A',
@ApplicantID,
'Web Service',
'Web Service',
'N',
@AssessmentID,
0,
@Output
)
SET @RecordID = @@IDENTITY
SET @Section = 0
SET @Completed = 0
SET @MyAssessmentID = @AssessmentID
END
SELECT @RecordID AS RecordID, @Section AS SectionID, @Completed AS Completed, @MyAssessmentID AS AssessmentID, @Output AS HashKey
GO
The code written out which does return "no record" as it should be doing
DECLARE @Secondary varchar(6)
DECLARE @FirstName varchar(20)
DECLARE @LastName varchar(20)
DECLARE @AssessmentID int
DECLARE @ApplicantID varchar(20)
SET @Secondary = 'MIRNDA'
SET @FirstName = 'Miranda'
SET @LastName = 'Johnson'
SET @AssessmentID = 1
SET @ApplicantID = '1234'
If Exists(SELECT RecordID FROM dbo.EvalData WHERE SecAccount = @Secondary AND FName = @FirstName AND LName = @LastName AND AssessmentID = @AssessmentID AND Spare1 = @ApplicantID)
print 'Found It';
Else
print 'no record'
thanks for any help
June 17, 2015 at 11:23 am
Are you sure your predicates are identical in both IF clauses. In your test you set your parameters but I can't tell if you used those exact values in the SP.
Is this a typo? Where it should be MIRANDA. If it is then that might explain why you didn't find the record in your test. Just throwing it out there.
SET @Secondary = 'MIRNDA'
Otherwise nothing is jumping out at me.
June 17, 2015 at 11:33 am
Positive. The secondary account code is a 6 character code. it has nothing to do with a person's name i just happened to create one similar to my name for testing purposes.
I get an old existing record if i run the procedure like so
EXECUTE CheckEvalInfo '1234','Miranda','Johnson','ACHVR',1,'AT45AT','MIRNDA'
It should return a new recordid instead it is finding an old existing recordid under that secondary with the same value in spare1 (the applicantid) however with a different person's name listed and a different assessmentid
June 17, 2015 at 12:10 pm
Sorry the VARCHAR(6) should have been a giveaway.
Hmmm...with the IF statement being Identical it is puzzling but without any more info I'm not sure.
Just another shot in the dark here but are you running both the procedure and query against the same DB? Just wondering if one is pointing to a PROD DB while the other is pointing to a DEV copy.
June 17, 2015 at 12:32 pm
yep both against the same db
I originally had this in the Exists statement in the sproc.
IF EXISTS(SELECT RecordID FROM dbo.EvalData WHERE SecAccount=@Secondary AND Spare1 = @ApplicantID )
After I noticed that it was returning an old record I added the other fields, I first altered the existing procedure tried it and then I dropped the old one and recreated with the same name but the changes
it still did not work.
I just tried again and now it is working correctly. Does it take a while for SQL Server 2005 to propogate the change to sprocs? most of the databases here at work are still on SQL Server 2000
June 17, 2015 at 12:34 pm
I see at least two big red flags in your code. The first one is that while loop. What is it doing? If I read it correctly it is simply stripping out all characters in your @Exclude variable?
The second red flag is @@IDENTITY. You should not be using that here since it seems like you want the value of the identity inserted into EvalData. If that table has a trigger that does an insert you will get that value. Instead you should use SCOPE_IDENTITY to get the last inserted value within the current scope.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2015 at 12:41 pm
the while loop creates the 12 digit random character string subtracting 1 from the
@len variable in each iteration until it has all 12 characters
I will have to look into the use of SCOPE_IDENTITY
June 17, 2015 at 1:23 pm
Miranda Johnson (6/17/2015)
the while loop creates the 12 digit random character string subtracting 1 from the@len variable in each iteration until it has all 12 characters
I will have to look into the use of SCOPE_IDENTITY
Ahh yes I see it now. You could do this with a tally table instead of a loop. I have a view on my system that is my tally table.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
GO
This has millions of uses and has been referred to around here as "the swiss army knife of t-sql". You can read more about tally tables and there uses here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
If you are interested in using a tally table instead of a loop for this it might look something like this.
declare @n varchar(64)
DECLARE @Len int
SET @Len = 12
set @n='';
with RandomChars as
(
select top(@Len) char(n) as RandChar
from cteTally
where CHAR(n) not IN ('0', ':', ';', '`', '0', 'l', '1', '.', ',', '&', '=', '-', '(', ')', '[', ']', '?', '/', '\', '_', '''')
AND N > 32
AND N <= 122
order by NEWID()
)
select @n = @n + RandChar
from RandomChars
select @n
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply