asked on

Dynamic SQL?

I have this syntax in an SSRS report that creates the dataset. In scouring the internet it looks like this is Dynamic SQL? Even after reading about it though I don't understand what this is doing:

exec ('Select sp_LP_BillingAnalysisAtty1.TypeFreq
      , sp_LP_BillingAnalysisAtty1.ProfName
      , sp_LP_BillingAnalysisAtty1.Initials
      , sp_LP_BillingAnalysisAtty1.WorkedUnits
      , Professionals.ProfClass
      , Professionals.QPROFSTATUS
From ' + @spsp_LP_BillingAnalysisAtty1 + ' as sp_LP_BillingAnalysisAtty1
INNER JOIN Prolaw.dbo.Professionals as Professionals on (sp_LP_BillingAnalysisAtty1.Initials = Professionals.Initials and sp_LP_BillingAnalysisAtty1.ProfName = Professionals.ProfName)
Order by sp_LP_BillingAnalysisAtty1.ProfName
      , sp_LP_BillingAnalysisAtty1.TypeFreq')

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andrei Fomitchev

exec -- executes the string '...' that is in () - dynamic query
('
Select sp_LP_BillingAnalysisAtty1.TypeFreq -- SELECTs the column TypeFreq from the table sp_LP_BillingAnalysisAtty1
      , sp_LP_BillingAnalysisAtty1.ProfName -- SELECTs the column ProfName from the table sp_LP_BillingAnalysisAtty1
      , sp_LP_BillingAnalysisAtty1.Initials -- SELECTs the column Initials from the table sp_LP_BillingAnalysisAtty1
      , sp_LP_BillingAnalysisAtty1.WorkedUnits -- SELECTs the column WorkedUnits from the table sp_LP_BillingAnalysisAtty1 
      , Professionals.ProfClass -- SELECTs the column ProfClass from the table Professionals
      , Professionals.QPROFSTATUS -- SELECTs the column QPROFSTATUS from the table Professionals
From ' -- FROM starts here
 + @spsp_LP_BillingAnalysisAtty1 + -- adds table name from the variable @spsp_LP_BillingAnalysisAtty1
' as sp_LP_BillingAnalysisAtty1 -- assigns alias sp_LP_BillingAnalysisAtty1 that is used as table name
INNER JOIN Prolaw.dbo.Professionals -- joins the table Professionals from DB Prolaw schema dbo
 as Professionals -- assigns the alias Professionals
on (sp_LP_BillingAnalysisAtty1.Initials = Professionals.Initials and sp_LP_BillingAnalysisAtty1.ProfName = Professionals.ProfName) -- join condition
Order by sp_LP_BillingAnalysisAtty1.ProfName
      , sp_LP_BillingAnalysisAtty1.TypeFreq -- orders result set
')

Open in new window