Re: trying to write my 1st stored procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <me@xxxxxxxxxxx> wrote in message news:%23D66yLb7FHA.808@xxxxxxxxxxxxxxxxxxxxxxx

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 



.

Relevant Pages

  • Re: Conversion error
    ... You canimport both types of data into a varchar column now. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... >>character data has to be one or the other. ...
    (microsoft.public.sqlserver.dts)
  • Re: trying to write my 1st stored procedure
    ... why do you use varchar for BugInfo.BugInfo? ... non-Unicode character data with a length of n bytes. ... consistently translate the bit patterns in Unicode data into characters ... @bugtext nvarchar, @idkey int OUTPUT ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Char and Varchar
    ... >>If changing an element to full size causes it to no longer fit on the ... > forwarding pointer is changed. ... >>Would you ever create a table with lets say 100 single character VARCHAR ...
    (microsoft.public.sqlserver.server)
  • Re: Char and Varchar
    ... >>If changing an element to full size causes it to no longer fit on the ... > forwarding pointer is changed. ... >>Would you ever create a table with lets say 100 single character VARCHAR ...
    (microsoft.public.sqlserver.programming)
  • Re: varchar problem
    ... A space is just another character. ... >> want to strip them, ... VARCHAR strings will only be as long as ... > the stored string itself. ...
    (alt.php)