If Exists Statement In Stored Procedure

Hello all!

Newbie question:

There appears to be something wrong with this syntax in SQL Server 2000:

CREATE PROCEDURE spAddNewUser

@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT

AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

I can't get past the last "if exists" statement, without getting a syntax error when checking syntax. I get "ERROR: Incorrect Syntax near ')'.

I'm sure it's a very simple mistake...

Thanks in advance for any help :-)I think the IF statement is expecting some more code.

if i add the code

begin
print 'yes'
end

after your code then i don't get any parse errors.

What extra code do you want to put in, as your stored procedure does not do anything at the mo :confused:|||I don't know if the code you have shown is what you have, but if it is you are missing the brackets around the paramter list.

CREATE PROCEDURE spAddNewUser
(
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
)
AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

Also don't forget to use BEGIN and END if you need to run a block of code when the IF statment is true.|||Try this way:

CREATE PROCEDURE spAddNewUser
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
AS
IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)
select 'exists!'
ELSE
select 'not exists!'|||all of you are correct...I guess it was just waiting for more info (what happens AFTER the IF EXISTS statement).

I just went ahead and completed the code and it was fine.

<blush>

:-)

Identity seed lost...?

Dear all,
The last value I see for a identity field is 174. That's fine.
But the next value after insert which appears is 217 instead of 175. How do
I force the sequence 'natural' again'
I suppose that it happen due to I deleted some rows...
I would need in order to add a new row into a another table.
Thanks in advance,
EnricEnric
SET IDENTITY_INSERT
Be aware that an IDENTITY property may have gaps as well , and if it is
important , you can change to the natural key and add value to maximal key
SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How
> do
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||Thanks for your post, anyway I will not know which will be the next value in
case I delete some rows.
"Uri Dimant" wrote:
> Enric
> SET IDENTITY_INSERT
> Be aware that an IDENTITY property may have gaps as well , and if it is
> important , you can change to the natural key and add value to maximal k
ey
> SELECT COALESCE(max(col),0)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
>
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:61187E6F-DC85-4E17-B114-11283BD50B64@.microsoft.com...
>
>|||Try this ...
DBCC CHECKIDENT(tablename, RESEED, 0)
DBCC CHECKIDENT(tablename, RESEED)
"Enric" wrote:

> Dear all,
> The last value I see for a identity field is 174. That's fine.
> But the next value after insert which appears is 217 instead of 175. How d
o
> I force the sequence 'natural' again'
> I suppose that it happen due to I deleted some rows...
> I would need in order to add a new row into a another table.
> Thanks in advance,
> Enric
>|||"Enric" wrote:
> Thanks for your post, anyway I will not know which will be the next value
in
> case I delete some rows.
> "Uri Dimant" wrote:
If you want a gapless sequence then IDENTITY is the wrong solution. Don't
use IDENTITY in a way that has meaning for your users precisely because you
can't always control the IDENTITY value. IDENTITY is intended to be used as
an artificial surrogate key only.
Why do you need an IDENTITY column and why do you care if the sequence has
gaps?
David Portas
SQL Server MVP
--

Identity field settings not copied by Import Wizard (2005)

It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column

[ref] [int] IDENTITY ( 1 , 1 ) NOT NULL,

When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be

[ref] [int] NOT NULL,

instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column.

-Eric

Hi Eric,

Did you ever find a solution to this? Do you know if this is due to a bug that is being addressed in sp1? Thanks.

Ryan|||

I too have noticed this problem. I've installed SP1 and ticked the relevant Identity box but still does not create the Identity Columns.

Please help or sugest a workaround.

Thanks

Mark

|||If these is a copy of a database from one SQl Server to another, then I suggest looking at using the Copy Database Wizard instead of the Import/Export Wizard. The problem you might be hitting is that the IE wizard is generic, it works with any source/dest via OLE DB/ODBC and I am pretty sure that the identity property is not reported through those APIs. If you use Copy Database Wizard it is SQL to SQL only and hence can be more aware.|||If the target DB is SQL 2000, Copy Database is no good.|||

Did you find any way to copy the identity columns from Sql 2005 to Sql 2000?

Need help ASAP

Thanks

Black.

Identity field settings not copied by Import Wizard (2005)

It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column

[ref] [int] IDENTITY ( 1 , 1 ) NOT NULL,

When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be

[ref] [int] NOT NULL,

instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column.

-Eric

Hi Eric,
Did you ever find a solution to this? Do you know if this is due to a bug that is being addressed in sp1? Thanks.
Ryan
|||

I too have noticed this problem. I've installed SP1 and ticked the relevant Identity box but still does not create the Identity Columns.

Please help or sugest a workaround.

Thanks

Mark

|||If these is a copy of a database from one SQl Server to another, then I suggest looking at using the Copy Database Wizard instead of the Import/Export Wizard. The problem you might be hitting is that the IE wizard is generic, it works with any source/dest via OLE DB/ODBC and I am pretty sure that the identity property is not reported through those APIs. If you use Copy Database Wizard it is SQL to SQL only and hence can be more aware.|||If the target DB is SQL 2000, Copy Database is no good.
|||

Did you find any way to copy the identity columns from Sql 2005 to Sql 2000?

Need help ASAP

Thanks

Black.

Identity field settings not copied by Import Wizard (2005)

It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column

[ref] [int] IDENTITY ( 1 , 1 ) NOT NULL,

When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be

[ref] [int] NOT NULL,

instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column.

-Eric

Hi Eric,
Did you ever find a solution to this? Do you know if this is due to a bug that is being addressed in sp1? Thanks.
Ryan
|||

I too have noticed this problem. I've installed SP1 and ticked the relevant Identity box but still does not create the Identity Columns.

Please help or sugest a workaround.

Thanks

Mark

|||If these is a copy of a database from one SQl Server to another, then I suggest looking at using the Copy Database Wizard instead of the Import/Export Wizard. The problem you might be hitting is that the IE wizard is generic, it works with any source/dest via OLE DB/ODBC and I am pretty sure that the identity property is not reported through those APIs. If you use Copy Database Wizard it is SQL to SQL only and hence can be more aware.|||If the target DB is SQL 2000, Copy Database is no good.
|||

Did you find any way to copy the identity columns from Sql 2005 to Sql 2000?

Need help ASAP

Thanks

Black.

Identity field settings not copied by Import Wizard (2005)

It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column

[ref] [int] IDENTITY ( 1 , 1 ) NOT NULL,

When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be

[ref] [int] NOT NULL,

instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column.

-Eric

Hi Eric,
Did you ever find a solution to this? Do you know if this is due to a bug that is being addressed in sp1? Thanks.
Ryan
|||

I too have noticed this problem. I've installed SP1 and ticked the relevant Identity box but still does not create the Identity Columns.

Please help or sugest a workaround.

Thanks

Mark

|||If these is a copy of a database from one SQl Server to another, then I suggest looking at using the Copy Database Wizard instead of the Import/Export Wizard. The problem you might be hitting is that the IE wizard is generic, it works with any source/dest via OLE DB/ODBC and I am pretty sure that the identity property is not reported through those APIs. If you use Copy Database Wizard it is SQL to SQL only and hence can be more aware.|||If the target DB is SQL 2000, Copy Database is no good.
|||

Did you find any way to copy the identity columns from Sql 2005 to Sql 2000?

Need help ASAP

Thanks

Black.

identity and order-by in select-intos

I have been using identity() and an order-by clause to create an ordering based on some table values. It appears to work, but can this be relied upon for large tables?

The helpfiles don't say whether identity() honors the order-by clause.

(I have a larger, more complex query in which it appears not be behaving in this manner)

drop table jim

truncate table fred
insert into fred values(50)
insert into fred values(10)
insert into fred values(40)
insert into fred values(20)
insert into fred values(30)

select
identity (int, 1,1) as order_id,
*
into jim
from fred
order by value

select * from jim order by order_id

order_id value
---- ----
1 10
2 20
3 30
4 40
5 50

(5 row(s) affected)What is the behavior of the other query you are having problems with ?|||RE:
Q1 It appears to work, but can this be relied upon for large tables?
A1 My guess is that statements within a transaction or stored procedure in the same scope using identity() may potentially allow gaps (skipped ID numbers) or apparently "inexplicable" insertion failures and rollbacks to occur. {Somewhat similar to the sorts of issues seen occasionally when carelessly using @.@.Identity.} For example: Consider an insert on a target table with an identity column that triggers related inserts to audit tables (which also have identity values). Calling @.@.IDENTITY after the statement and using its value for may sometimes result in "unexpected" behavior, as it will reflect the identity value of one of the audit table identities (instead of the last identity value of the target).

RE:
Q2 I have been using identity() and an order-by clause to create an ordering based on some table values.
Q3 (I have a larger, more complex query in which it appears not be behaving in this manner)

A2 The Identity () function is really probably meant to provide unique column row values only (in any event, relational tables aren't normally ordered per se). Therefore, based on that, I would be very hesitant to attempt to use the Identity () function (and rely on its apparent behavior) to create some sort of artificial table ordering.

A3 How is it "not be behaving in this manner"? Have you considered simply clustering the table on whatever you want the table ordered by (if that is the nature of the required result)?