tiwas 0 Light Poster

I have the following two sprocs:

USE [Ecotech]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Ecotech_AddUser]
@aspnet_id uniqueidentifier,
     @last_name nvarchar(255),
     @first_name nvarchar(255),
     @address1 nvarchar(255),
     @address2 nvarchar(255), 
     @zip nvarchar(10),
     @city nvarchar(255),
     @tlf_mobile nvarchar(16),
     @tlf_home nvarchar(16),
     @tlf_office nvarchar(16),
     @country nvarchar(16),
     @email nvarchar(50),
     @status int output
AS
BEGIN
    SET NOCOUNT ON;

    if (select COUNT(*) from ecotech_users where aspnet_id = @aspnet_id) > 0
        begin
            set @status = 0
        end
    else
        begin
            insert 
            into ecotech_users 
                (aspnet_id, 
                 last_name, 
                 first_name, 
                 address1, 
                 address2, 
                 zip, 
                 city, 
                 tlf_mobile, 
                 tlf_home, 
                 tlf_office, 
                 country,
                 email)
            values 
                (@aspnet_id,
                 @last_name,
                 @first_name,
                 @address1,
                 @address2, 
                 @zip,
                 @city,
                 @tlf_mobile,
                 @tlf_home,
                 @tlf_office,
                 @country,
                 @email)
                 
                 set @status = 1
         end
END

(populates my extensions to asp.net's user tables)

USE [Ecotech]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Ecotech_ModifyUser]
    -- Add the parameters for the stored procedure here
@aspnet_id uniqueidentifier,
     @last_name nvarchar(255),
     @first_name nvarchar(255),
     @address1 nvarchar(255),
     @address2 nvarchar(255), 
     @zip nvarchar(10),
     @city nvarchar(255),
     @tlf_mobile nvarchar(16),
     @tlf_home nvarchar(16),
     @tlf_office nvarchar(16),
     @country nvarchar(16),
     @email nvarchar(50),
     @status int output
AS
BEGIN
    SET NOCOUNT ON;
DECLARE    @return_value int
    if (select COUNT(*) from ecotech_users where aspnet_id = @aspnet_id) > 0
        begin
            update ecotech_users 
            set
                 last_name=@last_name, 
                 first_name=@first_name, 
                 address1=@address1, 
                 address2=@address2, 
                 zip=@zip, 
                 city=@city, 
                 tlf_mobile=@tlf_mobile, 
                 tlf_home=@tlf_home, 
                 tlf_office=@tlf_office, 
                 country=@country,
                 email=@email
            where     aspnet_id=@aspnet_id 
            set @status = 1;
        end
    else
        begin
            if (select COUNT(*) from aspnet_Users where UserId = @aspnet_id) > 0
                begin
                    exec @return_value = Ecotech_AddUser  @aspnet_id, @last_name, @first_name, @address1, @address2, 
                        @zip, @city, @tlf_mobile, @tlf_home, @tlf_office, @country, @email, @status
                end
            else
                begin
                    set @status = 0
                end    
        end
END

(modifies my extension table, but creates the extensions for a user if this does not already exist)

The adduser sproc works as it should. The modifyuser does everything correct, except for returning the status it returned from adduser. As I can see, there are only two possible values for @status - 0 or 1. But for some odd reason I end up with null when calling adduser through modifyuser, even though the extensions are added correctly.

Now, if I test the functions by using

USE [Ecotech]
GO
DECLARE    @status int
EXEC    [dbo].[Ecotech_AddUser] '288b2eec-7f3d-494e-a075-715cc1ebab8f', NULL, NULL, 
        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @status = @status OUTPUT

SELECT    @status as N'@status'
GO

I get the expected result (1 if created, 0 if not).

However, if I call

USE [Ecotech]
GO
DECLARE    @status int
EXEC    [dbo].[Ecotech_ModifyUser] '288b2eec-7f3d-494e-a075-715cc1ebab8f', NULL, NULL,
        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @status = @status OUTPUT

SELECT    @status as N'@status'
GO

I end up with null again.

Can someone help me out with that? I've surfed the web for an hour trying to figure out what I'm doing wrong, but I cannot find any errors. I'm sure it's something small and stupid, but...

Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.