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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply