Use cases for NULLIF

Question: I understand the theory behind NULLIF – based on the MS documentation “Returns a null value if the two specified expressions are equal”. But I’ve rarely had the opportunity to apply this function in real word scenarios . Can you expand on some real world scenarios for NULLIF ? Answer: You are right – it’s not a widely used function , I think mainly because the more widely used expression CASE is used Basically NULLIF(value1,value2) Is the equivalent to CASE WHEN value1 = value2 THEN NULL ELSE value1 END Using CASE gives you the advantage of utilising other expressions... Read more →

Msg 16955 – Could not create an acceptable cursor

Question: I’m getting an error when checking some new application installed with a SQL Server backend. How can I fix? The sql code and the associated error are below: declare @p1 int set @p1=0 declare @p3 int set @p3=98305 declare @p4 int set @p4=311300 declare @p5 int set @p5=0 exec sp_cursoropen @p1 output,N'SELECT GetDate()',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 Msg 16955, Level 16, State 2, Line 10 Could not create an acceptable cursor. Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1 [Batch Start Line 9] The cursor was not declared. Answer: This error is occurring... Read more →

How to find SQL Server Instance Default Path and Instance Default Log Path

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry. To read more about these options read SQL Server - Find Default Data location and Default Log Location .... For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and... Read more →

How to write a conditional substring in sql

Question: I have a SELECT statement returning a recordset . Before presenting the recordset , I want to strip out some of the substrings within certain records. This is an example of the recordset Brazil Brazil.com Argentina.net Bolivia The requirement is to strip out any part of the string from the dot and after. For example, “brazil.com” will become “brazil”. The problem I have currently is by running the SUBSTRING function across all the rows, if there is no dot in the row , the rown will return as blank. The only rows returning are the ones with a dot!... Read more →

SQL Server Stuff and comma delimited column

Question: I have two tables and would like to return a recordset which is made up of table1 plus an extra column which is made up of a comma-delimited string from table2 based on an INNER JOIN. I’ve read the comma delimited article , but this returns the full recordset as a CSV.SQL Server – Create a comma delimited sql result set (SQL Server ... Answer: One option to use is the SQL Server STUFF function.The SQL Server STUFF function is used to insert one string into another string. This makes it useful for what you’re attempting to achieve. You... Read more →