It is difficult for me to write this particular article and I’ll tell you why. If you don’t care then just skip down to the example, but here goes anyway. It is very rare that you should have to perform looping in SQL. There are certain situations that do require it, and they are mostly back-end or maintenance related. Ok, now that I got that off my chest, here you go. In order to perform an execution which iterates performing a “for each row”, this is the easiest way for me: [cc lang=”sql”] SELECT RowNum = ROW_NUMBER() OVER(ORDER BY CustomerID) ,* INTO #Customers FROM SalesLT.Customer DECLARE @MaxRownum int SET @MaxRownum = (SELECT MAX(RowNum) FROM #Customers) DECLARE @Iter int SET @Iter = (SELECT MIN(RowNum) FROM #Customers) WHILE @Iter
Continue reading ...
This error is pretty self explanatory. If you are here, I’m sure you simply want to know how to correct it, so here goes. The user context in which you are executing does not have the rights to execute the procedure or function. So first, in order to find out which user you are, simply execute the following query from the connection that is failing. [cc lang=”sql”] SELECT CURRENT_USER [/cc] Now that you know this, you need to login as a user that has the rights to change the execute permissions. Though it is not likely this same user that cannot execute the procedure also has the rights to change the permissions, you can still try. Otherwise simply login as a user with ‘dbo’ access and execute this: [cc lang=”sql”] GRANT EXECUTE ON [dbo].[procname] TO [youruser] [/cc] If you see this error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. Then you are out of luck. You are using SQL 2005+ and need to login as a different user.
The SQL Rank function was introduced in SQL Server 2005 and is part of a family of ranking functions. In order to explain the SQL Rank function, we need to look at it in context with the other rank functions. RANK DENSE_RANK ROW_NUMBER NTILE This list may seem overwhelming, however most of the ranking functions are rather similar. First, the syntax. Each ranking function takes a mandatory parameter of a column or value used in order to “rank”. Let’s look at an example run against the adventureworks light database. [cc lang=”sql”] SELECT UnitPrice = UnitPrice ,RowNumber = ROW_NUMBER() OVER (ORDER BY UnitPrice) ,Rank = RANK() OVER (ORDER BY UnitPrice) ,DenseRank = DENSE_RANK() OVER (ORDER BY UnitPrice) ,NTile = NTile(100) over (ORDER BY UnitPrice) FROM SalesLT.SalesOrderDetail [/cc] Looking at the results of the query provides the best explanation for the functions. From this list we can compare the UnitPrice in order to determine what each function is doing. The rownumber function, simply add one number per each row that is returned. The Rank function will compare the unit price and provide an integer based on it’s comparison with the previous row. If they are equal it will return the same rank. Internally, the rank will be incremented by one for each row (whether it is equal or not) and the next non-equal value will display the next internal rank. (I know this is a tough sentence, just look at the example). Dense_Rank performs the same as rank, except it does not […]
Continue reading ...
The most efficient method of copying a result set into a new table is to use the SELECT INTO method. This method also follows a very simple syntax. [cc lang=”sql”] SELECT * INTO dbo.NewTableName FROM dbo.ExistingTable [/cc] Once the query above is executed, all the columns and data in the table ExistingTable (along with their datatypes) will be copied into a brand new table named: NewTableName. The reason this is so fast is because it makes use an internal SQL Server feature called minimal logging. Minimal logging means that each row that is written to the new table is only written to the data page and is not written to the log file. Instead only a pointer to the data is inserted to the log file. This cuts the IO down by half. The query shown above is simple, but it can also be as complex as you’d like it to be. Any query that is currently returning a result set, can easily be made into a SELECT INTO query by inserting the INTO keyword after the select list. The Downside of SELECT INTO This title may be a little intense, however there are a couple issues that SELECT INTO does not cover. First, it does not copy over any of the constraints, keys, or computed columns. It also does not copy over the indexes. While this may not necessarily be a downside, it should be noted. Many times I have copied a table using SELECT INTO only to find […]
The try catch methodology of programming is a great innovation for SQL 2005+. The first question you should ask yourself before using Try/Catch should be “why?”. Why am I going to use Try/Catch? Personally, I have found a few uses, however I must say I do fall into the category of not being a developer that uses this for every procedure. Generally speaking, I do want my errors to bubble up to the next highest layer (like the web layer, and display exactly as they occur). And while you can use try/catch and still allow errors to bubble up, in a lot of cases I don’t see the need. In fact, in implementing Try/Catch in an improper way, it is possible to accidently hide errors to the extent of no one ever knowing about them. That said, probably the best use of Try/Catch is to implement your own error logging within your database. Before we get into an extensive logging example, let’s see an example of a Try Catch block. The following example would be a good use of Try/Catch because it is plausible that a negative number may enter a variable used in the Top Clause: [cc lang=”sql”] DECLARE @TopRows int SET @TopRows = -1 BEGIN TRY SELECT top (@TopRows) * FROM SalesLT.Customer END TRY BEGIN CATCH SELECT ERROR_NUMBER(), ERROR_MESSAGE() END CATCH [/cc] When we execute this script, we see that no exceptions are returned. Instead, a second result set is shown. Notice the first result set is also […]
Continue reading ...
