Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Must declare scalar variable. Expand / Collapse
Author
Message
Posted Wednesday, July 6, 2011 7:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 8, 2011 3:29 PM
Points: 3, Visits: 9
Hello,

My goal is to extract a value from a table and then put it in a temp table
A: When I run the code below
R: I get the following error "Must declare the scalar variable "@prikey".

Any ideas why it says I must declare @prikey on the line # that I actually already declared it on.




--============Declare a couple variables=====================
declare @prikey varchar(36);
declare @lname varchar(60);


--============Assign a variables a value==================
set @prikey = select cast (person_id AS varchar(36))
from person
where first_name = 'bob'
and last_name = 'smith'
);
set @lname = (select last_name
from person
where first_name = 'bob'
and last_name = 'smith'
);

--===============Use the variables======================
create table #temp (x varchar(36) primary key, last_name varchar(60) )
INSERT INTO #temp VALUES (@prikey, @lname)

select * from #temp
Post #1137266
Posted Wednesday, July 6, 2011 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 12,898, Visits: 32,108
run as a batch, i saw what you pasted was missing an open parenthesis, but otherwise just fine.
i suspect it's a highlight-and-F5 issue.
are you highlighting, say each section one part at a time,a dn when you run the code that is selected, it raises the error?


for the code you pasted , ALL of the code must be highlighted and run together.
--============Declare a couple variables=====================
declare @prikey varchar(36);
declare @lname varchar(60);


--============Assign a variables a value==================
set @prikey = (select cast (person_id AS varchar(36))
from person
where first_name = 'bob'
and last_name = 'smith'
);
set @lname = (select last_name
from person
where first_name = 'bob'
and last_name = 'smith'
);

--===============Use the variables======================
create table #temp (x varchar(36) primary key, last_name varchar(60) )
INSERT INTO #temp VALUES (@prikey, @lname)

select * from #temp



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1137272
Posted Wednesday, July 6, 2011 7:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,116, Visits: 6,438
notwhereuareat (7/6/2011)
Hello,

My goal is to extract a value from a table and then put it in a temp table
A: When I run the code below
R: I get the following error "Must declare the scalar variable "@prikey".

Any ideas why it says I must declare @prikey on the line # that I actually already declared it on.




--============Declare a couple variables=====================
declare @prikey varchar(36);
declare @lname varchar(60);


--============Assign a variables a value==================
set @prikey = select cast (person_id AS varchar(36))
from person
where first_name = 'bob'
and last_name = 'smith'
);
set @lname = (select last_name
from person
where first_name = 'bob'
and last_name = 'smith'
);

--===============Use the variables======================
create table #temp (x varchar(36) primary key, last_name varchar(60) )
INSERT INTO #temp VALUES (@prikey, @lname)

select * from #temp


Are you sure this is the exact code that gives you the error? When I run it I get a totally different error, the reason being that there is an open bracket missing after the equal sign in

set @prikey = cast (person_id AS varchar(36))

Just change this to

set @prikey = ( select cast (person_id AS varchar(36))

But let me ask you, why are you first extracting two values from the person table into two variables and then putting them into the #temp table? The following will do as well:

create table #temp (x varchar(36) primary key, last_name varchar(60) )

INSERT INTO #temp ( x, last_name )
SELECT
CAST (person_id AS varchar(36)),
last_name
FROM
person
WHERE
first_name = 'bob' AND
last_name = 'smith'

select * from #temp

Regards,
Jan


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1137287
Posted Wednesday, July 6, 2011 7:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,116, Visits: 6,438
Darn, Lowell beat me to it.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1137288
Posted Wednesday, July 6, 2011 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 8, 2011 3:29 PM
Points: 3, Visits: 9
Thanks Jan and Lowell for the help,

I must have made a minor change to the code before I copied and pasted it, but yes I got a totally different error msg... I will try to be more careful.

To answer your questions I am just slowly building up my skills in SQL.... This is just a test query to see how to transfer data. Thanks for the more efficient route.



Post #1137304
Posted Wednesday, July 6, 2011 8:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:03 AM
Points: 2,116, Visits: 6,438
You are welcome. After all, that's what we are all here for. And thanks for the feedback, that's always appreciated.

BTW, I like your nick!

Regards,
Jan


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1137315
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse