Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for the ‘store procedure’ Category

Tratamento de erros no SQL Server (TRY-CATCH)

Posted by karuta em agosto 24, 2018

— Criando tabela para teste 
CREATE TABLE #teste (id int)

— Adicionando restrição à tabela.
— Somente IDs menores do que 10 serão aceitos 

ALTER TABLE #teste ADD CONSTRAINT chkID CHECK (id < 10)

 

— Executando a tarefa em transação
BEGIN TRANSACTION

BEGIN TRY
        
    INSERT INTO #teste VALUES (5)
    INSERT INTO #teste VALUES (2)
    INSERT INTO #teste VALUES (13)  — Inserção de ID inválido

    COMMIT TRANSACTION — Efetivando alterações na base

END TRY
BEGIN CATCH
    
    PRINT ‘Erro ao executar script’
    ROLLBACK TRANSACTION — Desfazendo as alterações na base
          
END CATCH

SELECT * FROM #teste

DROP TABLE #teste

Anúncios

Posted in sql server, store procedure | Leave a Comment »

Uso de WITH e MERGE para inserção em lote pegando os ID do campo Identity Increment

Posted by karuta em agosto 24, 2018

agenda 1 – tabela com os dados [id_agenda1, nome, telefone]

agenda 2  – tabela nova que vai receber os dados [id_agenda2, nome, telefone]

agenda 3 – id da agenda1 e id criado na agenda 2 [id_agenda1, id_agenda2]

 


;WITH magica
AS (
SELECT * FROM [agenda1]
)
MERGE INTO [agenda2]
USING magica Y
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (nome, telefone)
OUTPUT INSERTED.id_agenda2, Y.id_agenda1  INTO agenda3;

Posted in sql server, store procedure | 2 Comments »

Função para criar JSON a partir de um XML

Posted by karuta em agosto 16, 2018

— Como usar:
SELECT dbo.FlattenedJSON(
(select top 10 artista_id, empresa_id, data_alteracao, login_alteracao from yb_artistas
FOR XML path, root)
)
— —————————————————————————————————————–
CREATE FUNCTION dbo.FlattenedJSON (@XMLResult XML)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE  @JSONVersion NVARCHAR(MAX), @Rowcount INT
SELECT @JSONVersion = , @rowcount=COUNT(*) FROM @XMLResult.nodes(‘/root/*’) x(a)
SELECT @JSONVersion=@JSONVersion+
STUFF(
  (SELECT TheLine FROM
    (SELECT ‘,
    {‘+
      STUFF((SELECT ‘,”‘+COALESCE(b.c.value(‘local-name(.)’, ‘NVARCHAR(255)’),)+‘”:”‘+
       REPLACE( –escape tab properly within a value
         REPLACE( –escape return properly
           REPLACE( –linefeed must be escaped
             REPLACE( –backslash too
               REPLACE(COALESCE(b.c.value(‘text()[1]’,‘NVARCHAR(MAX)’),),–forwardslash
               ‘\’, ‘\\’),
              ‘/’, ‘\/’),
          CHAR(10),‘\n’),
         CHAR(13),‘\r’),
       CHAR(09),‘\t’)
     +‘”‘
     FROM x.a.nodes(‘*’) b(c)
     FOR XML PATH(),TYPE).value(‘(./text())[1]’,‘NVARCHAR(MAX)’),1,1,)+‘}’
   FROM @XMLResult.nodes(‘/root/*’) x(a)
   ) JSON(theLine)
  FOR XML PATH(),TYPE).value(‘.’,‘NVARCHAR(MAX)’ )
,1,1,)
IF @Rowcount>1 RETURN ‘[‘+@JSONVersion+
]’
RETURN @JSONVersion
END

Posted in sql server, store procedure | Leave a Comment »

Localizar palavras em Store Procedures do SQL SERVER

Posted by karuta em janeiro 31, 2018

TYPE armazena o tipo do objeto a ser localizado, onde :
U => Tabela Usuário
S => Tabela de sistema
P => Procedure
V => View
F => Function

Posted in sql server, store procedure | Leave a Comment »

SQL Server: Uso Prático do OVER() para agrupar

Posted by karuta em outubro 23, 2016

select customerID, productID, orderDate, orderAmount
from Orders

customerID  productID   orderDate               orderAmount
----------- ----------- ----------------------- ---------------------
1           1           2007-01-01 00:00:00.000 20.00
1           2           2007-01-02 00:00:00.000 30.00
1           2           2007-01-05 00:00:00.000 23.00
1           3           2007-01-04 00:00:00.000 18.00
2           1           2007-01-03 00:00:00.000 74.00
2           1           2007-01-06 00:00:00.000 34.00
2           2           2007-01-08 00:00:00.000 10.00

(7 row(s) affected)
select customerID,  productID, orderDate, orderAmount, 
      sum(orderAmount) OVER (Partition by CustomerID) as Total
from Orders

customerID  productID   orderDate               orderAmount   Total
----------- ----------- ----------------------- ------------- ---------
1           1           2007-01-01 00:00:00.000 20.00         91.00
1           2           2007-01-02 00:00:00.000 30.00         91.00
1           2           2007-01-05 00:00:00.000 23.00         91.00
1           3           2007-01-04 00:00:00.000 18.00         91.00
2           1           2007-01-03 00:00:00.000 74.00         118.00
2           1           2007-01-06 00:00:00.000 34.00         118.00
2           2           2007-01-08 00:00:00.000 10.00         118.00

(7 row(s) affected)

 

 

Posted in sql server, store procedure | Leave a Comment »

Buscar ‘termos’ dentro de Store Procedures

Posted by karuta em outubro 25, 2015

select object_name(id) from syscomments where text like ‘% termos %’

Posted in sql server, store procedure | Leave a Comment »

execute mathematical formula stored as string

Posted by karuta em novembro 10, 2014

Declare @weight as char(25)
Declare @output as numeric(18,2)

SET @weight = '2*5+2*5*4'
declare @TheSQL nvarchar(1000)
Set @TheSQL = 'Select @theoutput = ' + @weight


--OutPut should be 50.
exec sp_executesql @stmt = @TheSQL
    , @params= N'@theoutput int OUTPUT' 
	, @theoutput = @output output

print @output


links úteis para pesquisa:
http://www.itjungle.com/fhg/fhg111004-story01.html
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Posted in sql server, store procedure | Leave a Comment »

Buscar Registros Duplicados

Posted by karuta em outubro 26, 2012

SELECT
cliente_id,produto_id,
count(*)
FROM [tabela]
WHERE
cliente_id <> ”
GROUP BY cliente_id,produto_id
HAVING COUNT(*) > 1

Posted in sql server, store procedure | Leave a Comment »

List All The Column With Specific Data Types in Database

Posted by karuta em outubro 13, 2012

SELECT s.name AS ‘schema’, ts.name AS TableName,
c.name AS column_name, c.column_id,
SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
t.name AS Datatypename
,t.is_user_defined, t.is_assembly_type
,c.is_nullable, c.max_length, c.PRECISION,
c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
ORDER BY s.name, ts.name, c.column_id

Posted in sql server, store procedure | Leave a Comment »

Descobrir registros duplicados em uma tabela SQL

Posted by karuta em outubro 13, 2012

SELECT * FROM tabela t1 WHERE EXISTS ( SELECT * FROM tabela t2 WHERE t2.campo = t1.campo GROUP BY campo HAVING COUNT(*) > 1 ) ORDER BY lista de campos

ou

SELECT * FROM tabela WHERE campo IN ( SELECT campo FROM tabela GROUP BY campo HAVING count(*) > 1) ORDER BY lista de campos

Posted in sql server, store procedure | Leave a Comment »