SQL: Using while loop for replacing cursor

Following SQL code snippet shows how we can use while loop :


create table #t (pk_counter int identity(1,1), pk_subquestion int, fk_question int, subquestion_text varchar (500), subquestion_type varchar (50), subquestion_order varchar(50), required_flag bit)

declare @counter int
declare @maxcount int
declare @subquestionid int
select @maxcount = count(pk_counter) from #t
set @counter = 1

while (@counter < @maxcount + 1) begin select @subquestionid=pk_subquestion from #t where pk_counter=@counter exec stp_get_options @subquestionid set @counter = @counter + 1 end
drop table #t

SQL: Finding duplicate rows in table

Following SQL code snippet demonstrates finding duplicate rows in table:

SELECT SampleDescription,
COUNT(SampleDescription) AS Occurance
FROM SchedTimeOffType
GROUP BY SampleDescription
HAVING ( COUNT(SampleDescription) > 1 ) order by Occurance desc

SQL: Recursive Select Variable

Using this method we can convert a vertical list into horizontal list. Atleast two uses are suggested by Paul Nielson:

1. Denormalizing a list
2. Dynamic Cross tabs query

In following example we will see how can we denormalize a list i.e. converting a vertical list into horizontal list.

use NorthWind;
DECLARE @Name nvarchar (2200)
SET @Name = ''
SELECT @Name = @Name + A.Name + '; ' FROM (Select DISTINCT CompanyName AS Name FROM Customers) AS A
PRINT @Name

Getting Caller Function Name

Sometimes it would be great to know which is the caller function for the current executing function for debugging purpose. Following code snippet shows how to get caller function name:

StackTrace stackTrace = new StackTrace();
StackFrame stackFrame = stackTrace.GetFrame(1);
MethodBase methodBase = stackFrame.GetMethod();
Console.WriteLine(methodBase.Name);