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