Get Recursive all childs by parentid in sql server

Get Recursive all childs by parentid in sql server 

Introduction : In this article i will show you how to get all recursive childs by parentid in sql server .To get all childs i have used recursive query using with CTE ( Common Table Expressions ) . It is very simple to with parentid and childId relationship table.I have written query for getting recursive child records in sql server is as follow .

Table design for Relationship : 

Query for get all childs by parentid in sql server : 
 

declare @ParentId as int;
set @ParentId =  1;

WITH RecursiveTable (ProductId, ParentId,ProductName, Level)
AS
(    
    SELECT      MaintTab.ProductId, 
                MaintTab.ParentId, 
                MaintTab.ProductName ,
                0 AS Level
    FROM ProductTable AS MaintTab
    WHERE ParentId = @ParentId
    
    UNION ALL
    
    SELECT  MaintTab.ProductId, 
                MaintTab.ParentId, 
                MaintTab.ProductName ,
                LEVEL + 1
    FROM ProductTable AS MaintTab
        INNER JOIN RecursiveTable Rtab ON
        MaintTab.ParentId = Rtab.ProductId
)
SELECT * FROM RecursiveTable

Output of query : 

Related Other posts

Pass arguments to String.Format method in c# Get Recursive all childs by parentid in sql server Merge word document files in asp.net c#

Validation Using Jquery in Asp.net page DOC file to RTF format in asp.net C#. Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Find all references Of Object Table , Stored Procedures, Scalar function In sql Server

Find all references Of Object Table , Stored Procedures, Scalar function In sql Server

Following is the query by which you can get all references of object  Like Table , Stored procedures ,Scalar function ..etc In Scalar function ,Trigger ,View ,stored procedure ..etc.

Many times we require that find all stored procedures that reference a given table .

By Following to search all objects in a database containing a certain string .

SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%any text to search for%'
ORDER BY Location, ObjectName

If pass table object name then it show all references table in Stored procedures ,Scalar function ..etc .

If pass Stored procedure object name then it show all references Stored procedures in Stored Procedures ..etc .

Related Other posts

Html Code in Blodspot Radiobuttonlist Example Image In Datalist

Database backup in sql Server CheckboxList example Datetime format in gridview

Highest second record in sql Nested gridview Checkboxs in gridview

Fetch Highest second Record In SQL Server

Fetch Highest second Record In SQL Server.

Introduction : In this article i will show you how to fetch second highest salary record in sql server database .Here you can fetch entire second highest row from the table in sql server .To achieve the highest record i have used Row_number().

Example :  Following is example which fetch second highest salary record from the table in sql server .

Select * from ( select  Row_number() over (  order by salary desc ) as Row_INDEX , * From Data ) as Temp where Row_INDEX = 2

Related Other posts

Html Code in blog Radiobuttonlist Example Image In Datalist Merge docs files in c#

Database backup in sql Server CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

To take back up of database In Sql Server

To take back up of database In SQL Server Database .


Follow the steps :

I have explained how to take back up of database using SQL Server Management Studio .

1. Fisrt Connect to the appropriate SQL Server Database Engine .

2. Expand Databases and select the databse which you want to take a back up  .

3. Rigth Click On Database and Go to the Task Option and In task Option Click on the Back Up option .

4.After then One dialog box will open then Add the destination file path where you want to save the back up file .

5.Click on the add button for the destination path .

6.One Dialog Box will open then choose the destination path and give the file name with .bak extension ,Like TestBaup.bak .

7.Finally you need click on OK Button .


8.Once back Up of database complete , One message will prompt that "The backup of database 'DatabaseName' completed successfully .

Get column values by comma separated in Sql Server

Get column values by comma separated in Sql Server
Introduction : In this article i will explain you how to get column's values by comma separated in sql server .
It is very easy to get all values of column in one string as separated by comma .

Query for get comma separated value of column in sql server :

Declare @Description varchar(4000)
select @Description = coalesce(@Description + ',' , '') + Name FROM UserTable
print @Description 

Output of query :

seta hamid , bhaumik vora ,vinayak , dave 

Related Other posts

Html Code in blog Radiobuttonlist Example Image In Datalist Merge docs files in c#

Database backup in sql Server CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Convert integer in time In Sql Server

Convert Integer in time In Sql Server

Introduction : In this article i will show you how to convert Integer to time format in sql server .I have created function will return time as a string .Its takes parameter as a second in integer and return time .

CREATE function [dbo].[GetMinutesFromInt](@seconds int)  
returns  varchar(20)  
as
 begin  
   declare @time varchar(20)  
   set @time =    CASE WHEN @seconds/3600<10 THEN '0' ELSE '' END   + RTRIM(@seconds/3600)  + ':' + RIGHT('0'+RTRIM((@seconds % 3600) / 60),2)  + ':' + RIGHT('0'+RTRIM((@seconds % 3600) % 60),2)  
   return(@time) 
 end 

Call Function :

select dbo.GetMinutesFromInt(70)

Output :

00:01:10

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Convert dateTime in format date time using Convert Function

Convert dateTime in format date time using Convert Function

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Following are the Date Time format outputs if you pass related parameter to Convert function




-------------------------------------------------------------------------------------
Date OutPut
-------------------------------------------------------------------------------------

select convert( varchar , getdate() , 101 ) mm / dd / yyyy
select convert( varchar , getdate() , 102 ) yy.mm.dd
select convert( varchar , getdate() , 103 ) dd/mm/yy
select convert( varchar , getdate() , 104 ) dd.mm.yy
select convert( varchar , getdate() , 105 ) dd-mm-yy
select convert( varchar , getdate() , 106 ) dd mon yy
select convert( varchar , getdate() , 107 ) Mon dd, yy
select convert( varchar , getdate() , 108 ) hh:mm:ss
select convert( varchar , getdate() , 109 ) mon dd yyyy hh:mi:ss:mmmAM (or PM)
select convert( varchar , getdate() , 110 ) mm-dd-yy
select convert( varchar , getdate() , 111 ) yy/mm/dd
select convert( varchar , getdate() , 112 ) yymmdd
select convert( varchar , getdate() , 113 ) dd mon yyyy hh:mm:ss:mmm(24h)
select convert( varchar , getdate() , 114 ) hh:mi:ss:mmm(24h)

--------------------------------------------------------------------------------------

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Cursor In Sql Server

Cursor In Sql Server

Cursor is a database Object used to manipulate data on a row-by-row basis ..

Following are step to create Cursor .

Declaring a Cursor
Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-Cursur standard and a syntax using a set of Transact-SQL extensions.
This is SQL-Cursur Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR select_statement

[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

Following is Table-Value Function In which I have Used Cursor ::

FUNCTION [dbo].[fntbGetRevenueOfEmployees]()
RETURNS @EmployeeRevenue TABLE ( OrderDate Date , EmployeeID int ,Employee1 decimal(16,2) , Employee2 decimal(16,2), Employee3 decimal(16,2) ,Employee4 decimal(16,2)  , 
Employee5 decimal(16,2)   )
AS
BEGIN        
DECLARE  Name_Cursor CURSOR FOR 
Select  convert( varchar , CustomerOrders.OrderDate ,101)  ,         Employees.EmployeeID , sum(dbo.fnItemPriceTotal(customerorderDetails.Qty        ,customerorderDetails.Price,ISNULL(customerorderDetails.Double1,0))) as TotalForRow From  CustomerOrders
left join customerorderDetails on CustomerOrders.OrderID = customerorderDetails.OrderID
left join Accounts on Accounts.AccountID =  CustomerOrders.CustomerID
left join Employees ON Accounts.SalesPersonID = Employees.EmployeeID
group by convert( varchar , CustomerOrders.OrderDate ,101)  , Employees.EmployeeID
order by convert( varchar , CustomerOrders.OrderDate ,101)

DECLARE @C_OrderDate  as Datetime
DECLARE @C_OrderTotal as  decimal(16,2)
DECLARE @C_EmployeeID as Int 
Declare @intCount as int
declare @TempEmpId as int
set @intCount = 0

OPEN Name_Cursor;
FETCH NEXT FROM Name_Cursor INTO @C_OrderDate ,@C_EmployeeID , @C_OrderTotal ;
WHILE @@FETCH_STATUS = 0
BEGIN
 
   insert into @EmployeeRevenue values( convert(varchar , @C_OrderDate ,101) , @C_EmployeeID , @C_OrderTotal , 0 ,0,0,0 ) ;

   FETCH NEXT FROM Name_Cursor INTO @C_OrderDate ,@C_EmployeeID , @C_OrderTotal ;

END
close Name_Cursor  
deallocate Name_Cursor  
return  
end

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page

Using Command Prompt Options to Install SQL Server Express

Using Command Prompt Options to Install SQL Server Express :-

Installation Of Sql Server 2005 Using Command Prompt( Silent Mode )

Here is the command line I am using : -

Start /wait SQLEXPR32.exe   /qb INSTANCENAME=MSSQLSERVER  INSTALLSQLDIR="C:\Program Files" TALLSQLSHAREDDIR="C:\ProgramFiles" ADDLOCAL=All ERRORREPORTING=1 SQLAUTOSTART=1 SQMREPORTING=1 SAPWD=Lobby@123 SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 ERRORREPORTING=1

To install SQL Server Express 2008 following is the command-line

SQLEXPR32_x86_ENU.exe /q /ACTION=Install /IACCEPTSQLSERVERLICENSETERMS
   /INSTANCENAME=SQLSERVER /ROLE=AllFeatures_WithDefaults
   /ADDCURRENTUSERASSQLADMIN=TRUE /SQLSVCACCOUNT="NT AUTHORITY\Network Service"
   /FEATURES=SQL

Related Other posts

Sorting, Paging, Add, Update, Delete in GridView Check / Uncheck all checkbox in treeview using jquery Image In Datalist Merge docs files in c#

Bind Treeview in asp.net c# using dataset CheckboxList example Datetime format in gridview Send mail in asp.net c#

Highest second record in sql Nested gridview Checkboxs in gridview Validation using jquery in asp.net page