trying to write my 1st stored procedure

20 Nov 2005 6:32 AM

Lloyd Dupont

I'm try ing to create a stored procedure which store bug information if it is a new bug and return -1, or inserted bug id.
I wrote the TSQL below but it's incorrect and I daon't see how to fix it, any tips?
============
ALTER PROCEDURE dbo.AddBug
    @bugtext nvarchar(max) ,
    @idkey int OUTPUT
    AS
        SET @idkey = -1;
        SELECT TOP (1) @idkey = ID, BugInfo FROM BugInfo WHERE BugInfo = @bugtext;

        IF @idkey > -1
        BEGIN
            @idkey = -1;
            RETURN;
        END

        INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
        SELECT @idkey = @@IDENTITY ;
RETURN

20 Nov 2005 9:15 AM

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

Lloyd Dupont wrote:
Show quote

> I'm try ing to create a stored procedure which store bug information if
> it is a new bug and return -1, or inserted bug id.
> I wrote the TSQL below but it's incorrect and I daon't see how to fix
> it, any tips?
> ============
> ALTER PROCEDURE dbo.AddBug
>     @bugtext nvarchar(max) ,
>     @idkey int OUTPUT
>     AS
>         SET @idkey = -1;
>         SELECT TOP (1) @idkey = ID, BugInfo FROM BugInfo WHERE BugInfo =
> @bugtext;
>
>         IF @idkey > -1
>         BEGIN
>             @idkey = -1;
>             RETURN;
>         END
>
>         INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
>         SELECT @idkey = @@IDENTITY ;
> RETURN
>

20 Nov 2005 10:14 AM

Lloyd Dupont

Hi Luxpes, it seems to work, indeed :-D

That raises 2 interesting question,

1. why do you use varchar for BugInfo.BugInfo?
doesn't it fills the end of the text with '\0' up to N ?
I want to store just my string without extraneous empty char, isn't
nvarchar(max) more appropriate?
(I was thinking to use ntext but the doc says it's deprecated and I should
use nvarchar(max))

2. what is this collate statement for?
is it important?

Show quote

"luxspes" <m*@privacy.net> wrote in message
news:%23D66yLb7FHA.808@TK2MSFTNGP09.phx.gbl...
> 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:
>> I'm try ing to create a stored procedure which store bug information if
>> it is a new bug and return -1, or inserted bug id.
>> I wrote the TSQL below but it's incorrect and I daon't see how to fix it,
>> any tips?
>> ============
>> ALTER PROCEDURE dbo.AddBug
>>     @bugtext nvarchar(max) ,
>>     @idkey int OUTPUT
>>     AS
>>         SET @idkey = -1;
>>         SELECT TOP (1) @idkey = ID, BugInfo FROM BugInfo WHERE BugInfo =
>> @bugtext;
>>
>>         IF @idkey > -1
>>         BEGIN
>>             @idkey = -1;
>>             RETURN;
>>         END
>>
>>         INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
>>         SELECT @idkey = @@IDENTITY ;
>> RETURN

20 Nov 2005 2:02 PM

luxspes

Lloyd Dupont wrote:

> Hi Luxpes, it seems to work, indeed :-D
>
> That raises 2 interesting question,
>
> 1. why do you use varchar for BugInfo.BugInfo?
> doesn't it fills the end of the text with '\0' up to N ?

no... IMHO that is "char" (and "nchar"), both varchar and nvarchar fill
"dinamically" depending on the size of the string to store.

> I want to store just my string without extraneous empty char, isn't
> nvarchar(max) more appropriate?

it is exactly the same as varchar(max)

> (I was thinking to use ntext but the doc says it's deprecated and I should
> use nvarchar(max))

char [ ( n ) ]
Fixed-length, non-Unicode character data with a length of n bytes. n
must be a value from 1 through 8,000. The storage size is n bytes. The
SQL-2003 synonym for char is character.

varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1
through 8,000. max indicates that the maximum storage size is 2^31-1
bytes. The storage size is the actual length of data entered + 2 bytes.
The data entered can be 0 characters in length. The SQL-2003 synonyms
for varchar are char varying or character varying.

Now... why if char is the same as nchar and varchar is the same as
nvarchar we have both kinds of types?:

The Unicode specification defines a single encoding scheme for most
characters widely used in businesses around the world. All computers
consistently translate the bit patterns in Unicode data into characters
using the single Unicode specification. This ensures that the same bit
pattern is always converted to the same character on all computers. Data
can be freely transferred from one database or computer to another
without concern that the receiving system will translate the bit
patterns into characters incorrectly. nchar and nvarchar work in "UNICODE"

The COLLATE its precicely because I am not using nchar or nvarchar... so
I need to say which code page that defines what patterns of bits
represent each character I am using... thinking things again... i think
it would be better to work in "unicode mode" ;)

Show quote

>
> 2. what is this collate statement for?
> is it important?
>
> "luxspes" <m*@privacy.net> wrote in message
> news:%23D66yLb7FHA.808@TK2MSFTNGP09.phx.gbl...
>
>>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:
>>
>>>I'm try ing to create a stored procedure which store bug information if
>>>it is a new bug and return -1, or inserted bug id.
>>>I wrote the TSQL below but it's incorrect and I daon't see how to fix it,
>>>any tips?
>>>============
>>>ALTER PROCEDURE dbo.AddBug
>>>    @bugtext nvarchar(max) ,
>>>    @idkey int OUTPUT
>>>    AS
>>>        SET @idkey = -1;
>>>        SELECT TOP (1) @idkey = ID, BugInfo FROM BugInfo WHERE BugInfo =
>>>@bugtext;
>>>
>>>        IF @idkey > -1
>>>        BEGIN
>>>            @idkey = -1;
>>>            RETURN;
>>>        END
>>>
>>>        INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
>>>        SELECT @idkey = @@IDENTITY ;
>>> RETURN
>
>
>

21 Nov 2005 12:27 PM

Lloyd Dupont

Hi Luxpes,

Thanks again for this complete and informed answer!

Show quote

"luxspes" <m*@privacy.net> wrote in message
news:uEm4Gsd7FHA.2716@TK2MSFTNGP11.phx.gbl...
> Lloyd Dupont wrote:
>> Hi Luxpes, it seems to work, indeed :-D
>>
>> That raises 2 interesting question,
>>
>> 1. why do you use varchar for BugInfo.BugInfo?
>> doesn't it fills the end of the text with '\0' up to N ?
> no... IMHO that is "char" (and "nchar"), both varchar and nvarchar fill
> "dinamically" depending on the size of the string to store.
>> I want to store just my string without extraneous empty char, isn't
>> nvarchar(max) more appropriate?
> it is exactly the same as varchar(max)
>> (I was thinking to use ntext but the doc says it's deprecated and I
>> should use nvarchar(max))
>
> char [ ( n ) ]
> Fixed-length, non-Unicode character data with a length of n bytes. n must
> be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003
> synonym for char is character.
>
> varchar [ ( n | max ) ]
> Variable-length, non-Unicode character data. n can be a value from 1
> through 8,000. max indicates that the maximum storage size is 2^31-1
> bytes. The storage size is the actual length of data entered + 2 bytes.
> The data entered can be 0 characters in length. The SQL-2003 synonyms for
> varchar are char varying or character varying.
>
> Now... why if char is the same as nchar and varchar is the same as
> nvarchar we have both kinds of types?:
>
> The Unicode specification defines a single encoding scheme for most
> characters widely used in businesses around the world. All computers
> consistently translate the bit patterns in Unicode data into characters
> using the single Unicode specification. This ensures that the same bit
> pattern is always converted to the same character on all computers. Data
> can be freely transferred from one database or computer to another without
> concern that the receiving system will translate the bit patterns into
> characters incorrectly. nchar and nvarchar work in "UNICODE"
>
> The COLLATE its precicely because I am not using nchar or nvarchar... so I
> need to say which code page that defines what patterns of bits represent
> each character I am using... thinking things again... i think it would be
> better to work in "unicode mode" ;)
>
>
>>
>> 2. what is this collate statement for?
>> is it important?
>>
>> "luxspes" <m*@privacy.net> wrote in message
>> news:%23D66yLb7FHA.808@TK2MSFTNGP09.phx.gbl...
>>
>>>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:
>>>
>>>>I'm try ing to create a stored procedure which store bug information if
>>>>it is a new bug and return -1, or inserted bug id.
>>>>I wrote the TSQL below but it's incorrect and I daon't see how to fix
>>>>it, any tips?
>>>>============
>>>>ALTER PROCEDURE dbo.AddBug
>>>>    @bugtext nvarchar(max) ,
>>>>    @idkey int OUTPUT
>>>>    AS
>>>>        SET @idkey = -1;
>>>>        SELECT TOP (1) @idkey = ID, BugInfo FROM BugInfo WHERE BugInfo =
>>>> @bugtext;
>>>>
>>>>        IF @idkey > -1
>>>>        BEGIN
>>>>            @idkey = -1;
>>>>            RETURN;
>>>>        END
>>>>
>>>>        INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
>>>>        SELECT @idkey = @@IDENTITY ;
>>>> RETURN
>>
>>

22 Nov 2005 5:37 AM

luxspes

Lloyd Dupont wrote:

> Hi Luxpes,
>
> Thanks again for this complete and informed answer!

you are welcome ;)

Show quote

>
> "luxspes" <m*@privacy.net> wrote in message
> news:uEm4Gsd7FHA.2716@TK2MSFTNGP11.phx.gbl...
>
>>Lloyd Dupont wrote:
>>
>>>Hi Luxpes, it seems to work, indeed :-D
>>>
>>>That raises 2 interesting question,
>>>
>>>1. why do you use varchar for BugInfo.BugInfo?
>>>doesn't it fills the end of the text with '\0' up to N ?
>>
>>no... IMHO that is "char" (and "nchar"), both varchar and nvarchar fill
>>"dinamically" depending on the size of the string to store.
>>
>>>I want to store just my string without extraneous empty char, isn't
>>>nvarchar(max) more appropriate?
>>
>>it is exactly the same as varchar(max)
>>
>>>(I was thinking to use ntext but the doc says it's deprecated and I
>>>should use nvarchar(max))
>>
>>char [ ( n ) ]
>>Fixed-length, non-Unicode character data with a length of n bytes. n must
>>be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003
>>synonym for char is character.
>>
>>varchar [ ( n | max ) ]
>>Variable-length, non-Unicode character data. n can be a value from 1
>>through 8,000. max indicates that the maximum storage size is 2^31-1
>>bytes. The storage size is the actual length of data entered + 2 bytes.
>>The data entered can be 0 characters in length. The SQL-2003 synonyms for
>>varchar are char varying or character varying.
>>
>>Now... why if char is the same as nchar and varchar is the same as
>>nvarchar we have both kinds of types?:
>>
>>The Unicode specification defines a single encoding scheme for most
>>characters widely used in businesses around the world. All computers
>>consistently translate the bit patterns in Unicode data into characters
>>using the single Unicode specification. This ensures that the same bit
>>pattern is always converted to the same character on all computers. Data
>>can be freely transferred from one database or computer to another without
>>concern that the receiving system will translate the bit patterns into
>>characters incorrectly. nchar and nvarchar work in "UNICODE"
>>
>>The COLLATE its precicely because I am not using nchar or nvarchar... so I
>>need to say which code page that defines what patterns of bits represent
>>each character I am using... thinking things again... i think it would be
>>better to work in "unicode mode" ;)
>>
>>
>>
>>>2. what is this collate statement for?
>>>is it important?
>>>
>>>"luxspes" <m*@privacy.net> wrote in message
>>>news:%23D66yLb7FHA.808@TK2MSFTNGP09.phx.gbl...
>>>
>>>
>>>>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:
>>>>
>>>>
>>>>>I'm try ing to create a stored procedure which store bug information if
>>>>>it is a new bug and return -1, or inserted bug id.
>>>>>I wrote the TSQL below but it's incorrect and I daon't see how to fix
>>>>>it, any tips?
>>>>>============
>>>>>ALTER PROCEDURE dbo.AddBug
>>>>>   @bugtext nvarchar(max) ,
>>>>>   @idkey int OUTPUT
>>>>>   AS
>>>>>       SET @idkey = -1;
>>>>>       SELECT TOP (1) @idkey = ID, BugInfo FROM BugInfo WHERE BugInfo =
>>>>>@bugtext;
>>>>>
>>>>>       IF @idkey > -1
>>>>>       BEGIN
>>>>>           @idkey = -1;
>>>>>           RETURN;
>>>>>       END
>>>>>
>>>>>       INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
>>>>>       SELECT @idkey = @@IDENTITY ;
>>>>>RETURN
>>>
>>>
>

20 Nov 2005 11:30 AM

Lloyd Dupont

thanks, BTW!

Show quote

"luxspes" <m*@privacy.net> wrote in message
news:%23D66yLb7FHA.808@TK2MSFTNGP09.phx.gbl...
> 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:
>> I'm try ing to create a stored procedure which store bug information if
>> it is a new bug and return -1, or inserted bug id.
>> I wrote the TSQL below but it's incorrect and I daon't see how to fix it,
>> any tips?
>> ============
>> ALTER PROCEDURE dbo.AddBug
>>     @bugtext nvarchar(max) ,
>>     @idkey int OUTPUT
>>     AS
>>         SET @idkey = -1;
>>         SELECT TOP (1) @idkey = ID, BugInfo FROM BugInfo WHERE BugInfo =
>> @bugtext;
>>
>>         IF @idkey > -1
>>         BEGIN
>>             @idkey = -1;
>>             RETURN;
>>         END
>>
>>         INSERT INTO BugInfo ( BugInfo ) VALUES ( @bugtext );
>>         SELECT @idkey = @@IDENTITY ;
>> RETURN