Karuta’s ASP & M$ SQLserver

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

Archive for the ‘sql server’ Category

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 »

UPDATE from SELECT using SQL Server

Posted by karuta em agosto 15, 2014

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

Posted in sql server | Leave a Comment »

Limpando logs de bases no SQL Server

Posted by karuta em agosto 7, 2014

backup log “BANCODEDADOS” with truncate_only
go

dbcc shrinkdatabase (’BANCODEDADOS’, 0)
go

Onde, BANCODEDADOS é o nome do banco onde fará manutenção. O primeiro comando faz um backup dos registros do log. O segundo, limpa os arquivos inúteis do banco, como logs gigantescos. 

Posted in sql server | Leave a Comment »

DIVISÃO NO SQL – MAIS DIGITOS APÓS A VIRGULA

Posted by karuta em julho 31, 2014

select 0.59 / 350 as resultado1
select CAST(0.59 as float) / CAST(350 as float) as resultado2

resultado1
—————————————
0.001685

(1 row(s) affected)

resultado2
———————-
0,00168571428571429

(1 row(s) affected)

Posted in sql server | Leave a Comment »

Envio dados SQL SERVER – ASP – ADO – adLongVarChar

Posted by karuta em julho 30, 2014

[Microsoft][ODBC SQL Server Driver]String data, right truncation

http://www.w3schools.com/asp/ado_datatypes.asp

varchar(MAX) can be used from ADO as an input parameter. The data type in this case would be adLongVarChar

Se acontecer problemas no tamanho do texto que você precisa enviar para o banco de dados, use o parametro: 201 = adLongVarChar

 

rsInserirRoyalty_cmd.Parameters.Append rsInserirRoyalty_cmd.CreateParameter(“param3”, 201, 1, -1, v0)

Posted in asp, sql server | Leave a Comment »

SQL SERVER IF ISNUMERIC

Posted by karuta em julho 10, 2014

There is no boolean in SQL Server. This means you can’t just say IF (expression); you must compare it to something, because it does return true or false in the same sense as you’re probably used to in other languages.

Just a preference, but I would prefer to write it this way:

IF ISNUMERIC('5675754674') = 1
 BEGIN
 ...
 END

There is no way in SQL Server to avoid the comparison to 1, as in your second example.

Also as an aside you should be aware of the weaknesses of ISNUMERIC() – it can give false positives for “numeric” values such as ., CHAR(9), e, $ and a host of other non-numeric strings. If you want to know if something is an integer, for example, better to say:

IF '5675754674' NOT LIKE '%[^0-9]%'
 BEGIN
 ...
 END

But even that is not a complete and valid test because it will return true for values > (2^32)-1 and it will return false for negative values.

Another downside to ISNUMERIC() is that it will return true if the value can be converted to any of the numeric types, which is not the same as all numeric types. Often people test for ISNUMERIC() and then try to cast a FLOAT to a SMALLINT and the conversion fails.

In SQL Server 2012 you will have a new method called TRY_CONVERT() which returns NULL if the conversion to the specified data type is not valid.

tks Aaron Bertrand (credit)

Posted in sql server | Leave a Comment »

Is it possible to use Aggregate function in a Select statment without using Group By clause?

Posted by karuta em novembro 22, 2013

You can use Select AGG() OVER() in TSQL

SELECT *,
SUM(Value) OVER()
FROM Table

There are other options for Over such as Partition By if you want to group:

SELECT *,
SUM(Value) OVER(PARTITION By ParentId)
FROM Table

http://msdn.microsoft.com/en-us/library/ms189461.aspx

Posted in sql server | Leave a Comment »

Função SPLIT – transforma uma variável com dados separados em tabela

Posted by karuta em março 14, 2013

 

–cria function split

CREATE FUNCTION [dbo].[fnSplit](
@frase VARCHAR(8000), @delimitador VARCHAR(8000) = ‘,’
) RETURNS @result TABLE (item VARCHAR(8000))

BEGIN
DECLARE @parte VARCHAR(8000)
WHILE CHARINDEX(@delimitador,@frase,0) <> 0
BEGIN
SELECT
@parte=RTRIM(LTRIM(SUBSTRING(@frase,1,CHARINDEX(@delimitador,@frase,0)-1))),
@frase=RTRIM(LTRIM(SUBSTRING(@frase,CHARINDEX(@delimitador,@frase,0)+LEN(@delimitador),LEN(@frase))))
IF LEN(@parte) > 0
INSERT INTO @result SELECT @parte
END

IF LEN(@frase) > 0
INSERT INTO @result SELECT @frase
RETURN
END

GO

 

Posted in sql server | 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 »

 
Seguir

Obtenha todo post novo entregue na sua caixa de entrada.