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(UnitPosition) FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef
// 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-- 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(UnitPosition)
FROM tblUnitGroup
WHERE QualRef=@.QualRef AND UnitRef=@.UnitRef AND
UnitGroupRef=@.UnitGroupRef
-- 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)|||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(unitposition),0)+1
FROM tblUnitGroup
WHERE qualref = @.qualref
AND unitref = @.unitref
AND unitgroupref = @.unitgroupref
GO
--
David Portas
SQL Server MVP
--|||Thankyou for the quick reply.|||Thanks
