remidian

Can't find what you're looking for? Use of one of the search websites below …

Home → mssql → Archive by category "T-SQL"

Category Archives: T-SQL

T-SQL != PL/SQL

Transact-SQL is central to the use of Microsoft SQL Server. All applications that communicate with SQL Server do so by sending Transact-SQL statements to the server, regardless of an application’s user interface. Transact-SQL Reference

Restore SQL Server database with TSQL

Recently I had troubles restoring a database in my SQL Server 2005 instance (SP3). I used the ‘wizard’ in the SQL Server Enterprise manager but after I got the message ‘restore completed successfully’ the database hung in ‘Restoring’ state, blocking connections.

I decided the try to restore the database with T-SQL, with T-SQL the restore completed successfully! Another advantage of using T-SQL is that T-SQL gave more logging. This logging shows me that the BAK file (delivered from a third party) is not a SQL Server 2005 backup but presumably a SQL Server 2000 database backup … 

DECLARE
  @BackupFile varchar(8000),
  @sql varchar(8000)
  SET @BackupFile = 'E:\DB2000.BAK'
  SET @sql = 'RESTORE DATABASE ADB01 FROM DISK = ''' + @backupfile + ''' WITH FILE = 1,  
    MOVE N''DB2000_dat'' TO N''E:\mssql\data\ADB01\ADB01_01.mdf'',  
    MOVE N''DB2000_log'' TO N''F:\mssql\tlog\ADB01\ADB01_01.ldf'',  
    NOUNLOAD,

  • / T-SQL
  • # 866
  • NOV, 15 2006

Control Structures

Control structures syntax; IF THEN ELSE, CASE, WHILE .

IF THEN ELSE

The IF THEN ELSE syntax in TSQL is kinda weird if you ask me … I forget them ‘all the time’, created examples below for my reference.

IF THEN
if 1 = 1
begin 
  print 'true'
end
 
go
IF THEN – short notation
if 1 = 1
  print 'true'
 
go
IF THEN ELSE
if 1 = 1
begin
  print 'true'
end
 
else  

begin
  print 'false'
end
 
go
IF THEN ELSE – short notation
if 1 = 1
  print 'true'
else  
  print 'false'

go
Multiple IF THEN ELSE blocks
if 1 = 1
begin
  print '1 = 1'
end
else  
if 1 = 2
  begin
    print '1 = 2'
  end
else
if 1 = 3
begin
  print '1 = 3'
end
 
go

CASE

TSQL has the CASE control structure …

declare @i int, @r int
 
set

  • / T-SQL
  • # 5819
  • NOV, 3 2006

cursors in tsql

Cursors in tsql, it can be done …

Simple cursor loop

Example below is the most straight forward example of walking through a CURSOR with a WHILE loop in tsql. The cursor is declared, opened and and a FETCH NEXT is done while @@fetch_status equals 0.

The behaviour of a FETCH [ NEXT ] call is that it returns the value(s) for the column(s) specified in the DECLARE CURSOR as a single-row result set.

Basic cursor example with FETCH returning row as single-row result set
1> declare c1 cursor
2> for
3> select left( name, 32)            as name,
4>        cast( dbid as varchar(10)) as dbid
5>   from master.dbo.sysdatabases
6>  where dbid < 5
7>
8> open c1
9> fetch next from c1
10>
11> -- while @@fetch_status != -1 and @@fetch_status

  • / T-SQL
  • # 1732
  • NOV, 1 2006

return variables from dynamic sql – sp_executesql

Returing values into tsql variables from a dynamic sql statement using sp_executesql stored procedure.

Scenario

The example below displays the number of objects per database. The following steps are involved.

open a cursor for the select * from master.dbo.sysdatabases statement (cursor is a while loop), inside the cursor a dynamic sql statement is defined , at last the stored procedure sp_executesql is used to retrieve the output of this defined sql statement into tsql variables .
sp_execute example to return values into variables 
1> declare @s_dtb  sysname
2> declare @s_sql  nvarchar(2000)
3> declare @i_obj  int
4> declare c1     cursor
5> for
6> select name
7>   from master.dbo.sysdatabases
8>
9>
10> open c1
11> fetch c1 into @s_dtb
12>
13> while @@fetch_status >= 0
14> begin
15>
16>   -- construct sql statement
17>   set @s_sql = 'select @count = count(*)
18>

  • / T-SQL
  • # 668
  • SEP, 18 2006

environment

Getting around in the mssql environment

Whoami

In MSSQL (2000) there’s not an easy way to see in which database you are. Script below creates a view ‘whoami’ that gives you the information; 

create view dbo.whoami
as
select @@spid as '@@spid',
       cast( user as varchar(12)) as 'user',
       cast( p.dbid as varchar(6)) + ':' + cast( d.name as varchar(14)) as 'database',
       cast( p.loginame as varchar(16)) loginame,
       cast( p.hostname as varchar(16)) as 'hostname',
       cast( p.nt_username as varchar(16)) nt_username
   from master.dbo.sysprocesses p left join master.dbo.sysdatabases d
      on d.dbid = p.dbid
   where spid = @@spid
 
go
 
grant select on dbo.whoami to public
go
 

1> select * from whoami
2> go
 @@spid user         database

Date Time functions

Date time functions (frustrations) in T-SQL.

Current date

Functions getdate and getutcdate display the current date. Both functions accept zero arguments.

1> print getdate()
2> print getutcdate()
3> go
Sep 18 2006  8:25PM
Sep 18 2006  6:25PM

Formatting

To format the display of your date you need to convert it to a varchar (like the to_char function in oracle).

So to display 18 september as 25-09-2006 I must convert it the Italian?? way, as shown below.

1> print convert( varchar(32), getdate(), 105)
2> go
18-09-2006
1> print convert( varchar(32), getdate(), 5)
2> go
18-09-06

Advanced(?) formatting

My requirement is simple: get the date displayed as ‘yyyymmdd_hhmiss’. And yes I want leading zeros for the first nine monts/minutes. So ’09′ for ‘September’ and not ’9′. How hard can that be … Well, I am missing something bigtime OR this basic functionality is simply missing from sqlserver T-SQL (version 2000).

I wrote the T-SQL code below to get me the