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

J

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.
 
S

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.
 
B

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)
 
J

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]...
 
S

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]...
 
J

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]...
 
Joined
Nov 5, 2009
Messages
1
Reaction score
0
Must declare the scalar variable error:

Hi i am raghu ..I am getting the following error while executimng the procedure for list of values >

Like : Must declare the scalar variable "@Status"

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_OrderUpdateStatus]
(
@OrderList varchar(500),
@Status varchar(50)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'UPDATE [Orderhdr] SET
Status = @Status Where OrderNumber in (+ @OrderList + )'
EXEC(@SQL)
END
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top