My SP works on 2005 bit not on 2000 it doesn''t step through my code.

Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.

Code Snippet

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

-My Code Stops here

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

Any help would be greatly appreciated

Kind Regards

Carel greaves

Well, without knowing where you get the values of @.clientId or what the value was, there is no telling. No where in your code does the ClientId get set.

Can you add some print statements to your code to show what the values are? Preferrably before and after the IF statements, so you can see the value, and then if it actually got inside the block.

|||What do you mean "My Code Stops Here"? Are you getting an error? Or is it just not matching any of your IF statements as you expect?

Without seeing the entire SP and sample data, there is no telling what is causing your problem.

|||

Hi sorry, here is the whole procedure. Whe iu said my code stops here, it doesn't give me an error, it means that it doesn't run through the if statements, so it inserts the member's details into my members table and then it stops. That's all, doesn't look like it even touches my IF statements, and it doesn't give me any error codes.

Sorry about before, i was more confused, so i didn't know what data to put on the forum for help.

Here is all the code for my SP.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[csp_MemberUploader]

AS

DECLARE @.CurrentValue INT

DECLARE @.numValues INT

DECLARE @.MaxValue INT

SELECT @.numValues = COUNT(ID), @.MaxValue = MAX(ID)

FROM StageMemberUploading

WHILE @.numValues <> 0

BEGIN

DECLARE @.DateOfBirth DATETIME

DECLARE @.Male VARCHAR(50)

DECLARE @.Female VARCHAR(50)

DECLARE @.Single VARCHAR(50)

DECLARE @.Married VARCHAR(50)

DECLARE @.Divorced VARCHAR(50)

DECLARE @.Widowed VARCHAR(50)

DECLARE @.Height VARCHAR(50)

DECLARE @.Weight VARCHAR(50)

DECLARE @.Absentism VARCHAR(50)

DECLARE @.UsergroupID INT

DECLARE @.ClientID INT

SELECT @.DateOfBirth = [Date of birth],

@.Male = [Male],

@.Female = [Female],

@.Single = [Single],

@.Married = [Married],

@.Divorced = [Divorced],

@.Widowed = [Widowed],

@.Height = [Height],

@.Weight = [Weight],

@.Absentism = [Absentism],

@.UsergroupID = UsergroupID,

@.ClientID = ClientID

FROM StageMemberUploading

WHERE ID = @.numValues

INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)

SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]

FROM StageMemberUploading

WHERE ID = @.numValues

SET @.CurrentValue = (SELECT SCOPE_IDENTITY())

IF @.ClientID IS NOT NULL BEGIN

INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)

VALUES (@.CurrentValue, @.ClientID, @.UsergroupID)

END

IF @.DateOfBirth IS NOT NULL BEGIN

INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 1, @.DateOfBirth)

END

IF @.Male = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 1)

END

IF @.Female = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 2, 3)

END

IF @.Single = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 8)

END

IF @.Married = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 7)

END

IF @.Divorced = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 9)

END

IF @.Widowed = 'x' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 6, 10)

END

IF @.Height = '1.22' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 3, 85)

END

IF @.Weight = 'x' BEGIN

INSERT INTO _MemberProfileNumericValues (MemberID, OptionID, OptionValue)

VALUES (@.CurrentValue, 4, @.Weight)

END

IF @.Absentism = '1' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 158)

END

IF @.Absentism = '2' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 159)

END

IF @.Absentism = '3' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 160)

END

IF @.Absentism = '4' BEGIN

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

VALUES (@.CurrentValue, 15, 161)

END

SET @.numValues = @.numValues - 1

END

Kind Regards

Carel Greaves

|||

As far as i can see, your proc looks fine and there aren't any glaring errors. Which leads me to believe its the data that is causing the problems.

I'd definitely follow Louis' advice and add print statements before you go into the IF blocks so you can check the data against that of the table.

PRINT @.NumValues

PRINT @.CurrentValue

PRINT @.Absentism

....

|||My first guess is your values for @.Male, @.Female, etc are NULL.

Add some commands like this and see what you get:

IF @.Male IS NULL
PRINT '@.Male is NULL'
ELSE
PRINT '@.Male=['+@.Male+']'