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!