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