SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 scalar variable decleration error		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shwetaatul
Starting Member

India
5 Posts
Posted - 03/29/2012 :  06:56:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 03/29/2012 :  07:51:47  Show Profile  Reply with Quote
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 
)
Go to Top of Page

shwetaatul
Starting Member

India
5 Posts
Posted - 03/30/2012 :  02:56:57  Show Profile  Reply with Quote
Hi Sunita,

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

Atul bhardwaj
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 03/30/2012 :  10:00:51  Show Profile  Reply with Quote
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
Go to Top of Page

shwetaatul
Starting Member

India
5 Posts
Posted - 03/31/2012 :  03:01:51  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5501 Posts
Posted - 03/31/2012 :  16:11:20  Show Profile  Reply with Quote
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
Edited by - TG on 03/31/2012 16:12:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000