Check for other values with a procedure.

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

SQL Server Development (2000)

Check for other values with a procedure.

Author

Topic

JTProg
Starting Member

24 Posts

Posted - 2006-09-15 : 16:00:29

How can I make this procedure to only allow an identical @LineItem or null each time, and nothing else? If it is something else, I want to return a message to the user stating values have to be identical after an initial entry has been made instead of running the insert command. This is not a default. Each batch can have a range(g2, g3, g4, g5) of values, but once a value from that range has been identified it should remain the same for that batch.

CREATE PROCEDURE dbo.procInsertSingle
@ID Nvarchar(8),
@Type Nvarchar(20),
@LineItem Nvarchar(5),
@ResultMessage Nvarchar(50) OUTPUT

AS

IF EXISTS(Select LineItem from dbo.Line Where ID = ID and LineItem = @LineItem)
BEGIN
BEGIN TRAN
INSERT INTO dbo.Line
( Name,
Type,
LineItem)

VALUES
( @Name,
@Type,
@LineItem)
END
ELSE
BEGIN
Select @ResultMessage = 'values have to be identical after an initial entry'
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 05:32:02

First I think
IF EXISTS(Select * from dbo.Line Where ID = ID and LineItem = @LineItem)

should be
IF EXISTS(Select * from dbo.Line Where ID = @ID and LineItem = @LineItem)

Now you have to clarify your objective...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources