SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 subquery question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

trouble2
Constraint Violating Yak Guru

Netherlands
267 Posts

Posted - 12/10/2010 :  07:43:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 12/10/2010 :  08:06:40  Show Profile  Reply with Quote
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
Go to Top of Page

Ifor
Aged Yak Warrior

574 Posts

Posted - 12/10/2010 :  08:14:59  Show Profile  Reply with Quote
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.

Edited by - Ifor on 12/10/2010 08:16:36
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 12/10/2010 :  08:21:55  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000