Local variables in stored proc

[hals_left]

How do you declare and then SELECT a value for a local variable within
stored procedure, increment the value and then use in an Insert
statement? Thanks

Any sites that explain this syntax for SQL Server 2000? Thanks
hals_left
CREATE PROCEDURE [dbo].[InsertQualUnit]
@QualRef tinyint,
@UnitRef tinyint,
@UnitGroupRef tinyint,

// this needs to be a local var not an output param, how ?
@UnitPosition tinyint Output

AS

// Assign a value to the the variable from a SELECT query, how ?
SELECT @UnitPosition= SELECT MAX(UnitPositio n) FROM tblUnitGroup
WHERE QualRef=@QualRe f AND UnitRef=@UnitRe f AND
UnitGroupRef=@U nitGroupRef

// inc the value
@UnitPosition+= 1

// Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @QualRef, @UnitRef, @UnitGroupRef , @UnitPosition)
GO

[louis]

-- MODIFIED STORED PROC:
CREATE PROCEDURE [dbo].[InsertQualUnit]
@QualRef tinyint,
@UnitRef tinyint,
@UnitGroupRef tinyint

AS

-- this needs to be a local var not an output param, how ?
declare @UnitPosition tinyint

-- Assign a value to the the variable from a SELECT query, how ?
SELECT @UnitPosition= MAX(UnitPositio n)
FROM tblUnitGroup
WHERE QualRef=@QualRe f AND UnitRef=@UnitRe f AND
UnitGroupRef=@U nitGroupRef

-- inc the value
select @UnitPosition=@ UnitPosition+1

-- Use the new value in another SQL statement
INSERT INTO tblQualUnits ( QualRef, UnitRef, UnitGroupRef ,
UnitPosition )
VALUES ( @QualRef, @UnitRef, @UnitGroupRef , @UnitPosition)

[David Portas]

See below and the following link.
http://www.google.co.uk/groups?selm=...40giganews.com

But your proc looks a bit strange. Why not just make the key (qualref,
unitref, unitgroupref) and then increment a quantity column? Like:

UPDATE tblQualUnits
SET unit_quantity = unit_quantity + 1
WHERE qualref = @qualref
AND unitref = @unitref
AND unitgroupref = @unitgroupref

Otherwise your table is just an accumulator of redundant rows.

CREATE PROCEDURE [dbo].[InsertQualUnit]
@qualref TINYINT,
@unitref TINYINT,
@unitgroupref TINYINT
AS

INSERT INTO tblQualUnits (qualref, unitref, unitgroupref, unitposition)

SELECT @qualref, @unitref, @unitgroupref,
COALESCE(MAX(un itposition),0)+ 1
FROM tblUnitGroup
WHERE qualref = @qualref
AND unitref = @unitref
AND unitgroupref = @unitgroupref
GO

--
David Portas
SQL Server MVP
--

[cc900630]

Thankyou for the quick reply.

[cc900630]

Thanks