UPDATE with top clause

If you want update a table with top clause see the below script:

UPDATE top (1) TABLE1
SET status ='ready'
where status ='hold'

At the same time if you want to return the Row IDs of updated table:

UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid
where status ='hold'

—————— result—————-
rowid
1

Same thing if you want to store in variable

DECLARE @UpdatedIds table (id int)
DECLARE @rowId int


UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid INTO @UpdatedIds
where status ='hold'

SELECT @rowId=id FROM @UpdateIds
SELECT @rowId

HTTP Get method from MSSQL

If you want to call a URL and store the response of that url use this function:

ALTER function [dbo].[GetHttp]
(
@url varchar(8000)
)
returns varchar(8000)
as
BEGIN
DECLARE @win int
DECLARE @hr  int
DECLARE @text varchar(8000)

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win


EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text


END

Http Post from MSSQL

Call the HTTP post using MSSQL script :


ALTER function [dbo].[POSTHttp]
(
@url varchar(8000),
@data varchar(2000)
)
returns varchar(8000)
as

BEGIN
DECLARE @win int
DECLARE @hr  int
DECLARE @text varchar(8000)


EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'POST',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'setRequestHeader',NULL, 'Content-type'
, 'application/x-www-form-urlencoded'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send',null,@data
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text

END

CTE Function

CTE fucntion can be used on this way:

IF OBJECT_ID('tempdb..#dataList') IS NOT NULL
DROP TABLE #dataList

CREATE TABLE #dataList(
id            INT
,name        VARCHAR(50)
,parent        INT
,ip            INT
)


INSERT #dataList
SELECT 1    id, 'Group1'        Name, 0 Parent, 0 ip UNION ALL
SELECT 2    id, 'SubGroup1'        Name, 1 Parent, 0 ip UNION ALL
SELECT 3    id, 'Item1'            Name, 2 Parent, 1 ip UNION ALL
SELECT 4    id, 'Item2'            Name, 2 Parent, 1 ip UNION ALL
SELECT 5    id, 'Item3'            Name, 2 Parent, 1 ip UNION ALL
SELECT 6    id, 'Item4'            Name, 2 Parent, 1 ip UNION ALL
SELECT 7    id, 'SubGroup2'        Name, 1 Parent, 0 ip UNION ALL
SELECT 8    id, 'Item5'            Name, 7 Parent, 1 ip UNION ALL
SELECT 9    id, 'Item6'            Name, 7 Parent, 1 ip UNION ALL
SELECT 10    id, 'Item7'            Name, 7 Parent, 1 ip UNION ALL
SELECT 11    id, 'Item8'            Name, 7 Parent, 1 ip

--SELECT * FROM #dataList


DECLARE @id INT = 1
;WITH CTE(id, name, parent, ip)
AS
(
SELECT * FROM #dataList WHERE id = @id
UNION ALL
SELECT d.* FROM #dataList D
INNER JOIN CTE c ON d.parent = c.id
)

SELECT * FROM CTE WHERE ip = 1 ORDER BY id ASC;

Dynamic PIVOT

Sample for Dynamic PIVOT Query:

Some time we need complex cross tab result for report, that time we can use the PIVOT Query, for example we have 3 Tables and we have data like this :


IF OBJECT_ID('tempdb..#customer') IS NOT NULL
DROP TABLE #customer

CREATE TABLE #customer (
CustomerId INT,
CustomerName VARCHAR(50)
)


INSERT #customer
SELECT 1, 'Ram'
INSERT #customer
SELECT 2, 'Hari'
INSERT #customer
SELECT 3, 'Depesh'
INSERT #customer
SELECT 4, 'Sailesh'

--Select * from #customer



————————————————————————–

IF OBJECT_ID('tempdb..#LoanType') IS NOT NULL
DROP TABLE #LoanType

CREATE TABLE #LoanType (
LoanId INT,
LoanName VARCHAR(50)
)


INSERT #LoanType
SELECT 1, 'Loan-1'
INSERT #LoanType
SELECT 2, 'Loan-2'
INSERT #LoanType
SELECT 3, 'Loan-3'
INSERT #LoanType
SELECT 4, 'Loan-4'
INSERT #LoanType
SELECT 5, 'Loan-5'
INSERT #LoanType
SELECT 6, 'Loan-6'

--Select * from #LoanType


——————————————————————————

IF OBJECT_ID('tempdb..#LoanCustomer') IS NOT NULL
DROP TABLE #LoanCustomer


CREATE TABLE #LoanCustomer (
CustomerId INT,
LoanId INT,
Amount MONEY
)


INSERT #LoanCustomer
SELECT 1, 1, 500
INSERT #LoanCustomer
SELECT 2, 1, 5300
INSERT #LoanCustomer
SELECT 3, 3, 1500
INSERT #LoanCustomer
SELECT 2, 1, 2500
INSERT #LoanCustomer
SELECT 1, 6, 5500
INSERT #LoanCustomer
SELECT 2, 4, 1100
INSERT #LoanCustomer
SELECT 2, 3, 3200
INSERT #LoanCustomer
SELECT 2, 3, 3200

--Select * from #LoanCustomer


——————————————————————————–

Combine Result of three table from PIVOT Query:


DECLARE
@sql VARCHAR(MAX)
,@LoanType VARCHAR(MAX)


SELECT
@LoanType = ISNULL(@LoanType + ', ', '') + '[' + LoanName + ']'
FROM (
SELECT
DISTINCT
l.LoanName
FROM #LoanCustomer lc
INNER JOIN #LoanType l ON lc.LoanId = l.LoanId
) x


SET @sql = '
SELECT
CustomerName
,' + @LoanType +
Sample for Dynamic PIVOT


FROM (
SELECT
c.CustomerName,
l.LoanName,
lc.Amount
FROM #LoanCustomer lc
INNER JOIN #customer c ON lc.CustomerId = c.CustomerId
INNER JOIN #LoanType l ON l.LoanId = lc.LoanId
) dataList
PIVOT
(
SUM(Amount)
FOR LoanName IN(' + @LoanType+ ')
) X '


EXEC (@sql)


 

This Script is Prepared by Netra Prasad Acharya (MSSQL Developer)

MSSQL Hash Algorithms

MSSQL Hash Algorithms:

There are two forms of encryption algorithms: one-way and two-way. Two-way algorithms allow you to encrypt and decrypt data. One-way algorithms will only encrypt data without any ability to decrypt.

A hash algorithm is the simplest way to encrypt data, however, once encrypted, you can’t decrypt the data. Your data remains secure, ensuring that in order to “decrypt” the contents of a column you would first have to know the original data value.

--Different hash algorithms produce different hash values
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('MD5', @TEST_VAL)
SELECT @TEST_VAL = 'SQL Server'
SELECT HashBytes('SHA1', @TEST_VAL)
GO

--Hash values are case sensitive
DECLARE @TEST_VAL varchar(100)
SELECT @TEST_VAL = 'sql'
SELECT HashBytes('SHA1', @TEST_VAL)
SELECT @TEST_VAL = 'SQL'
SELECT HashBytes('SHA1', @TEST_VAL)
GO

SUBSTRING Function

See the Uses of SUBSTRING, CHARINDEX, REPLACE Functions in MSSQL.


SELECT
'3/6/2010' as OLD_FORMAT,
charindex('/','3/6/2010',1) [FIRST/POSITION],
charindex('/','3/6/2010',3) [2nd/POSITION],
replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','') as MM,
replace(left('3/6/2010',2),'/','') as DD,
right('3/6/2010',4) as YYYY,

replace( SUBSTRING ('3/6/2010', charindex('/','3/6/2010',1), 2),'/','')+'/'+
replace(left('3/6/2010',2),'/','')+'/'+
right('3/6/2010',4) as NEW_FORMAT

Example to convert date  dd/mm/yyyy to mm/dd/yyyy

Follow

Get every new post delivered to your Inbox.