subquery question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

subquery question

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-12-10 : 07:43:27

HI, I want to do this on SQL 2000 (don't ask):

CREATE PROCEDURE [dbo].[Category_Add]
@CategoryName nvarchar(255),
@CategoryStatus bit

AS

DECLARE @CategoryID INT

INSERT INTO
Category(
[CategoryName],
[CategoryOrder],
[CategoryStatus]
)
VALUES
(
@CategoryName,
IsNull((select max(CategoryOrder) From Category), 0) + 1,
@CategoryStatus
)

SET @CategoryID = SCOPE_IDENTITY()

RETURN @CategoryID
GO

It says:

Subqueries are not allowed in this context. Only scalar expressions are allowed.
So what would be a good solution for this

The secret to creativity is knowing how to hide your sources. (Einstein)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-10 : 08:06:40

Try:

CREATE PROCEDURE [dbo].[Category_Add]
@CategoryName nvarchar(255),
@CategoryStatus bit,
@CategoryID INT OUTPUT

AS
BEGIN
DECLARE @MaxCatOrder INT

Select @MaxCatOrder =Max(CategoryOrder) From Category

INSERT INTO
Category(
[CategoryName],
[CategoryOrder],
[CategoryStatus]
)
VALUES
(
@CategoryName,
isnull(@MaxCatOrder,0) + 1,
@CategoryStatus
)

SET @CategoryID = SCOPE_IDENTITY()

END
GO

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-12-10 : 08:14:59

You should only return errors.
Use an OUTOUT parameter instead:


SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Category_Add
	@CategoryName nvarchar(255)
	,@CategoryStatus bit
	,@CategoryID int OUTPUT
AS

SET NOCOUNT ON

INSERT INTO dbo.Category(CategoryName, CategoryOrder, CategoryStatus)
SELECT @CategoryName
	,COALESCE(MAX(CategoryOrder), 0) + 1
	,@CategoryStatus
FROM dbo.Category WITH (UPDLOCK, HOLDLOCK)

SET @CategoryID = SCOPE_IDENTITY()
GO

-- to use
DECLARE @CategoryID int
EXEC dbo.Category_Add 'NewName', 1, @CategoryID OUTPUT
SELECT @CategoryID

Edit: Sorry - answer already given.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-10 : 08:21:55

quote:

INSERT INTO dbo.Category(CategoryName, CategoryOrder, CategoryStatus)
SELECT @CategoryName
,COALESCE(MAX(CategoryOrder), 0) + 1
,@CategoryStatus
FROM dbo.Category WITH (UPDLOCK, HOLDLOCK)

When multiple inserts are happening on different systems simultaneosly, I feel this approach is correct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources