SCOPE_IDENTITY & Must declare variable

25 Aug 2006 5:41 PM

modhak

Hi All
I am using SQL Server 2005

I have a table

CREATE TABLE dbo.testrun_reports
(
    RunID INT IDENTITY(1,1) PRIMARY KEY,
    RunDate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
    db2dbreport VARCHAR(MAX),
    incexcreport VARCHAR(MAX)
);

and stored procedure

alter PROCEDURE dbo.Run_Start
    @RunID INT OUTPUT
AS

    SET NOCOUNT ON;
    INSERT dbo.testrun_reports(db2dbreport,incexcreport) values
('Started','Started');
    SELECT @RunID = SCOPE_IDENTITY();

When I try to create this Stored Procedure
I am getting an error 'Must Declare Scalar variable @RunID'
I have it as OUTPUT paramter right?

What is the problem here. Please let me know.

Thanks

25 Aug 2006 5:51 PM

David Browne

<mod***@gmail.com> wrote in message
Show quote

news:1156527713.966706.50160@m73g2000cwd.googlegroups.com...
> Hi All
> I am using SQL Server 2005
>
> I have a table
>
> CREATE TABLE dbo.testrun_reports
> (
>    RunID INT IDENTITY(1,1) PRIMARY KEY,
>    RunDate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
>    db2dbreport VARCHAR(MAX),
> incexcreport VARCHAR(MAX)
> );
>
> and stored procedure
>
> alter PROCEDURE dbo.Run_Start
>    @RunID INT OUTPUT
> AS
>
>    SET NOCOUNT ON;
>    INSERT dbo.testrun_reports(db2dbreport,incexcreport) values
> ('Started','Started');
>    SELECT @RunID = SCOPE_IDENTITY();
>
> When I try to create this Stored Procedure
> I am getting an error 'Must Declare Scalar variable @RunID'
> I have it as OUTPUT paramter right?
>
> What is the problem here. Please let me know.
>

No idea. It works fine for me:

create table testrun_reports
(
id int identity primary key,
db2dbreport varchar(10),
incexcreport varchar(10)
)

create PROCEDURE dbo.Run_Start
    @RunID INT OUTPUT
AS

    SET NOCOUNT ON;
    INSERT dbo.testrun_reports(db2dbreport,incexcreport) values
('Started','Started');
    SELECT @RunID = SCOPE_IDENTITY();

go

declare @RunID int

exec dbo.Run_Start @RunID OUTPUT

select @RunID RunID

David

25 Aug 2006 6:16 PM

modhak

I noticed someone else also had same issue
http://msforums.ph/forums/135728/ShowPost.aspx

select RunID = SCOPE_IDENTITY(); -- works
select @RunID = SCOPE_IDENTITY(); -- does not work

What is the difference between the two.

Please let me know.

25 Aug 2006 6:23 PM

David Browne

<mod***@gmail.com> wrote in message

news:1156529797.705740.88730@i3g2000cwc.googlegroups.com...
>I noticed someone else also had same issue
> http://msforums.ph/forums/135728/ShowPost.aspx
>
> select RunID = SCOPE_IDENTITY(); -- works

This is a simple select that returns a value aliased as RunID.

> select @RunID = SCOPE_IDENTITY(); -- does not work

This is a variable assignment.

David

25 Aug 2006 6:25 PM

Kalen Delaney

The first selects a value and displays it as tabular data with a column
header of RunID.

The second tries to assign a value to a variable. The variable must be
declared in the same batch in which it is assigned. So by itself, with no
variable declaration, the second will fail.

--
HTH
Kalen Delaney, SQL Server MVP

<mod***@gmail.com> wrote in message

Show quote

news:1156529797.705740.88730@i3g2000cwc.googlegroups.com...
>I noticed someone else also had same issue
> http://msforums.ph/forums/135728/ShowPost.aspx
>
> select RunID = SCOPE_IDENTITY(); -- works
> select @RunID = SCOPE_IDENTITY(); -- does not work
>
> What is the difference between the two.
>
> Please let me know.
>

25 Aug 2006 6:31 PM

modhak

Then why is it failing, it is declared as output paramter. I just want
to know if something specific to my environment setup.
Thanks

25 Aug 2006 6:48 PM

David Browne

<mod***@gmail.com> wrote in message

news:1156530678.610423.218160@p79g2000cwp.googlegroups.com...
> Then why is it failing, it is declared as output paramter. I just want
> to know if something specific to my environment setup.
> Thanks
>

It's not failing. It works fine.

David

25 Aug 2006 6:56 PM

Kalen Delaney

Maybe you're getting the error in the call to the procedure?
How are you calling it... are you declaring a variable to pass in as the
actual output parameter?

e.g.

declare @myvar int
exec dbo.Run_Start @myvar output

--
HTH
Kalen Delaney, SQL Server MVP

Show quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:eqyFIcHyGHA.4972@TK2MSFTNGP03.phx.gbl...
>
> <mod***@gmail.com> wrote in message
> news:1156530678.610423.218160@p79g2000cwp.googlegroups.com...
>> Then why is it failing, it is declared as output paramter. I just want
>> to know if something specific to my environment setup.
>> Thanks
>>
>
> It's not failing. It works fine.
>
> David
>

25 Aug 2006 7:04 PM

Arnie Rowland

It works fine for me.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

<mod***@gmail.com> wrote in message

Show quote

news:1156527713.966706.50160@m73g2000cwd.googlegroups.com...
> Hi All
> I am using SQL Server 2005
>
> I have a table
>
> CREATE TABLE dbo.testrun_reports
> (
>    RunID INT IDENTITY(1,1) PRIMARY KEY,
>    RunDate SMALLDATETIME NOT NULL DEFAULT GETDATE(),
>    db2dbreport VARCHAR(MAX),
> incexcreport VARCHAR(MAX)
> );
>
> and stored procedure
>
> alter PROCEDURE dbo.Run_Start
>    @RunID INT OUTPUT
> AS
>
>    SET NOCOUNT ON;
>    INSERT dbo.testrun_reports(db2dbreport,incexcreport) values
> ('Started','Started');
>    SELECT @RunID = SCOPE_IDENTITY();
>
> When I try to create this Stored Procedure
> I am getting an error 'Must Declare Scalar variable @RunID'
> I have it as OUTPUT paramter right?
>
> What is the problem here. Please let me know.
>
> Thanks
>