Showing posts with label MS Sql. Show all posts
Showing posts with label MS Sql. Show all posts

Sunday, 24 June 2018

Get Auto Increment No in Scalar Function in MS Sql

CREATE FUNCTION [dbo].[GETBookingOrderNo]()
RETURNS CHAR(9)
AS
BEGIN
DECLARE @lastval NVARCHAR(50)
SET @lastval = (SELECT MAX(BookingOrderNo) FROM BookingDetailMaster)
IF @lastval IS NULL SET @lastval = 'RO 000001'
--SET @lastval='RO 000009'
DECLARE @i INT
SET @i = RIGHT(@lastval,6) + 1
RETURN 'RO ' + RIGHT('0000' + CONVERT(VARCHAR(10),@i),6)
END
GO

SELECT [dbo].[GETBookingOrderNo]()

Monday, 26 March 2018

Split String Scalar Valued Function

CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END
      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
           
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
           
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END
      RETURN
END
GO

Test ::

SELECT Item

FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')

Saturday, 16 December 2017

Convert Multiple Rows into One Comma Separated Values in SQL server 2008

Convert Multiple Rows into One Comma Separated Values in SQL server 2008

comma sep











Solutions
For the above problem, below are different queries to achieve this.
Query 1: Using “COALESCE” function
DECLARE @temp VARCHAR(MAX)
SELECT
 @temp = COALESCE(@temp+’, ‘ ,”) + title
FROM
 [dbo].[country]
SELECT
 @temp ‘title’
Query 2: Using without “COALESCE” function
DECLARE @temp VARCHAR(MAX)
SET
 @temp = ”
SELECT
 @temp = @temp + title + ‘, ‘
FROM
 [dbo].[country]
SELECT
 SUBSTRING(@temp, 0, LEN(@temp))
Query 3: Using “FOR XML PATH”
DECLARE @temp VARCHAR(MAX)
SET
 @temp = (SELECT ‘, ‘ + cast(s.title as varchar)
FROM [dbo].[country] 
s
ORDER BY s.title

FOR XML PATH(”
))
SELECT
 SUBSTRING(@temp, 2, 200000) AS title

How to Change Password in SQL Server 2012

How to Change Password in SQL Server 2012


To Change Password Programmatically
  1. Open the SQL Server Management Studio.
  2. Click new query to open a New Query.
  3. Paste and execute the following code.
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N’New Password’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Saturday, 18 November 2017

Replace Empty String To Value Zero ("0")

declare @value varchar(10);
set @value = '';
SELECT COALESCE(NULLIF(@value,''), '0')

Wednesday, 4 October 2017

Cursor Demo




ALTER PROCEDURE [dbo].[UpdateStateFromNAVtoCRM]
AS
--TRUNCATE TABLE [dbo].[State_Mst]

--INSERT INTO [dbo].[State_Mst]
--(
--[Code],[Name]
--)
--SELECT [Code],
--      [Description]   
--FROM [PCAPL07122016].[dbo].[Prakash Chemicals$State]

DECLARE @Code NVARCHAR(250)
DECLARE @Name NVARCHAR(450)

DECLARE @StateCodeCount NVARCHAR(50)

DECLARE cur_emp CURSOR
STATIC FOR
SELECT [Code],[Description]
FROM [PCAPL07122016].[dbo].[Prakash Chemicals$State]
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
FETCH NEXT FROM cur_emp INTO @Code,@Name
WHILE @@Fetch_status = 0
BEGIN

SET @StateCodeCount=''
SET @StateCodeCount=ISNULL((SELECT TOP 1 ISNULL(Code,'')
FROM [dbo].[State_Mst]
WHERE LOWER(Code)=LOWER(@Code)),'')


IF(LEN(@StateCodeCount)=0)
BEGIN
INSERT INTO [dbo].[State_Mst]
(
[Code],[Name]
)
VALUES
(
@Code,@Name
)
END
ELSE
BEGIN
UPDATE [dbo].[State_Mst]
SET [Name]=@Name
WHERE LOWER(Code)=LOWER(@Code)
END
FETCH NEXT FROM cur_emp INTO  @Code,@Name
END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF 

Wednesday, 30 August 2017

Find Duplicate Rows in MSSql Table

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Sunday, 27 August 2017

What is Trigger in SQL Server?

What is Trigger in SQL Server?
A trigger is especial type of Stored Procedure that executes when certain action performed on Table like:-
  • Insert
  • Delete
  • Update

It’s a Database Object which is bound to a table and is executed automatically.

There are Two type of Trigger:-
  • After Trigger(For Trigger)
  • Instead of Trigger


After Trigger are divided into Three type:-
  1. For Insert
  2. For Delete
  3. For Update


Instead of Trigger are divided into Three type:-
  1. Instead of Insert
  2. Instead of Delete
  3. Instead of Update

Hyrarchy in SQL Table using stored procedure and cursor within cursor

Hyrarchy in SQL Table using stored procedure and cursor within cursor





--syntex for creating Table named hyrarchy

create table hyrarchy(id int primary key identity(1,1),catname
varchar(50),rel varchar(1),lev int,relid int)

--syntex for Inserting Data in  Table named hyrarchy

insert into hyrarchy(catname ,rel,lev ,relid) values('Student','p',0,0)
insert into hyrarchy(catname ,rel,lev ,relid) values('Book','c',1,1)
insert into hyrarchy(catname ,rel,lev ,relid) values('Pencil','c',2,1)
insert into hyrarchy(catname ,rel,lev ,relid) values('School','p',0,0)
insert into hyrarchy(catname ,rel,lev ,relid) values('Teacher','c',1,4)
insert into hyrarchy(catname ,rel,lev ,relid) values('Class','c',2,4)
insert into hyrarchy(catname ,rel,lev ,relid) values('Copy','c',3,1)



--syntex for creating stored procedure

create proc sp_hyrarchyoftable
as
begin
--syntex for declaring variables
declare @id int
declare @catname varchar(50)
declare @rel varchar(1)
declare @lev int
declare @relid int
declare @catnemrel varchar(100)
--syntex for declare cursor
declare cursorforlevel cursor fast_forward for selectid,catname,rel,lev,relid from hyrarchy where relid=0
--syntex for open cursor
open cursorforlevel
--syntex for create temporary table
create table #temp1(id int,CategorName varchar(50),Relation varchar(1),Lvelint,Relation_id int,)
--fetching data row by row
fetch next from cursorforlevel into @id,@catname,@rel,@lev,@relid
--checking all rows are fetched
while @@FETCH_STATUS=0
begin
--fetched row data are inserted into temp table #temp1
insert into #temp1 values(@id,@catname,@rel,@lev,@relid)
--syntex for declare cursor
declare cursorforlevel1 cursor fast_forward for selectid,catname,rel,lev,relid from hyrarchy where relid=@id
--syntex for open cursor

open cursorforlevel1
--fetching data row by row
fetch next from cursorforlevel1 into @id,@catname,@rel,@lev,@relid
--checking all rows are fetched
while @@FETCH_STATUS=0
begin
--fetched row data are inserted into temp table #temp1
insert into #temp1 values(@id,@catname,@rel,@lev,@relid)
--move data fetching in next row if all row data not fetched
fetch next from cursorforlevel1 into @id,@catname,@rel,@lev,@relid
end
-- Closing cursor 

close cursorforlevel1
-- Dealocate cursor from Memory 

deallocate cursorforlevel1
--move data fetching in next row if all row data not fetched

fetch next from cursorforlevel into @id,@catname,@rel,@lev,@relid
end
-- Closing cursor 

close cursorforlevel
-- Dealocate cursor from Memory 

deallocate cursorforlevel

select * from #temp1

end

React Hooks - custom Hook

  v CustomHook Ø React allows us to create our own hook which is known as custom hook. Example – 1 localStorage Demo Step-1 Create ...