SqlServer stored procedure newby question

[Lloyd Dupont]

I'm trying to write a stored procedure which try to write some text and
return the new ID or -1 if the text was already there.

something along those lines:
Table: BugReport
------------------
ID: int, identity/auto-increment
Text: nvarchar(max)

create bool Procedure(nvarchar(max) bugText)
{
if( count( select * from BugReport where Text = bugText) )
return -1;

int ret;
insert into BugReport ( Text ) values ( BugReport ); select ret =
@@lastrowid;
return ret;
}

How would you write this in real/valid TSQL ?
Thanks!

 

[luxspes]

What about:

ALTER PROCEDURE [dbo].[AddBug]
@bugtext nvarchar(max) ,
@idkey int OUTPUT
AS
BEGIN
DECLARE @count int ;
SELECT @count = count(*) FROM BugInfo WHERE BugInfo= @bugtext;

IF @count = 0
BEGIN
INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
SELECT @idkey = @@IDENTITY;
RETURN;
END
SET @idkey = -1;
END

I tested it with the following code...and it worked fine:

DECLARE @return_value int,
@idkey int

SELECT @idkey = 0

EXEC @return_value = [dbo].[AddBug]
@bugtext = N'Some bug',
@idkey = @idkey OUTPUT

SELECT @idkey as N'@idkey'

SELECT 'Return Value' = @return_value

GO

The table was defined like this (using SQLServer 2005):

CREATE TABLE [dbo].[BugInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BugInfo] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
CONSTRAINT [PK_BugInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I hope all this it works for you ...

 

[luxspes]

What about:

ALTER PROCEDURE [dbo].[AddBug]
@bugtext nvarchar(max) ,
@idkey int OUTPUT
AS
BEGIN
DECLARE @count int ;
SELECT @count = count(*) FROM BugInfo WHERE BugInfo= @bugtext;

IF @count = 0
BEGIN
INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
SELECT @idkey = @@IDENTITY;
RETURN;
END
SET @idkey = -1;
END

I tested it with the following code...and it worked fine:

DECLARE @return_value int,
@idkey int

SELECT @idkey = 0

EXEC @return_value = [dbo].[AddBug]
@bugtext = N'Some bug',
@idkey = @idkey OUTPUT

SELECT @idkey as N'@idkey'

SELECT 'Return Value' = @return_value

GO

The table was defined like this (using SQLServer 2005):

CREATE TABLE [dbo].[BugInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BugInfo] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
CONSTRAINT [PK_BugInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I hope all this it works for you ...

 

[Sericinus hunter]

What about:

ALTER PROCEDURE [dbo].[AddBug]
@bugtext nvarchar(max) ,
@idkey int OUTPUT
AS
BEGIN
DECLARE @count int ;
SELECT @count = count(*) FROM BugInfo WHERE BugInfo= @bugtext;

IF @count = 0
BEGIN
INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
SELECT @idkey = @@IDENTITY;
RETURN;
END
SET @idkey = -1;
END

Instead of SELECT COUNT(*) I would suggest

IF EXISTS (SELECT 1 FROM BugInfo WHERE BugInfo = @bugtext)
BEGIN
...further as written

This will likely be more efficient.

 

[Lloyd Dupont]

Thanks Sericinus, interesting precision.

What about:

ALTER PROCEDURE [dbo].[AddBug]
@bugtext nvarchar(max) ,
@idkey int OUTPUT
AS
BEGIN
DECLARE @count int ;
SELECT @count = count(*) FROM BugInfo WHERE BugInfo= @bugtext;

IF @count = 0
BEGIN
INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
SELECT @idkey = @@IDENTITY;
RETURN;
END
SET @idkey = -1;
END

Instead of SELECT COUNT(*) I would suggest

IF EXISTS (SELECT 1 FROM BugInfo WHERE BugInfo = @bugtext)
BEGIN
...further as written

This will likely be more efficient.