August 10, 2014 at 2:25 pm
Hello everyone-
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)
,@course_due varchar(100)
,@month_last_taken varchar(100)
SET @intFlag =1
SET @TABLE_NAME='dbO.TD_EmployeeProfile_FinalV2'
SELECT @INTFLAGMAX = ROW_COUNT FROM dbo.FN_COUNT_ROWS (@TABLE_NAME)AS X
WHILE @intFlag <= @INTFLAGMAX
BEGIN
SELECT @EMP_ID = ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID ASC) ROW_NUM FROM dbO.TD_EmployeeProfile_FinalV2) as new WHERE ROW_NUM=@intFlag
SELECT @EMAIL = email FROM (
select vw_sch.person_number as id, vw_sch.fname,vw_sch.email
, vw_sch.manager_name,vw_sch.coursename_new, vw_sch.mgr_email
from
TD_EMPLOYEE_COURSE_SCHEDULE_VW AS VW_SCH
LEFT join TD_EMPLOYEE_COURSE_HIST_VW_xx AS VW_HIS
ON VW_HIS.id=VW_SCH.id
where month_number_due=7
and (month_last_taken_num is null or month_last_taken_num<7)
and vw_sch.person_number=@EMP_ID
) as sel
SELECT @fname = fname FROM (
select vw_sch.person_number as id, vw_sch.fname,vw_sch.email
, vw_sch.manager_name,vw_sch.coursename_new, vw_sch.mgr_email
from
TD_EMPLOYEE_COURSE_SCHEDULE_VW AS VW_SCH
LEFT join TD_EMPLOYEE_COURSE_HIST_VW_xx AS VW_HIS
ON VW_HIS.id=VW_SCH.id
where month_number_due=7
and (month_last_taken_num is null or month_last_taken_num<7)
and vw_sch.person_number=@EMP_ID
) as sel
SELECT @month_last_taken =coursename_new FROM (
select vw_sch.person_number as id, vw_sch.fname,vw_sch.email
, vw_sch.manager_name,vw_sch.coursename_new, vw_sch.mgr_email
,month_last_taken
from
TD_EMPLOYEE_COURSE_SCHEDULE_VW AS VW_SCH
LEFT join TD_EMPLOYEE_COURSE_HIST_VW_xx AS VW_HIS
ON VW_HIS.id=VW_SCH.id
where month_number_due=7
and (month_last_taken_num is null or month_last_taken_num<7)
and vw_sch.person_number=@EMP_ID
) as sel
SELECT @course_due =month_last_taken FROM (
select vw_sch.person_number as id, vw_sch.fname,vw_sch.email
, vw_sch.manager_name,vw_sch.coursename_new, vw_sch.mgr_email
from
TD_EMPLOYEE_COURSE_SCHEDULE_VW AS VW_SCH
LEFT join TD_EMPLOYEE_COURSE_HIST_VW_xx AS VW_HIS
ON VW_HIS.id=VW_SCH.id
where month_number_due=7
and (month_last_taken_num is null or month_last_taken_num<7)
and vw_sch.person_number=@EMP_ID
) as sel
EXEC msdb.dbo.Sp_send_dbmail
@recipients =@email,
@subject = 'Compliance Overdue',
@importance= 'high',
@body = ' Dear '+ @fname +',
We realize you may have taken ' + @course_due + ' in '+@month_last_taken +'. In order to align with the new compliance calendar you will need to complete the following course(s) this month. '
SET @intFlag = @intFlag + 1
END
GO
August 10, 2014 at 2:30 pm
One way to assign more than one value to a variable is to use a table variable.
But the major issue with your code is slightly different: you're using the very same question again and again to assign a single variable.
Here's a way to assign values to more than one variable with one SELECT:
SELECT @EMAIL = email,
@fname = fname,
@month_last_taken =coursename_new
FROM (
select vw_sch.person_number as id, vw_sch.fname,vw_sch.email
, vw_sch.manager_name,vw_sch.coursename_new, vw_sch.mgr_email
from
TD_EMPLOYEE_COURSE_SCHEDULE_VW AS VW_SCH
LEFT join TD_EMPLOYEE_COURSE_HIST_VW_xx AS VW_HIS
ON VW_HIS.id=VW_SCH.id
where month_number_due=7
and (month_last_taken_num is null or month_last_taken_num<7)
and vw_sch.person_number=@EMP_ID
Viewing 2 posts - 1 through 2 (of 2 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