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+']'
