July 6, 2011 at 7:34 am
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]
July 6, 2011 at 7:42 am
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
July 6, 2011 at 7:49 am
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
July 6, 2011 at 7:50 am
July 6, 2011 at 7:59 am
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.
July 6, 2011 at 8:11 am
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy