Must declare scalar variable.

Question

Post reply

Must declare scalar variable.

notwhereuareat

SSC Enthusiast

Points: 153
More actions
July 6, 2011 at 7:34 am

#243921

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.
[font="Courier New"]
--============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[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

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.
[font="Courier New"]
--============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[/font]

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)[/url]

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 3

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)[/url]

notwhereuareat SSC Enthusiast Points: 153 More actions · Answer 4

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.

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

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)[/url]