Lloyd said:
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?
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
