April 30, 2012 at 9:25 am
Greetings...
I am NO SQL expert - good at writing very simple queries - but now I have to do something a bit more extensive.
We are converting .NET (VB) code into SQL Stored Procedures and though I am great at VB.NET code, trying to do the same thing in T-SQL has been like pulling teeth with pliers, hold the novacaine! I have been all over the web to try to learn how to do this simple thing, but even there, most of it produces errors...
Let me describe what we're after in this first part of the task... I have a query that produces good data - one column is a Date (DateTime) column. I need to get the value of the very first record's Date. Lets say its 02/02/2005. I need to then take that date as a start point, and build a summary table that has one record for every month between that date, and now.
Here is what my code looks like (partially) to this point...
DECLARE TempCursor CURSOR FOR
SELECT
Files.FileName,
Files.LastModified,
Files.FileType,
SourceFiles.ImportType,
SourceFiles.ImportID,
FileTypes.Application,
dbo.fn_IsAppType('Access',FileTypes.Application) As IsAccess,
dbo.fn_IsAppType('Excel',FileTypes.Application) As IsExcel,
dbo.fn_IsAppType('Word',FileTypes.Application) As IsWord,
dbo.fn_IsAppType('PowerPoint',FileTypes.Application) As IsPowerPoint
FROM Files
INNER JOIN Groups ON Files.GroupID = Groups.GroupID
INNER JOIN SourceFiles ON Groups.BatchID = SourceFiles.BatchID
INNER JOIN Import ON SourceFiles.ImportID = Import.ImportID
INNER JOIN FileTypes ON Files.FileType = FileTypes.FileExtension
WHERE (Files.FileType in (select * from dbo.fn_Split(@FileType,',')))
AND (Files.LastModified > dateadd(day, -1, CONVERT(DATETIME, @DateFrom, 102)))
AND (Files.LastModified < dateadd(day, 1, CONVERT(DATETIME, @DateTo, 102)))
AND (SourceFiles.ImportDate > dateadd(day, -1, CONVERT(DATETIME, @ImpDateFrom, 102)))
AND (SourceFiles.ImportDate < dateadd(day, 1, CONVERT(DATETIME, @ImpDateTo, 102)))
AND (CHARINDEX(dbo.PADL(SourceFiles.ImportId,2,'0') , @ImportID)) > 0
AND (SourceFiles.ImportType = @ImportType)
ORDER BY Files.LastModified , FileTypes.Application
--====================================================================================================
-- Create the Summary Table
--====================================================================================================
CREATE TABLE SUMMARY (
ModDate DateTime,
RMonth VARCHAR (2) ,
RYear VARCHAR (4) ,
AccessCount INT ,
AccessRunTot INT ,
AccessRunPct Decimal ,
ExcelCount INT ,
ExcelRunTot INT ,
ExcelRunPct Decimal ,
WordCount INT ,
WordRunTot INT ,
WordRunPct Decimal ,
PPointCount INT ,
PPointRunTot INT ,
PPointRunPct Decimal ,
LineTotal Decimal ,
RunTotal Decimal ,
PctOfTot Decimal);
--====================================================================================================
-- Declare the working values
--====================================================================================================
Declare @Cnt_Access As Int, @Cnt_Excel As Int, @Cnt_Word As Int, @Cnt_PPoint As Int
Declare @Rnt_Access As Int, @Rnt_Excel As Int, @Rnt_Word As Int, @Rnt_PPoint As Int, @Rnt_AllFiles As Int
Declare @Pct_Access As Decimal, @Pct_Excel As Decimal, @Pct_Word As Decimal, @Pct_PPoint As Decimal, @Pct_AllFiles As Decimal
Declare @Gtl_Access As Int, @Gtl_Excel As Int, @Gtl_Word As Int, @Gtl_PPoint As Int, @Gtl_AllFiles As Int
Declare @LastModi As DateTime
Declare @KeyMonth As Int, @KeyYear As Int
Declare @CurrApp As Varchar(30)
Declare @StartDate as DateTime
Declare @StartYear as Int
Declare @StartMonth as Int
--====================================================================================================
-- Populate the Summary Table with date ranges
--====================================================================================================
OPEN TEMPCURSOR
FETCH FIRST FROM TEMPCURSOR Into @StartDate
SET @StartMonth = 1
SET @StartYear = YEAR(@StartDate.LastModified) <=- AND THIS IS WHERE THINGS FALL APART...
What I am trying to do here is go to the top of the TempCursor and just read that first record. But no matter how I write this, SQL goes "Huh? What file?"
I tried...
FETCH LastModified FROM TEMPCURSOR INTO @STARTDATE
...Same result - SQL doesnt know what LastModified is...
So I guess my first question (maybe more to come in days ahead) is "How do I reference a column in a Temp Cursor?" Or for that matter, reference any column!
Can someone point me to somewhere I can learn to do this?
Thanks very much!
April 30, 2012 at 9:39 am
Please don't use a cursor for this. . .
WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),
CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(n) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y)
SELECT DATEADD(M,n,modDate)
FROM CTE5
CROSS APPLY (SELECT CAST('2005-02-02' AS DATETIME)) a(modDate)
WHERE DATEADD(M,n,modDate) <= GETDATE();
Returns: -
-----------------------
2005-02-02 00:00:00.000
2005-03-02 00:00:00.000
2005-04-02 00:00:00.000
2005-05-02 00:00:00.000
2005-06-02 00:00:00.000
2005-07-02 00:00:00.000
2005-08-02 00:00:00.000
2005-09-02 00:00:00.000
2005-10-02 00:00:00.000
2005-11-02 00:00:00.000
2005-12-02 00:00:00.000
2006-01-02 00:00:00.000
2006-02-02 00:00:00.000
2006-03-02 00:00:00.000
2006-04-02 00:00:00.000
2006-05-02 00:00:00.000
2006-06-02 00:00:00.000
2006-07-02 00:00:00.000
2006-08-02 00:00:00.000
2006-09-02 00:00:00.000
2006-10-02 00:00:00.000
2006-11-02 00:00:00.000
2006-12-02 00:00:00.000
2007-01-02 00:00:00.000
2007-02-02 00:00:00.000
2007-03-02 00:00:00.000
2007-04-02 00:00:00.000
2007-05-02 00:00:00.000
2007-06-02 00:00:00.000
2007-07-02 00:00:00.000
2007-08-02 00:00:00.000
2007-09-02 00:00:00.000
2007-10-02 00:00:00.000
2007-11-02 00:00:00.000
2007-12-02 00:00:00.000
2008-01-02 00:00:00.000
2008-02-02 00:00:00.000
2008-03-02 00:00:00.000
2008-04-02 00:00:00.000
2008-05-02 00:00:00.000
2008-06-02 00:00:00.000
2008-07-02 00:00:00.000
2008-08-02 00:00:00.000
2008-09-02 00:00:00.000
2008-10-02 00:00:00.000
2008-11-02 00:00:00.000
2008-12-02 00:00:00.000
2009-01-02 00:00:00.000
2009-02-02 00:00:00.000
2009-03-02 00:00:00.000
2009-04-02 00:00:00.000
2009-05-02 00:00:00.000
2009-06-02 00:00:00.000
2009-07-02 00:00:00.000
2009-08-02 00:00:00.000
2009-09-02 00:00:00.000
2009-10-02 00:00:00.000
2009-11-02 00:00:00.000
2009-12-02 00:00:00.000
2010-01-02 00:00:00.000
2010-02-02 00:00:00.000
2010-03-02 00:00:00.000
2010-04-02 00:00:00.000
2010-05-02 00:00:00.000
2010-06-02 00:00:00.000
2010-07-02 00:00:00.000
2010-08-02 00:00:00.000
2010-09-02 00:00:00.000
2010-10-02 00:00:00.000
2010-11-02 00:00:00.000
2010-12-02 00:00:00.000
2011-01-02 00:00:00.000
2011-02-02 00:00:00.000
2011-03-02 00:00:00.000
2011-04-02 00:00:00.000
2011-05-02 00:00:00.000
2011-06-02 00:00:00.000
2011-07-02 00:00:00.000
2011-08-02 00:00:00.000
2011-09-02 00:00:00.000
2011-10-02 00:00:00.000
2011-11-02 00:00:00.000
2011-12-02 00:00:00.000
2012-01-02 00:00:00.000
2012-02-02 00:00:00.000
2012-03-02 00:00:00.000
2012-04-02 00:00:00.000
Applying it to your table, I think you'd be looking at this: -
WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),
CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(n) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y)
SELECT DATEADD(M,n,modDate)
FROM CTE5
CROSS APPLY (SELECT MIN(modDate)
FROM Files) a(modDate)
WHERE DATEADD(M,n,modDate) <= GETDATE();
April 30, 2012 at 9:40 am
You can't reference a column like that. You setup a cursor with its definition and then you get "NEXT" not column names. Instead of
FETCH LastModified FROM TEMPCURSOR INTO @STARTDATE
You should use
FETCH NEXT FROM TEMPCURSOR INTO @STARTDATE
As a side note your code looks to be filled with two of the most poorly performing constructs in t-sql (scalar functions and cursors).
There are times where both of these are the best choice but glancing at your code snippet I don't think this is the best choice for either of those. If you want some help with either of those post some additional info and we can knock it out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 9:41 am
First of all, your fetch into has to have variables for all the columns you have defined in your select list for the cursor. You then use the appropriate variable.
Second, you probably don't need a cursor, but there isn't enough information to really provide you with a set-based alternative.
April 30, 2012 at 9:53 am
Wow...
Thanks for the replies, and the advice. I made the cursor READ_ONLY, presuming that would help improve things - but if you guys are saying "Dont use cursors", that begs the question; "What WOULD I used?" Temp Table? Isnt that really just the same thing in the end? Sorry if I sound novice on that - but please, any suggestion would be most helpful. I am probably a bit beyond Novice into the realm of "knows enough to really hurt himself".
Some say "More information would be helpful". What information do you need? Just ask and I will provide as this has been driving me totally nuts for days now.
I dont know if this will help - but what I am trying to do here is take a "Main" Query (that first piece you see), and then summarize data within it by Month and year. This is why I was thinking to put my "Main" query as a cursor, and then set up a new table (called Summary) and cycle the data twice. First pass, setup the dates (eg, month and year covering the span from first record in the query to 'Now'). Second pass, do the counts and percentages against that cursor.
Please feel free to beat me, whip me, shame me, flog me - whatever - any help you can offer is most appreciated. If this were VB or C#, heck, even FoxPro - I'd have this done in minutes - but T-SQL? My Lord, was it invented just to punish people???
I deeply appreciate the help - please ask if I can provide you any refining info.
April 30, 2012 at 10:40 am
blandry (4/30/2012)
Wow...Thanks for the replies, and the advice. I made the cursor READ_ONLY, presuming that would help improve things - but if you guys are saying "Dont use cursors", that begs the question; "What WOULD I used?" Temp Table? Isnt that really just the same thing in the end? Sorry if I sound novice on that - but please, any suggestion would be most helpful. I am probably a bit beyond Novice into the realm of "knows enough to really hurt himself".
Some say "More information would be helpful". What information do you need? Just ask and I will provide as this has been driving me totally nuts for days now.
I dont know if this will help - but what I am trying to do here is take a "Main" Query (that first piece you see), and then summarize data within it by Month and year. This is why I was thinking to put my "Main" query as a cursor, and then set up a new table (called Summary) and cycle the data twice. First pass, setup the dates (eg, month and year covering the span from first record in the query to 'Now'). Second pass, do the counts and percentages against that cursor.
Please feel free to beat me, whip me, shame me, flog me - whatever - any help you can offer is most appreciated. If this were VB or C#, heck, even FoxPro - I'd have this done in minutes - but T-SQL? My Lord, was it invented just to punish people???
I deeply appreciate the help - please ask if I can provide you any refining info.
We will need the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT INTO statements) for the table(s) involved, expected results based onthe sample data provides (a good way for that is to create a table and load it with data using INSERT INTO statements). Please note, sample data should be just that, sample data. Not real production data. Just enough to be representative of the problem domain and not so much that you can't put together the expected results manually.
Need help with this? Read the first article I reference below in my signature block, it will walk through the steps you need to post the requested info.
April 30, 2012 at 11:17 am
Thanks Lynn...
However, there are no CREATE TABLE statements - this comes out of one of our core Databases, created 10 years ago (or more?) through the interface, not statements. As well, there are no easy "INSERT INTO" statements here. The data comes out of XML files, each read into a block object through our BE (Business Entities) Class, and then passed through to our DALC (Data Access Layer Class). Within the DALC, each table has its own VB.NET code that calls SQL - for example....
Dim oCmd As New SqlCommand
Dim oHash As Hashtable
Try
With oCmd
.CommandTimeout = 0
.CommandText = "usp_cre_AddInsComplete"
End With
Me.BuildParameter(oCmd, "@GroupID", ToDBNull(GroupID, True), ParameterDirection.Input, SqlDbType.NVarChar, 4)
Me.BuildParameter(oCmd, "@Name", ToDBNull(Name), ParameterDirection.Input, SqlDbType.NVarChar, 100)
Me.BuildParameter(oCmd, "@ProgID", ToDBNull(ProgID), ParameterDirection.Input, SqlDbType.NVarChar, 100)
Me.BuildParameter(oCmd, "@ClassID", ToDBNull(ClassID), ParameterDirection.Input, SqlDbType.NVarChar, 100)
Me.BuildParameter(oCmd, "@IsDotNet", ToDBNull(IsDotNet), ParameterDirection.Input, SqlDbType.Bit, 1)
Me.BuildParameter(oCmd, "@FileName", ToDBNull(FileName), ParameterDirection.Input, SqlDbType.NVarChar, 100)
Me.BuildParameter(oCmd, "@FriendlyName", ToDBNull(FriendlyName), ParameterDirection.Input, SqlDbType.NVarChar, 100)
Me.BuildParameter(oCmd, "@Description", ToDBNull(Description), ParameterDirection.Input, SqlDbType.NVarChar, 200)
Me.BuildParameter(oCmd, "@UserName", ToDBNull(UserName), ParameterDirection.Input, SqlDbType.NVarChar, 50)
Me.BuildParameter(oCmd, "@ComputerName", ToDBNull(ComputerName), ParameterDirection.Input, SqlDbType.NVarChar, 50)
Me.BuildParameter(oCmd, "@RegistryPath", ToDBNull(RegistryPath), ParameterDirection.Input, SqlDbType.NVarChar, 200)
Me.BuildParameter(oCmd, "@Version", ToDBNull(Version), ParameterDirection.Input, SqlDbType.NVarChar, 50)
Me.BuildParameter(oCmd, "@CommandLineSafe", ToDBNull(CommandLineSafe), ParameterDirection.Input, SqlDbType.Bit, 1)
Me.BuildParameter(oCmd, "@Comments", ToDBNull(Comments), ParameterDirection.Input, SqlDbType.NVarChar, 255)
Me.BuildParameter(oCmd, "@CompanyName", ToDBNull(CompanyName), ParameterDirection.Input, SqlDbType.NVarChar, 50)
Me.BuildParameter(oCmd, "@FileCreateDate", ToDBNull(FileCreateDate), ParameterDirection.Input, SqlDbType.DateTime, 8)
Me.BuildParameter(oCmd, "@IPAddress", ToDBNull(IPAddress), ParameterDirection.Input, SqlDbType.NVarChar, 25)
Me.BuildParameter(oCmd, "@AddInID", System.DBNull.Value, ParameterDirection.Output, SqlDbType.Int, 4)
oHash = Me.ExecNonQuery(oCmd)
Return Convert.ToInt32(oHash.Item("@AddInID"))
Catch ex As Exception
Throw ex
End Try
There are 46-48 of these in our DALC and BE classes!
Thus simply 'sending' you something means sending you a couple applications, a few libraries, and then ensuring you can run them which means sending you one of our Authorization keys, as well as ensuring you are current on your .NET version, etc etc. And this is not to mention the Non-Disclosure you would have to sign and return... By the time you get that all setup and working, well, I am asking you for free support really, and I dont its fair to do that kind of thing for free. As well, its tempting to hand you code and have you fix it - but really, I need to learn this, not just get handed answers - so let me back peddle this a bit and just ask this simple question:
- If (it seems) everyone says "Dont use Cursors", then what? Temp Tables?
What I mean here is I MUST get the original data 'queried' into something I can then either cycle through, or issue further selects to get to the data I need.
So if not cursors - whats the best/safest/generally recommended element to use?
April 30, 2012 at 12:40 pm
What Lynn meant is that you need to get the create table statements and any other objects (i.e. usp_cre_AddInsComplete). We only care about the sql portion of the process. The article he referenced explains how to collect the information required for questions like these. It does take some legwork on your end but you will be rewarded with tested, fast code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 12:46 pm
Ah, Gotcha...
I got a couple great suggestions and some guideance off another SQL site and I am pursuing that right now. If I get stuck I will try to bundle up something for Lynn, but I have to obfuscate everything these days as a few weeks ago we had code stolen off another site (when one of our guys posted more than he should) and so the company has "locked us down" and now requires NDA's on anything above a Snippet we post or send to any site or support entity. Yeah, I know, silly - but as we compete directly with Microsoft, these are the marching orders.
Let me see how far I can get with these other suggestions - they sound pretty good!
Thanks
April 30, 2012 at 12:54 pm
blandry (4/30/2012)
Ah, Gotcha...I got a couple great suggestions and some guideance off another SQL site and I am pursuing that right now. If I get stuck I will try to bundle up something for Lynn, but I have to obfuscate everything these days as a few weeks ago we had code stolen off another site (when one of our guys posted more than he should) and so the company has "locked us down" and now requires NDA's on anything above a Snippet we post or send to any site or support entity. Yeah, I know, silly - but as we compete directly with Microsoft, these are the marching orders.
Let me see how far I can get with these other suggestions - they sound pretty good!
Thanks
Please note, this isn't just for me. It is for anyone who would like to help.
April 30, 2012 at 1:01 pm
Point taken... Good point too. Let me first try to finish what I am doing now as suggested elsewhere (which seems to be working better) and then we will go from there. If this actually gets the results I want, I will post that too.
Many thanks!
April 30, 2012 at 1:34 pm
The CTE that I posted for you generates the months between a start date and now, was that not what you were after?
It is massively faster than a cursor solution or any form of looping. SQL is set-based, the way you described your problem suggests that this is not how you think when designing solutions. If you don't understand how the CTE works, post back and I'll give some more explanation.
April 30, 2012 at 1:52 pm
Cadavre...
I apologize - yes, the CTE you forwarded is great, sleek, and well, its just getting my head around it that took time. I appreciate the code.
And yes again - I am a Developer and Architect so I have very 'light' SQL knowledge and duties compared to you and the folks up at SSC. I deal more with overall software, Classes, 3rd party dev tools and OS level stuff than with SQL. In fact, that is often the problem with SSC - there is such a wealth of deep knowledge that those of us who just use SQL for well, 'basic' needs and dont spend our lives in it, get blown away by some of what can be seen on SSC.
(I might note there that constantly I hear "dont use cursors" but because I dont know enough, I dont know why, and then am left with the horrible question; "Why did MS build something into SQL Server that all the experts say "Dont use!")
None the less, you are correct that I tend to approach data in more a 'waterfall' type approach than a set based one - and yes, I know I must break that habit but I spend so little time in any complex SQL until the boss walks in and says "I was advised we need to do this in SQL, not in C# or VB.NET (datasets)")
Thank you for the CTE - it is VERY slick and as I incorporate it I will also try to learn it and give you a shout back if I need help. Thanks for that!
FYI too - I dumped the cursor idea and am now gearing the query with Selects - if I get it working in the next day or two, I will post it again to have you guys add your advice.
Again, many thanks!
April 30, 2012 at 2:05 pm
blandry (4/30/2012)
None the less, you are correct that I tend to approach data in more a 'waterfall' type approach than a set based one - and yes, I know I must break that habit but I spend so little time in any complex SQL until the boss walks in and says "I was advised we need to do this in SQL, not in C# or VB.NET (datasets)")
If it's any conciliation, over the last 12 months I've been struggling to go the other way as I've been moved into having a lot more to do with the application layer in my current position.
blandry (4/30/2012)
(I might note there that constantly I hear "dont use cursors" but because I dont know enough, I dont know why, and then am left with the horrible question; "Why did MS build something into SQL Server that all the experts say "Dont use!")
Cursors have their uses, but developers that don't think in a set-bases fashion fall back on them when it's the wrong place to use them. This leads more experienced SQL developers to just blanket "don't use a cursor" in an attempt to force the less experienced SQL developers to think set-based.
blandry (4/30/2012)
FYI too - I dumped the cursor idea and am now gearing the query with Selects - if I get it working in the next day or two, I will post it again to have you guys add your advice.
Chances are reasonably good that a numbers table, calendar table, or CTE in-memory numbers table will perform better.
Unfortunately, without DDL, readily consumable obfuscated sample data and expected results[/url], it's pretty impossible for me to prove it. Good luck with it, sounds like you've been dumped in an interesting situation 😀
April 30, 2012 at 2:09 pm
Cursors serve a roll, but a very small one. Doing things in a set-based manner works faster as that is how SQL Server is built.
For a simplistic approach, think of it this way.
I want to give everyone in the company a 10% raise (yes, we all would love that, wouldn't we).
Procedurally (using a cursor, and just one way of doing so)
declare c cursor for
select
emp_id
from
dbo.employee;
declare @emp_id int;
open c;
fetch next from c into @emp_id;
while @@FETCH_STATUS = 0
begin
update dbo.employee set
salary = salary * 1.1
where
emp_id = @emp_id;
fetch next from c into @emp_id;
end
close c;
deallocate c;
Set-based
update dbo.employee set
salary = salary * 1.1;
Oh, wow. To convert that simple cursor operation to set-based is very easy. Please note, eliminating cursors isn't all ways that easy. Sometimes it takes some work figuring out exactly what is happening inside a cursor-based solution.
Viewing 15 posts - 1 through 15 (of 77 total)
You must be logged in to reply to this topic. Login to reply