"Must declare the scalar variable @Nickname"...

[Jiggaz]

Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

[Scott G.]

You might have better luck with this question in the sqlserver groups (microsoft.public.sqlserver.programming). In the meantime, why not let Transact-SQL do some of the work for you

IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname) BEGIN
-- the user is there
END
ELSE BEGIN
-- the user is not there
END

Scott
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl...
Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

[bruce barker]

dynamic sql runs in its own context (think of it as a sub call), so no
variables declared in the caller are valid in the dynamic statement. i have
no idea why you used dynamic sql or declared @checkforexistingrecord as a
varchar.

try:

declare @checkForExistingRecord int
if exists (select * from users where nickname = @usernick)
set @checkForExistingRecord = 1
else
set @checkForExistingRecord = 0
-- bruce (sqlwork.com)
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message
news:2a*****************************@phx.gbl...

Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

[Jiggaz]

i have done that :
________________
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)
BEGIN
-- the user is there
RETURN -1
END
ELSE BEGIN
insert into users values (@Nickname , @Password ,
@Email , @Date, @Name, @Lastname, @Sexe,
@Titre, @Adress, @Birthday, @Phonetel, @Mobilephone,
@Website, 50, '', '', '', '', @MailsAllowed)
RETURN 1
END
___________________

But, always return me -1 but, it's good: if user is
already in the table, sql doesn't add him and if not
exists, add! But always return me in msg box -1?
How to get if it hsn't been added to show a message : good
or error if already in base?

Thanks. Regards.

-----Original Message-----
You might have better luck with this question in the sqlserver groups (microsoft.public.sqlserver.programming).
In the meantime, why not let Transact-SQL do some of the
work for you
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname) BEGIN -- the user is there
END
ELSE BEGIN
-- the user is not there
END

Scott
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl... Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

[Scott G.]

I'm not exactly sure what you are asking; are you saying that your sproc is always returning -1? If you think the SELECT * FROM users... should be "failing" try to return a count or debug the sproc. You could also run put some SQL PRINT statements in you sproc and run it from QueryAnalyzer if you need to troubleshoot.

Scott

"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl...
i have done that :
________________
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)
BEGIN
-- the user is there
RETURN -1
END
ELSE BEGIN
insert into users values (@Nickname , @Password ,
@Email , @Date, @Name, @Lastname, @Sexe,
@Titre, @Adress, @Birthday, @Phonetel, @Mobilephone,
@Website, 50, '', '', '', '', @MailsAllowed)
RETURN 1
END
___________________

But, always return me -1 but, it's good: if user is
already in the table, sql doesn't add him and if not
exists, add! But always return me in msg box -1?
How to get if it hsn't been added to show a message : good
or error if already in base?

Thanks. Regards.

-----Original Message-----
You might have better luck with this question in the sqlserver groups (microsoft.public.sqlserver.programming).
In the meantime, why not let Transact-SQL do some of the
work for you
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname) BEGIN -- the user is there
END
ELSE BEGIN
-- the user is not there
END

Scott
"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl... Hi,

Look my stored procedure :
__________________
ALTER PROCEDURE dbo.CreateAccount

@Nickname varchar(30),
@Password varchar(15),
@Email varchar(50),
@Date datetime,
@Name varchar(50),
@Lastname varchar(50),
@Sexe varchar(2),
@Titre varchar(15),
@Adress varchar(255),
@Birthday varchar(50),
@Phonetel varchar(50),
@Mobilephone varchar(50),
@Website varchar(255),
@MailsAllowed varchar(2)

AS

DECLARE @insertstring nchar(4000)
DECLARE @checkforexistingrecord VARCHAR(60)
DECLARE @add VARCHAR(2000)
DECLARE @usernick VARCHAR(30)

SET @usernick = @Nickname

SET @insertstring = 'DECLARE @checkforexistingrecord
VARCHAR(60)
SELECT nickname FROM users WHERE nickname = '''
+ @usernick + ''''
EXEC(@insertstring)
SELECT @checkforexistingrecord = @@ROWCOUNT
__________________

Why i get this error?
I just want to verify if the username already exists..

Thanks. Regards.

[Jiggaz]

No, what's failing is the stored procedure : it doesn't
return me 1 when it adds the user. Always return me "-1"...

Why? If there is an user it has to return me -1 but when it
adds must not..

How can i return a value depending on the result of INSERT
which could be get, after, by asp.net and show a message to
the user if his account has or hasn't been created?

Thanks.

-----Original Message-----

I'm not exactly sure what you are asking; are you saying that your sproc is always returning -1? If you think the
SELECT * FROM users... should be "failing" try to return a
count or debug the sproc. You could also run put some SQL
PRINT statements in you sproc and run it from QueryAnalyzer
if you need to troubleshoot.
Scott

"Jiggaz" <an*******@discussions.microsoft.com> wrote in message news:2a*****************************@phx.gbl... i have done that :
________________
IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)
BEGIN
-- the user is there
RETURN -1
END
ELSE BEGIN
insert into users values (@Nickname , @Password ,
@Email , @Date, @Name, @Lastname, @Sexe,
@Titre, @Adress, @Birthday, @Phonetel, @Mobilephone,
@Website, 50, '', '', '', '', @MailsAllowed)
RETURN 1
END
___________________

But, always return me -1 but, it's good: if user is
already in the table, sql doesn't add him and if not
exists, add! But always return me in msg box -1?
How to get if it hsn't been added to show a message : good
or error if already in base?

Thanks. Regards.

>-----Original Message-----
>You might have better luck with this question in the

sqlserver groups (microsoft.public.sqlserver.programming).
In the meantime, why not let Transact-SQL do some of the
work for you

>
>IF EXISTS (SELECT * FROM users WHERE nickname = @Nickname)

BEGIN

> -- the user is there
>END
>ELSE BEGIN
> -- the user is not there
>END
>
>Scott
> "Jiggaz" <an*******@discussions.microsoft.com> wrote in

message news:2a*****************************@phx.gbl...

> Hi,
>
> Look my stored procedure :
> __________________
> ALTER PROCEDURE dbo.CreateAccount
>
> @Nickname varchar(30),
> @Password varchar(15),
> @Email varchar(50),
> @Date datetime,
> @Name varchar(50),
> @Lastname varchar(50),
> @Sexe varchar(2),
> @Titre varchar(15),
> @Adress varchar(255),
> @Birthday varchar(50),
> @Phonetel varchar(50),
> @Mobilephone varchar(50),
> @Website varchar(255),
> @MailsAllowed varchar(2)
>
> AS
>
> DECLARE @insertstring nchar(4000)
> DECLARE @checkforexistingrecord VARCHAR(60)
> DECLARE @add VARCHAR(2000)
> DECLARE @usernick VARCHAR(30)
>
> SET @usernick = @Nickname
>
> SET @insertstring = 'DECLARE @checkforexistingrecord
> VARCHAR(60)
> SELECT nickname FROM users WHERE nickname = '''
> + @usernick + ''''
> EXEC(@insertstring)
> SELECT @checkforexistingrecord = @@ROWCOUNT
> __________________
>
> Why i get this error?
> I just want to verify if the username already exists..
>
> Thanks. Regards.