Instrucciones rápidas en T-SQL

Conocer la estructura de una tabla

2011-10-06T08:00:00.000-05:00

EXEC sp_MShelpcolumns 'Avales'

Convierte un número a letra

2011-10-05T10:42:00.001-05:00

/************************************************
Convierte un número a letra
execute sp_ConvertNumberToLetter 91234.78, '',0,1
Ej: 3456 > Tres Mil Cuatrociectos Cincuenta y Seis
*************************************************/

CREATE PROCEDURE sp_ConvertNumberToLetter
@iNumber VARCHAR(20),
@sResult VARCHAR(2000) OUTPUT,
@bUpperCase BIT = 1,
@bForProvider BIT = 0
AS
DECLARE @sNumber AS VARCHAR(13)
DECLARE @iLenght AS TINYINT
DECLARE @iCounter AS TINYINT
DECLARE @iQuota AS TINYINT
DECLARE @sPlus AS VARCHAR(20)
DECLARE @iPosition AS TINYINT

DECLARE @sHundreds AS NVARCHAR(3)
DECLARE @sHundOfThou AS NVARCHAR(3)
DECLARE @sHundOfMill AS NVARCHAR(3)
DECLARE @sHundOfTril AS NVARCHAR(3)

DECLARE @sDecimal AS VARCHAR(2)

DECLARE @sResults1 AS VARCHAR(200)
DECLARE @sResults2 AS VARCHAR(200)
DECLARE @sResults3 AS VARCHAR(200)
DECLARE @sResults4 AS VARCHAR(200)

DECLARE @sSubNumber AS VARCHAR(25)


--Convierte el número en NVarchar
SELECT @sNumber = RTRIM(CAST(@iNumber AS VARCHAR(13)))

--Quita los espacios en Blanco
SET @sNumber = RTRIM ( LTRIM( @sNumber ) )

--Inicia el contador en 1
SET @iCounter = 1

/*Separa la cantidad correspondiente a los centavos (SI EXISTEN)*/
-- SELECT CHARINDEX( '.', @sNumber )
IF CHARINDEX( '.', @sNumber ) <> 0
   BEGIN
    --Busca el lugar donde se encuentre el punto decimal y a partir del siguiente caracter
    --lo convierte en la parte decimal del número
    SET @sDecimal = SUBSTRING( @sNumber, CHARINDEX( '.', @sNumber ) + 1 , LEN( @sNumber ) )
--    select @sdecimal, '@sdecimal'
   --Desaparece esa parte decimal del número en general
    IF RTRIM(@sDecimal) <> ''
     BEGIN
      SET @sNumber = SUBSTRING( @sNumber, 1, CHARINDEX( '.', @sNumber ) - 1 )
--     select @snumber, '@snumber'
     END
   END
/*SI NO EXISTEN LOS PONE COMO 00/100*/
ELSE
   BEGIN
    SET @sDecimal = '00'
   END

--Invierte la cadena para obtener los números reales a convertir
SET @sNumber = REVERSE(@sNumber)

-- SELECT @sNumber

--Obtiene su longitud
SET @iLenght = LEN(@sNumber)

--Los separa en cantidades de 3 numeros
WHILE ( @iLenght > 0) AND ( @iCounter <= 4 )
   BEGIN
--   SELECT @sNumber , 'aqui toy'

    /*Verifica que haya mas de 3 numeros en la cadena*/
    IF @iLenght >= 3
     BEGIN
      SET @iQuota = 3
     END
    ELSE
     BEGIN
      SET @iQuota = @iLenght
     END

    /*Guarda en cada variable la cadena de números repartida*/
    IF @iCounter = 1
     BEGIN
      SET @sHundreds = SUBSTRING( @sNumber, 1 , @iQuota )
      SET @sHundreds = REVERSE( @sHundreds )
      END
    IF @iCounter = 2
     BEGIN
      SET @sHundOfThou   = SUBSTRING( @sNumber, 1 , @iQuota )
      SET @sHundOfThou = REVERSE( @sHundOfThou )
     END
    IF @iCounter = 3
     BEGIN
      SET @sHundOfMill = SUBSTRING( @sNumber, 1 , @iQuota )
      SET @sHundOfMill = REVERSE( @sHundOfMill )
     END
    IF @iCounter = 4

     BEGIN
      SET @sHundOfTril = SUBSTRING( @sNumber, 1 , @iQuota )
      SET @sHundOfTril = REVERSE( @sHundOfTril )
     END

    SET @sNumber = SUBSTRING(@sNumber , @iQuota + 1 , @ilenght )

    SET @iCounter = @iCounter + 1

    --Obtiene su longitud
    SET @iLenght = LEN(@sNumber)

   END

/*Incializa las variables de resultados*/
         SET @sResults1 = ''
         set @sResults2 = ''
         set @sResults3 = ''
         set @sResults4 = ''

-- EXECUTE sp_ConvertHundredsOfThousands @sHundOfThou , @sResults2 OUTPUT
/*Unidades a Centenas*/
EXECUTE sp_ConvertHundreds @sHundreds, @sResults1 OUTPUT
/*Unidades de Millar a Centenas de Millar */
EXECUTE sp_ConvertHundredsOfThousands @sHundOfThou , @sResults2 OUTPUT
/*Unidades de Millon as Centenas de Millon*/
EXECUTE sp_ConvertHundredsOfMillion @sHundOfMill , @sResults3 OUTPUT
/*Unidades de Billon a Centenas de Billon*/
EXECUTE sp_ConvertHundredsOfThousands @sHundOfTril , @sResults4 OUTPUT

/************************************************************
   Se evalúan las terminaciones que se agregarán para el caso de los Miles de Millones
*****************************************************************/
IF ( RTRIM( @sResults3 ) = '' )
AND ( RTRIM( @sResults4 ) <> '' )
AND ( RTRIM( @sResults1 ) = '' )
   BEGIN
    SET @sPlus = 'Millones'
   END
ELSE
   BEGIN
    SET @sPlus = ''
   END

/*Se concatena el resultado*/
SET @sResult =
LTRIM( RTRIM( @sResults4 ) ) + ' ' + @sPlus +
LTRIM( RTRIM( @sResults3 ) ) + ' ' +
LTRIM( RTRIM( @sResults2 ) ) + ' ' +
LTRIM( RTRIM( @sResults1 ) )

/*Agrega la palabra 'Pesos' y los centavos*/
SET @sResult = @sResult + ' Pesos' + ' ' +
LTRIM( RTRIM( @sDecimal ) ) + '/100 M.N.'

/*Verifica si es que existe alguna cantidad
    antes de la palabra pesos, de no ser asi
    pone CERO*/
IF CHARINDEX( 'P', LTRIM( @sResult ) ) = 1
   BEGIN
    SET @sResult = 'Cero ' + LTRIM( @sResult )
   END

/*Verifica que se escriba "UN PESO" y no "UN PESOS"*/
IF ( CHARINDEX( 'UN', LTRIM( @sResult ) ) = 1 )
     AND ( CHARINDEX( 'P', LTRIM( @sResult ) ) = 4 )
   BEGIN
    SET @sResult = REPLACE( @sResult, 'Pesos', 'Peso' )
   END

/*Verifica que se introduzca la palabra "DE" en el caso de los millones cerrados (Cuando el Modulo es 0 se introduce)*/
/*Obtiene el número dividido entre 1 millón*/
SET @sSubNumber = CAST( CAST( @iNumber AS DECIMAL ) / 1000000 AS VARCHAR(25) )
/*Separa la cadena después del punto*/
SET @sSubNumber = SUBSTRING( @sSubNumber , CHARINDEX( '.', @sSubNumber ) + 1 , LEN( @sSubNumber ) )
/*Comprueba después que la cadena separada sea mayor a 0 para poner la palabra "DE"*/
IF CAST( @sSubNumber AS INTEGER ) = 0
   BEGIN
    SET @sResult = REPLACE ( @sResult, 'Pesos', 'de pesos')
    SET @sResult = REPLACE ( @sResult, ' de', 'de')
   END


/*Para la posteridad, reemplaza donde halla ' pesos'*/

SET @sResult = REPLACE( @sResult, ' peso', ' peso' )

SET @sResult = LTRIM( RTRIM( @sResult ) )

/*¿Se convierten a mayúsculas?*/
IF @bUpperCase = 1
   BEGIN
    SET @sResult = LTRIM( RTRIM(UPPER( @sResult ) ) )
   END

/*¿Es una respuesta para el proveedor*/
IF @bForProvider = 1
   BEGIN
    SELECT @sResult
   END

Crear tabla de Amortización (método francés)

2011-10-05T10:31:00.001-05:00

/***********************************************************************************************

Objetivo: Crear tabla de amortizacion método francés

Fecha: 20/Julio/2011

exec sp_AmortizacionFrances 25000,12,0.85

************************************************************************************************/

Alter Procedure sp_AmortizacionFrances

@Prestamo float

,@Periodos smallint

,@Tasa float

-- 0. Declara variables

Declare @TasaMens float

Declare @TasaT1 float

Declare @CuotaC1 float

Declare @CuotaC1mT1 float

Declare @Amortizacion float

Declare @UltAmortizado float

Declare @TotAmortizado float

Declare @Cuota float

Declare @Total float

Declare @Interes float

Declare @Contador smallint

-- 1. Crear tabla amortización

If object_id('tempdb..#Amortizacion') is not null drop table #Amortizacion

Create table #Amortizacion (

Mes smallint

,Total float

,Interes float

,Cuota float

,Amortizacion float

,Amortizado float

)

-- 2. Asignacion valores

-- Set @Prestamo=500

-- Set @Periodos=5

-- Set @Tasa=0.10

Set @TasaMens=@Tasa/12

Set @TasaT1=@Prestamo*@TasaMens

Set @CuotaC1=@Prestamo*((power(1+@TasaMens,@Periodos)*@TasaMens)/(power(1+@TasaMens,@Periodos)-1))

Set @CuotaC1mT1=@CuotaC1-@TasaT1

-- 3. Calcula primera Amortizacion

Insert into #Amortizacion values(1,@Prestamo,@TasaT1,@CuotaC1,@CuotaC1-@TasaT1,@CuotaC1-@TasaT1)

-- 4. Calculo antes ciclo

Set @Amortizacion=@CuotaC1-@TasaT1

Set @UltAmortizado=@CuotaC1-@TasaT1

Set @Total=@Prestamo-@Amortizacion

Set @Contador=2

While @Contador <= @Periodos

Begin

Set @Interes=(@Prestamo-@CuotaC1mT1*(power(1+@TasaMens,@Contador-1)-1)/@TasaMens)*@TasaMens

Set @Cuota=@Prestamo*((power(1+@TasaMens,@Periodos)*@TasaMens)/(power(1+@TasaMens,@Periodos)-1))

Set @Amortizacion=@Cuota-@Interes

Set @TotAmortizado=@Amortizacion+@UltAmortizado

--Amortizacion n

Insert into #Amortizacion values

(@Contador,@Total,@Interes,@Cuota,@Amortizacion,@TotAmortizado)

Set @UltAmortizado=@Amortizacion+@UltAmortizado

Set @Total=@Total-@Amortizacion

Set @Contador=@Contador+1

End

--5. Resultado

Select * from #Amortizacion

compute sum(interes),sum(Amortizacion),sum(cuota)

Buscar texto en todos los SPs

2011-10-05T09:52:00.001-05:00

CREATE proc dbo.p_FindText
@strFind varchar (100),
@varDBName varchar (100) = 'MIBASE'
AS
BEGIN

Declare @varQuery varchar (1000)
select @varQuery =
'SELECT distinct ' +
'name SP_Name, ''sp_helptext '''''' + name + ''''''''SP_HT ' +
'FROM [' + @varDBName + '].[dbo].[sysobjects]
inner join [' + @varDBName + '].[dbo].[syscomments] ' +
'on [' + @varDBName + '].[dbo].[sysobjects].id
= [' + @varDBName + '].[dbo].[syscomments].id ' +
'where xtype = ''P'' ' +
'and text like ''%' + @strFind + '%'' ' +
'order by name '
exec (@varQuery)
END --sp

Encontrar duplicados

2011-10-05T09:51:00.001-05:00

SELECT email,COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Trasponer una tabla

2011-10-05T09:50:00.001-05:00

If object_id('tmpTabla1') is not null drop table tmpTabla1
Create table tmpTabla1 (Numero int,Tipo varchar(3),Importe float)

Insert into tmpTabla1 values (1990,'CAP',100)
Insert into tmpTabla1 values (1990,'INT',20)
Insert into tmpTabla1 values (1990,'IVA',1)
Insert into tmpTabla1 values (1995,'CAP',300)
Insert into tmpTabla1 values (1995,'INT',200)
Insert into tmpTabla1 values (1995,'IVA',100)
Insert into tmpTabla1 values (2000,'CAP',100)
Insert into tmpTabla1 values (2000,'INT',20)
Insert into tmpTabla1 values (2000,'IVA',1)
Insert into tmpTabla1 values (2005,'CAP',300)
Insert into tmpTabla1 values (2005,'INT',200)
Insert into tmpTabla1 values (2005,'IVA',100)

--Truncate table tmpTabla1
Select * from tmpTabla1

SELECT Numero,
       Cap= ISNULL((SELECT Importe FROM tmpTabla1 WHERE Tipo = 'CAP' and Numero=t.Numero),0),
       [Int]= ISNULL((SELECT Importe FROM tmpTabla1 WHERE Tipo = 'INT' and Numero=t.Numero),0),
       Iva= ISNULL((SELECT Importe FROM tmpTabla1 WHERE Tipo = 'IVA' and Numero=t.Numero),0)
FROM tmpTabla1 t
GROUP BY Numero

Elimina registros duplicados en una tabla

2011-10-05T09:49:00.003-05:00

Drop table tmpT1
Create table tmpT1 (col1 int,col2 int)

Insert into tmpT1 values (1,1)
Insert into tmpT1 values (1,2)
Insert into tmpT1 values (1,1) --duplicada
Insert into tmpT1 values (1,4)
Insert into tmpT1 values (1,5)

drop table tmpT2
Select * into tmpT2 from tmpT1

truncate table tmpT2

Declare @Col1 int,@Col2 int
Declare CursorA CURSOR forward_only FOR
Select * from tmpT1
OPEN CursorA
FETCH NEXT FROM CursorA
INTO @Col1,@Col2
WHILE @@FETCH_STATUS = 0
BEGIN
    If not exists(Select * from tmpT2 where Col1=@Col1 and Col2=@Col2)
    Insert into tmpt2 values (@Col1,@Col2)

    FETCH NEXT FROM CursorA
     INTO @Col1,@Col2
END
CLOSE CursorA
DEALLOCATE CursorA

Select * from tmpT1
Select * from tmpT2

Función DIFFERENCE

2011-10-05T09:49:00.001-05:00

SELECT DIFFERENCE('Smithers', 'Smythers')
SELECT DIFFERENCE('Anothers', 'Brothers')
SELECT DIFFERENCE('Perez', 'WSAeMilaAerezaZXCS')

Función SOUNDEX

2011-10-05T09:48:00.003-05:00

SELECT SOUNDEX ('Smith'), SOUNDEX ('Smitheres')

Función SPLIT

2011-10-05T09:48:00.001-05:00

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end
Select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

Obtener fecha sin la hora (otra versión)

2011-10-05T09:47:00.001-05:00

SELECT CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME)

Obtener la fecha sin la hora

2011-10-05T09:46:00.001-05:00

SELECT CAST(CAST(YEAR(getdate()) AS VARCHAR(4)) + '/' +
CAST(MONTH(getdate()) AS VARCHAR(2)) + '/' +
CAST(DAY(getdate()) AS VARCHAR(2)) AS DATETIME)

Ejecutar un script desde la linea de comandos

2011-10-05T09:45:00.001-05:00

osql -Sntserverx -Usa -Pmypassword -dBASE20111005 -i "E:\File\scripts\Script1.sql"

Dividir un nombre en más de dos partes

2011-10-05T09:44:00.003-05:00

DECLARE @FullName VARCHAR(100)
SET @FullName = 'John Doe Lopez'
SELECT PARSENAME(REPLACE(@FullName, ' ', '.'), 3) AS [Last2Name],
PARSENAME(REPLACE(@FullName, ' ', '.'), 2) AS [FirstName],
PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName]

Dividir nombre completo en apellidos

2011-10-05T09:44:00.001-05:00

DECLARE @FullName VARCHAR(100)
SET @FullName = 'Juan Perez'
SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS [FirstName],SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [LastName]

Generar un número al azar

2011-10-05T09:43:00.001-05:00

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],Contrato,Grupo,Integrante
From Contratos where contrato > 400000

Controlar el error División entre cero

2011-10-05T09:42:00.003-05:00

SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage] FROM [Table1]

SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]

Modifica la columna de una tabla

2011-10-05T09:42:00.001-05:00

ALTER TABLE [dbo].[Customers] ALTER COLUMN [CustomerID] INT NOT NULL

Cambiar el tamaño de la columna

2011-10-05T09:41:00.001-05:00

ALTER TABLE [dbo].[Customers] ALTER COLUMN [FirstName] VARCHAR(50)

Conocer si una tabla tiene llave primaria

2011-10-05T09:40:00.001-05:00

IF OBJECTPROPERTY( OBJECT_ID( '[dbo].[Customers]' ), 'TableHasPrimaryKey' ) = 1
PRINT '[dbo].[Customers] table has a primary key.'

Cambiar el propietario de una tabla

2011-10-05T09:39:00.003-05:00

EXECUTE sp_changeobjectowner 'dbo.Customers', 'my'

Cambiar el nombre a una tabla

2011-10-05T09:39:00.001-05:00

EXECUTE sp_rename '[dbo].[Clients]', 'Customers'

Importar desde Excel a SQL

2011-10-05T09:38:00.001-05:00

If Object_id('tempdb..#tmpArchivo') is not null Drop Table #tmpArchivo
Create table #tmpArchivo (Num float,Nombre varchar(100),Apellido varchar(100),Direccion varchar(100))

Insert into #tmpArchivo
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=E:\Siopfile\Archivo.xls',
                'SELECT * FROM [Dir$]')
--Leer Excel en caso de que no tenga Cabecera cada columna
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;HDR=No',
                'SELECT * FROM [Sheet1$]')

--Para indicar que las columnas tienen diferentes tipos (Ej: q venga en el CP numeros y letras)
SELECT
* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel8.0;Database=C:\Source\Addresses.xls;HDR=No;IMEX=1',
                'SELECT * FROM [Sheet1$]')

--Para importar directo a una tabla SQL>Excel
SELECT * into #tmpArchivo2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=E:\Siopfile\Archivo.xls',
                'SELECT * FROM [Dir$]')

--Para importar con el tipo de datos correcto
SELECT CAST([FirstName] AS VARCHAR(20)) AS [FirstName],
       CAST([LastName] AS VARCHAR(20)) AS [LastName],
       CAST([Address] AS VARCHAR(50)) AS [Address],
       CAST([City] AS VARCHAR(30)) AS [City],
       CAST([State] AS VARCHAR(2)) AS [State],
       CAST([ZIP] AS VARCHAR(10)) AS [ZIP]
INTO [dbo].[Addresses_Temp]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Source\Addresses.xls;IMEX=1',
                'SELECT * FROM [Sheet1$]')

Guardar el resultado en plantilla HTML

2011-10-05T09:37:00.001-05:00

USE pubs
GO
EXECUTE sp_makewebtask @outputfile = 'C:\MULTIPLE.HTM',
@query = 'SELECT title, price FROM titles SELECT au_lname, au_fname
FROM authors SELECT pub_id, pub_name FROM publishers SELECT au_lname,
au_fname FROM authors', @templatefile = 'C:\MULTIPLE.TPL',
@dbname = 'pubs', @rowcnt = 5, @whentype = 9
GO

--Se requiere esta plantilla
<HTML>
<HEAD>
<TITLE>SQL Server Multiple Queries with Template Web Sample</TITLE>
<BODY>
<H1>Books For Sale</H1>
<HR>
<P>
<TABLE BORDER>
<TR> <TH><I>Title</I></TH> <TH><B>Price</B></TH> </TR>
<%begindetail%>
<TR> <TD><I> <%insert_data_here%> </I> </TD>
<TD ALIGN=RIGHT><B> $<%insert_data_here%></B></TD> </TR>
<%enddetail%>
</TABLE>
<P>
<HR>
<%insert_data_here%>
<P>
<TABLE BORDER>
<TR> <TH ALIGN=CENTER>ID</TH> <TH ALIGN=LEFT><I>Publishers Name</I></TH> </TR><%begindetail%>
<TR> <TD> <%insert_data_here%> </TD>
<TD ALIGN=LEFT><I> <%insert_data_here%></I></TD> </TR><%enddetail%>
</TABLE>
<HR>
<%insert_data_here%>
<P>
<A HREF = "http://www.microsoft.com">Microsoft</A><P>
<A HREF = "http://msdn.microsoft.com">MSDN</A><P>
</BODY>
</HTML>

Escribir a un archivo de texto (ver 2)

2011-10-05T09:36:00.001-05:00

master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '
exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'