scalar variable decleration error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

scalar variable decleration error

Author

Topic

shwetaatul
Starting Member

5 Posts

Posted - 2012-03-29 : 06:56:16

I have created the below table.
create table student(id int, F_Name nvarchar(64), L_Name nvarchar(64),Email nvarchar(64))

I want to run a stored procedure here and want to store the value in output paramater. But I am getting the below error in Bracket. While stored procedure complied successfully. Please check and let me know where I am worng?

create procedure student_rec3

(
@id int,
@stud_f_name2 varchar(64) output
)
as
Begin
select @stud_f_name2=f_name+' '+L_name from student where ID=@id
End

exec student_rec3 4, @stud_f_name2 output
(But when I tried to run this query, I get the error like “PLEASE DECLARE @STUD_F_NAME AS A SCALAR VARIABLE”)

For output, running the below query –

Select @stud_f_name2

Atul bhardwaj

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-29 : 07:51:47

The easiest way to figure out the syntax of how to call the stored procedure if you are not sure is to right click on the stored proc name in object explorer in SSMS, and select Script Procedure as -> Execute To -> New Query Editor window. That will give you a window with all the right declarations etc. and you just have to set the input variables. So it would be something like this:

DECLARE @RC int
DECLARE @id int
DECLARE @stud_f_name2 varchar(64)

SET @id = 11;

EXECUTE @RC = dbo.student_rec3
   @id
  ,@stud_f_name2 OUTPUT

SELECT @stud_f_name2;

One additional thing you may want to do is to add a default value of NULL to the @stud_f_name2 parameter

create procedure student_rec3

(
@id int, 
@stud_f_name2 varchar(64) = NULL output 
)

shwetaatul
Starting Member

5 Posts

Posted - 2012-03-30 : 02:56:57

Hi Sunita,

I declared the variable like @stud_f_name2 varchar(64) = NULL output
But still getting the same error.

Atul bhardwaj

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-30 : 10:00:51

Not sure what the problem is. I recreated your stored proc (although with just dummy data) and ran it, and it seems to run fine. Compare your code against this - or post the stored proc, and the query you are running:

-- Stored proc
CREATE PROCEDURE student_rec3(@id INT, @stud_f_name2 VARCHAR(64) OUTPUT)
AS
BEGIN
	SELECT
		@stud_f_name2 = 'Jane Doe'
END 

-- Query
DECLARE @RC int
DECLARE @id int
DECLARE @stud_f_name2 varchar(64)

SET @id = 11;

EXECUTE @RC = dbo.student_rec3
   @id
  ,@stud_f_name2 OUTPUT;
  
SELECT @stud_f_name2

shwetaatul
Starting Member

5 Posts

Posted - 2012-03-31 : 03:01:51

Hi,

Thank you. But i execute your code and got the error like "Please Declare @RC as a scalar variable" Can you please check.

Atul bhardwaj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-03-31 : 16:11:20

For Sunitabeck's code to run correctly you need to put a "GO" before the "-- Query". The code to create the SP will include all the code until a batch is terminated. That would either be from a "GO" or if you execute just highlighted portion of the code.

You will get that error if you try to execute you SP without declaring your local version of the SP's output variable.
I like to explicitly call SPs with the @parameter = @localVariable syntax just for clarity sake. You may be confused by the local variable named the same as the procedure parameter.
so:


GO
CREATE PROCEDURE student_rec3(@id INT, @stud_f_name2 VARCHAR(64) OUTPUT)
AS
BEGIN
	SELECT
		@stud_f_name2 = 'Jane Doe'
END 

GO

-- Query
DECLARE @RC int
DECLARE @id int
DECLARE @myName varchar(64)

SET @id = 11;

EXECUTE @RC = dbo.student_rec3
   @id = @id
  ,@stud_f_name2 = @myName OUTPUT;
  
SELECT @myName as [stud_f_name2]

go
drop proc student_rec3

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources