Developer Forum

What am I doing wrong when I execute a SROC (using Output parameters)
from within another?

The following code:

Declare @IPAddressId Int
Exec Login_GetIPAddressId '127.0.0.1', @IPAddressId
Select @IPAddressId
Go

gives Null. Why?

ALTER PROC Login_GetIPAddressId
(@IPAddress varchar(15), @IPAddressId Int Output)
As
IF Exists (SELECT * From Login_IPAddress Where IPAddress =
@IPAddress)
Begin
Set @IPAddressId = (SELECT IPAddressId From Login_IPAddress
Where IPAddress = @IPAddress)
End
Else
Begin
Insert Into Login_IPAddress(IPAddress) Values(@IPAddress)
Set @IPAddressId = @@Identity
End
Return

I am trying to use this sproc: Login_GetIPAddressId from within
another.


Here is my Login_IPAddress table:

CREATE TABLE [dbo].[Login_IPAddress](
[IPAddressID] [int] IDENTITY(1,1) NOT NULL,
[IPAddress] [varchar](15) NOT NULL DEFAULT (''),
CONSTRAINT [PK_IPAddress] PRIMARY KEY CLUSTERED ( [IPAddressID]
ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 85)
)

ALTER PROCEDURE [dbo].[Login_Successful_Add]
(
@LoginID INT,
@IPAddress VARCHAR(15),
@LoginDate DATETIME,
@BrowserId Int
)
AS
Declare @IPAddressId Int
Exec Login_GetIPAddressId @IPAddress, @IPAddressId
INSERT INTO Login_Successful (loginId, IPAddressId, login_at,
BrowserId)
VALUES (@LoginID, @IPAddressId, @LoginDate, @BrowserId)
DELETE FROM Login_Successful WHERE login_at < DATEADD(DAY, -30,
@LoginDate)
DECLARE @ADDRESSCOUNT INT
SET @ADDRESSCOUNT = (SELECT count(*) FROM Login_IPAddress_Log
WHERE IPAddressId = @IPAddressId AND LoginID = @LoginID)
IF @ADDRESSCOUNT = 0
INSERT INTO Login_IPAddress_Log(LoginID, TimesUsed, LastUsed,
IPAddressId)
VALUES (@LoginID, 1, @LoginDate, @IPAddressId)
ELSE
UPDATE Login_IPAddress_Log
SET TimesUsed = TimesUsed + 1, LastUsed = @LoginDate
WHERE LoginID = @LoginID AND IPAddressId = @IPAddressId
RETURN

>

You have to use the "OUTPUT" keyword in the call to the SP too, so your call
should look like:
Exec Login_GetIPAddressId '127.0.0.1', @IPAddressId OUTPUT

Palli

Thanks Palli, I just figured this out by googling it.

I actually came back here to apologise to everyone for posting that
question as I should've googled more beforehand.