"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
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]

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" <[email protected]> wrote in

message news:[email protected]...

 

[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

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" <[email protected]> wrote in

message news:[email protected]...

 

[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" <[email protected]> wrote in

message news:[email protected]...