print @variable probem

[I got a serious problem on print @.variable, please be kind to take a look]
CREATE PROCEDURE dbo.Get_seainvno
@.prefix varchar(20),
@.countno int,
@.period varchar(6),
@.result int output
AS
DECLARE @.testing int
IF @.countno > 0
BEGIN
SELECT number AS result
FROM arinvNo
WHERE prefix = @.prefix
print @.result <--I can show
SELECT @.testing = @.result %100
print @.testing <----It
can't show the variable in SQL analayzer ? Pls help
IF (@.result % 100) >= 90
BEGIN
UPDATE arinvNo
SET number = number + 75
WHERE prefix = @.prefix
END
ELSE
BEGIN
UPDATE arinvNo
SET number = number + 1
WHERE prefix = @.prefix
ENDDid you put out the length of the result, to see wheter it exsists ?
Additionaly it would put some identifier around this:
print 'The result after is:' + Cast(ISNULL(@.result,'Value is NULL') as
varchar(20))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ray" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23wiSwouSFHA.3152@.TK2MSFTNGP12.phx.gbl...
> [I got a serious problem on print @.variable, please be kind to take a
> look]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing
> <----It can't show
> the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>|||Ray
I try and always use a SET when settting variables. The reason for this is
when the select returns a null you won't get unexpected or null results. I
know you can use isnull or set concat_null_yields_null but it's a lot of
work around for something that could have been prevented in the first place.
Coming from a C background I also like to always initialise variables. Even
if it's not needed.
Instead of select @.var = col from table
I would use
Set @.var = 0
I would use set @.var = (select col from table)
But to come back to your problem without changing your code... what's the
parameters your running this procedure with. I'm sure you are no noddy but
you sure you want to use % modulus , when you modulus something is will
always return everything n - 1 when mod n thus 99 - 91 will fall through you
r
loop. Is this suppose to happen ?
"Ray" wrote:

> [I got a serious problem on print @.variable, please be kind to take a look
]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing <----
It
> can't show the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>
>|||Ray
I try and always use a SET when settting variables. The reason for this is
when the select returns a null you won't get unexpected or null results. I
know you can use isnull or set concat_null_yields_null but it's a lot of
work around for something that could have been prevented in the first place.
Coming from a C background I also like to always initialise variables. Even
if it's not needed.
Instead of select @.var = col from table
I would use
Set @.var = 0
I would use set @.var = (select col from table)
But to come back to your problem without changing your code... what's the
parameters your running this procedure with. I'm sure you are no noddy but
you sure you want to use % modulus , when you modulus something is will
always return everything n - 1 when mod n thus 99 - 91 will fall through you
r
loop. Is this suppose to happen ?
"Ray" wrote:

> [I got a serious problem on print @.variable, please be kind to take a look
]
> CREATE PROCEDURE dbo.Get_seainvno
> @.prefix varchar(20),
> @.countno int,
> @.period varchar(6),
> @.result int output
> AS
> DECLARE @.testing int
> IF @.countno > 0
> BEGIN
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
> print @.result <--I can show
> SELECT @.testing = @.result %100
> print @.testing <----
It
> can't show the variable in SQL analayzer ? Pls help
> IF (@.result % 100) >= 90
> BEGIN
> UPDATE arinvNo
> SET number = number + 75
> WHERE prefix = @.prefix
> END
> ELSE
> BEGIN
> UPDATE arinvNo
> SET number = number + 1
> WHERE prefix = @.prefix
> END
>
>|||On Wed, 27 Apr 2005 13:30:19 +0800, Ray wrote:

>[I got a serious problem on print @.variable, please be kind to take a look]
(snip)
> SELECT number AS result
> FROM arinvNo
> WHERE prefix = @.prefix
>print @.result <--I can show
(snip)
Hi Ray,
I'm not sure why this PRINT shows anything, nor what it shows - but it
won't be the number just retrieved from the arinvNo table.
I think you want this syntax:
SELECT @.result = number
FROM arinvNo
WHERE prefix = @.prefix
(Note the changed SELECT clause - I added the @. to indicate a variable and
changed the syntax to make it an assignment SELECT statement).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

primary keys

"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."

can soemone explain to me why i have this kind of error?

i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!

all the values from of application_input will be stored also in approve_overtime.

sometimes the datas can be stored.sometimes it cannot and produces an error!

what do u think?

hmmm pls help!

Check if you are wanting to insert duplicate id_no into approve_overtime ?

Primary Key Question

If I try to run a script to create a primary key:

ALTER TABLE [dbo].[Drawers]

ADD PRIMARY KEY (ID)

Why does SQL Server name the primary key in this format below? The last section of letters and number changes each time I drop and create a key:

PK__Drawers__1C873BEC

I need a unique name so that I can create one script that will drop and create indexes anywhere. I cannot do this with SQL Server creating a different key name everytime a key is created. How can I resolve this?

Thanks,

cj

If you are wanting to have a fixed primery key, the best thing you can do is name it yourself:

ALTER TABLE [dbo].[Drawers]

ADD CONSTRAINT PK_DRAWERS
PRIMERY KEY (ID)

|||

What if the primary key column, ID does not exist to begin with?

Say I add the column for the primary key as:

ALTER TABLE dbo.TransactionLineItems

ADD ID INTEGER

Then, the ID column will be null and I will not be able to add a primary key to it if it is already null:

ALTER TABLE dbo.TransactionLineItems

ADD ID INTEGER CONSTRAINT PK_TransLineItems_ID IDENTITY PRIMARY KEY

I get the error:

Column names in each table must be unique. Column name 'ID' in table 'dbo.TransactionLineItems' is specified more than once.

|||

So if the column that you wish to use as a Primary Key does not exist, AND if you wish to use an IDENTITY column for that Primary Key, AND you want to definitively name the Primary Key, try this two (2) step process.

Code Snippet

ALTER TABLE dbo.TransactionLineItems

ADD [ID] int IDENTITY

GO

ALTER TABLE [dbo].[Drawers]

ADD CONSTRAINT PK_TransactionLineItems PRIMARY KEY ([ID])
GO

|||

To me if you get the message that you say it means that you are mistaken and in fact that column -- ID -- already DOES exist. Can somebody please check me on this?

|||

Kent,

My understanding is that the error occurred after he first added an [ID] column, and then attempted to add another [ID] column as a PK constraint.

|||Yes, this is the syntax I needed. Thanks!