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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: bruce barker (nospam_brubar_at_safeco.com)
Date: 07/09/04 

Date: Fri, 9 Jul 2004 09:11:44 -0700

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" <anonymous@discussions.microsoft.com> wrote in message
news:2a52b01c465a7$a1ef12c0$a301280a@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.

Relevant Pages

  • Re: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
  • Re: Turning rows into columns.
    ... You might try something like Dynamic SQL (I just found out how powerful this ... DECLARE @i INT ... WHILE @i <= COUNTFROM dtblRowColumns ... > stored procedure, it might find 5 rows of data. ...
    (microsoft.public.sqlserver.programming)
  • Cursor based loop not working properly
    ... I am having trouble with getting a variable in a loop to update. ... Then I am doing an exec on a dynamic sql statement for each of these tables. ... why my @recordCount is not getting populated. ... DECLARE @numTablesWithProtseg VARCHAR ...
    (microsoft.public.sqlserver.programming)
  • Re: VARCHAR2 Length
    ... need to declare a variable longer than 4000, ... need to declare a variable and continue to concatenate strings onto it ... If you need dynamic SQL statements longer than 32767 characters you will need to ...
    (comp.databases.oracle.misc)
  • Re: Dynamic SQL withing UDF
    ... You can't use dynamic SQL in a UDF, ... "Keith Harris" wrote in message ... DECLARE names_cur CURSOR READ_ONLY ... > FETCH NEXT FROM names_cur INTO @name ...
    (microsoft.public.sqlserver.programming)