Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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


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

> The following code:

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

> gives Null. Why?

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

-----------------------------------------------Reply-----------------------------------------------

You need to declare output parameters when invoking the procedure as well:

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

ML

---
http://milambda.blogspot.com/

-----------------------------------------------Reply-----------------------------------------------

On 30 May, 12:00, "Pall Bjornsson" <p@kvos.is> wrote:

> > The following code:

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

> > gives Null. Why?

> 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.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc