SQL Cheetsheet

Overview
Useful Techniques

Overview

This document contains useful SQL commands and functionality. For more information, see https://www.sqlservertutorial.net/.

Useful Techniques

While Loops

The syntax for a while loop in sql server is:

while something_is_true
begin
    do_some_things
end

Here is a simple working example:

declare @counter int = 10
while @counter > 0
begin
    print @counter
    set @counter = @counter - 1
end

Cursors

If you need to iterate over a table row by row, use a cursor. Here is a simple working example:

create table #MyTable (UniqueID int, [count] int)
insert into #MyTable values (1, 0)
insert into #MyTable values (2, 0)
insert into #MyTable values (3, 0)
insert into #MyTable values (4, 0)
insert into #MyTable values (5, 0)
select * from #MyTable
----------------------------------------------------------------------------------------------------------------------
declare @unique_id int                                          -- 1. declare variable(s)
declare MyCursor cursor for select UniqueID from #MyTable       -- 2. declare cursor
open MyCursor;                                                  -- 3. open cursor
fetch next from MyCursor into @unique_id                        -- 4. fetch first row into the variable(s) you declared
while @@FETCH_STATUS = 0                                        -- 5. open while loop
begin
    update #MyTable 
    set [count] = UniqueID * 2 where UniqueID = @unique_id      --   use data you fetched
    fetch next from MyCursor into @unique_id                    --   fetch subsequent rows
end
                                                                -- 6. close and deallocate cursor
close MyCursor;
deallocate MyCursor;
----------------------------------------------------------------------------------------------------------------------
select * from #MyTable
drop table #MyTable

As seen in the example above, there are several steps to take when using a cursor:

Declare variables
Declare cursor
Open cursor
Fetch the first row into the variables you declared
Open a while loop to use data you fetched into your variables and fetch subsequent rows
Close and deallocate cursor

@@FETCH_STATUS contains the status of the last cursor fetch. A value of 0 indicates that another row was fetched and more processing can be done. Any other value indicates that there are no more rows in the table to process, so the while loop ends.

Here is another, more realistic example of cursor use, taken from the smart.p11_Load_DropSample stored procedure in CORE_CEM.

declare
    @ColName varchar(max),
    @MaxLength int,
    @DataType varchar(max)

declare ColCursor cursor for
select column_name, character_maximum_length, data_type
from information_schema.columns
where table_name = 'P11_IntakeForm' and column_name in (
    select column_name
    from information_schema.columns
    where table_name = 'p11_intakeform_DropSample'
)
open ColCursor;
fetch next from ColCursor into @ColName, @MaxLength, @DataType
while @@FETCH_STATUS = 0
begin
    print @@FETCH_STATUS
fetch next from ColCursor into @ColName, @MaxLength, @DataType
end

close ColCursor;
deallocate ColCursor;

Source: https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/

Dynamic SQL

This technique allows the creation of "on-the-fly" sql statements at runtime. They are very useful when you need your code to be flexible. Here is a simple example using the while loop we saw previously:

create table #MyTable (UniqueID int, [count] int)
insert into #MyTable values (1, 0)
insert into #MyTable values (2, 0)
insert into #MyTable values (3, 0)
insert into #MyTable values (4, 0)
insert into #MyTable values (5, 0)
select * from #MyTable
----------------------------------------------------------------------------------------------------------------------
declare @script varchar(max) 
declare @counter int = 10
while @counter > 0
begin
    set @script = 'update #MyTable set count =' + cast(@counter as varchar(10)) + ' where UniqueID = ' + cast(@counter as varchar(10))
    exec(@script)
    set @counter = @counter - 1
end
----------------------------------------------------------------------------------------------------------------------
select * from #MyTable
drop table #MyTable

To summarize, construct a string of valid sql with the variables you need, then call the exec() function on it. Here is a more realistic example of dynamic sql (again taken from the smart.p11_Load_DropSample stored procedure in CORE_CEM), that also combines all of the techniques discussed so far: