CODEWITHSUNDEEP
sql-serverfunctionstored-proceduresdynamic-sql
Schwimms
Schwimms

Reputation: 85

Create report using dynamic SQL

I have a table example as such:

State   Project Build Type  ACTUAL0     ACTUAL1     ACTUAL2  
------- ------- ----------- ----------- ----------- ----------
Ohio    154214  Residential 1/5/2013    2/25/2013   7/12/12  
Utah    214356  Commercial  7/08/13     6/9/13      7/1/12

I am trying to create a report that takes the column headers beginning with the word actual and get a count of how many dates are less than a specific date. I have a temp table that I create of the column headers beginning with the word actual. This is just and example, there are over 250 columns name actual. So the table looks like this:

MilestoneNmbr  
-------------
ACTUAL1  
ACTUAL2  
ACTUAL3

Now what I think would work is to take the row as a variable for the column header and pass in a date into a function. Here is a function I created:

CREATE FUNCTION [dbo].[GetMSActualCount] 
(
    @ACTUAL nvarchar(16),
    @DATE nvarchar(16)
)
RETURNS int
AS
BEGIN
DECLARE @ACTUALRETURN int
    DECLARE @SQL nVarchar(255) = 
    'SELECT COUNT(' + @ACTUAL + ') AS Expr1
    FROM [CASPR_MILESTONES_000-036]
    WHERE '+ @ACTUAL +' > ' + @DATE
exec sp_executesql @SQL, N''
SET @ACTUALRETURN = @SQL
    -- Return the result of the function
    RETURN @ACTUALRETURN

END

If I run the following query:

DECLARE @DATE varchar(20)
SET @DATE = '''1/1/2013'''
SELECT MilestoneNmbr, dbo.getMSActualCount(milestonenmbr,@Date) from #List_CASPR_Milestones

So my error is that I can't use dynamic SQL in a function. With that being so, what can I do? My easy query here I think will turn into hundreds of lines. Is there another easy way I can do this?

EDIT:

My results I am looking for is something like this:

MilestoneNmbr   CountofDate  
--------------- ------------
ACTUAL1         200  
ACTUAL2         344  
ACTUAL3         400

Upvotes: 2

Views: 1440

Answers (3)

Schwimms
Schwimms

Reputation: 85

To solve this I created a table housing the ACTUAL dates. I then went and looped through each row in the List_ACTUAL table to get the names and select the count of the dates names greater than the variable I pass in. I will be converting this to a PROC. This is how:

DECLARE @MS nvarchar(16)
DECLARE MSLIST CURSOR LOCAL FOR SELECT MilstoneNmbr FROM List_ACTUAL
DECLARE @SQL nvarchar(max)
DECLARE @DATE nvarchar(16)
SET @DATE = '1/1/2013'
CREATE #TEMP (Milestones nvarchar(16), Frozen int)
OPEN MSLIST
FETCH NEXT FROM MSLIST INTO @MS
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @SQL = 'INSERT INTO #TEMP VALUES (''' +@MS+ ''', (Select count(' +@MS+ ') FROM PROJECTDATA WHERE ' +@MS+ ' > ''' + @Date + '''))'
EXEC sp_executesql @SQL, N''
FETCH NEXT FROM MSLIST INTO @MS
END
CLOSE MSLIST
DEALLOCATE MSLIST

Hope this helps someone.

Upvotes: 0

Rostol
Rostol

Reputation: 209

I agree 100% with Charles. If you CAN change the structure this is what I would do:

If possible have a build type table (ID/Build Type), don't have text columns unless you need them as text for something. Anything that can be coded, code it.

The two tables:

  • project header (Proj_ID (long_int)/State (int or char(2)) / build_type (int)), primary key either Proj_id by itself or a new ID if its not unique (as a PK Proj_id & State would not be too useful as a PK).
  • Project_date (Proj_ID (same as PK above) / Date_ID (int) / Actual_Date (DateTime))

So your second example would be:

Project_Header:

214356 / UT / 2  (being 1 Residential, 2 Commercial, 3 Industrial ...)

Project_Date:

 214356 / 0 / '07/08/13'
 214356 / 1 / '06/09/13'
 214356 / 2 / '07/01/12'

Latest build date by project would be: 

Select 'Actual_date' 
from Project_date 
where Project_id='nnn' 
order by date_id DESC 
Limit 1;

Your query would be something like (if the dates are in incremental order):

Select Project_id, max(Date_id)
From Project_date
Group by Project_id
having Actual_date < @date

you can see it's pretty straight forward.

If you CAN'T change the structures but you CAN make new tables I would make an SP that takes that ugly table and generates the Project_Date x times per day ( or you could even tie it to a trigger on inert/update of the first table) and the Project_header once per day (or more often if needed). This would take considerably less time and effort than what you are attempting, plus you could use it for other queries.

Upvotes: 2

Charles Kincaid
Charles Kincaid

Reputation: 41

You are right you can't use dynamic SQL in a function. There are two answers:

First your table with 250 columns ACTUAL plus a number is a nightmare. You can't use any of the built in stuff that SQL does well to help. You should have two tables. First a projects table that has an ID column plus columns for State, Project, and BuildType. Then a table of ProjectDates with a ProjectID column that references the first table and then a column for ActualDate. Reporting from that should be easy.

Given that you probably can't fix the structure try writing a stored procedure. That can use dynamic SQL. Event better is that your stored procedure can create temp tables like above and then use them to do statistics.

Upvotes: 2

Related Questions