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!

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 ;)...

Lloyd Dupont wrote:
Show quoteHide quote

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 ;)...

Lloyd Dupont wrote:
Show quoteHide quote

luxspes wrote:
Show quoteHide quoteInstead 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.

Thanks Sericinus, interesting precision.

Show quoteHide quote

Accept/Reject Cascading - DataSet
ADO.NET 2.0 TransactionScope object question: across business obje
Alternative to using DSN to connect to database
SQL tables question
concurrent transactions