SQL Question




Call stored procedure from another stored procedure in SQL Server
//Procedure 1
Create PROCEDURE  stp_Test1
as
begin
    select * from table
end

//Procedure 2
Create  PROCEDURE   stp_Test2
as
begin
     // To call the Procedure stp_Test1 in this procedure
     // Its return that Procedure output.
     exec stp_Test1    
end

“Call Stored procedure from within another stored procedure, return values”

//first sp
CREATE  PROCEDURE  USP_GetDetail
 (
@Number VARCHAR (100)
)
BEGIN
select  No, Name, Mark, Results from tablename where Number=@Number
END

//second sp
CREATE PROCEDURE procedurename2
 (
@Number VARCHAR (100)
)
AS

Declare Table @tempteble(@Number VARCHAR(100), Name VARCHAR(100), Mark VARCHAR(100), Results VARCHAR(1))

BEGIN
INSERT INTO @tempteble(Number, Name , Mark , Results)
Exec  USP_GetDetail @Number
END


 

How to call function in stored procedure ?
USE [TestOil]
GO
/****** Object:  UserDefinedFunction [dbo].[Func_GetLocationName]    Script Date: 08/20/2013 10:55:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Nishikant>
-- Create date: <24-05-2013>
-- Description:   <>
-- =============================================
ALTER FUNCTION [dbo].[Func_GetLocationName]
(
      @LOC_ID INT
)
RETURNS NVARCHAR(50)
AS

BEGIN

      Declare @LocationName NVARCHAR(50);
            SET @LocationName=null;
     
            SELECT @LocationName=Location_Name FROM LocationMaster WHERE Location_Code=@LOC_ID
     
      RETURN @LocationName

END




This query will give 4th highest salary record but will show only 1 highest record if even if there are multiple duplicate salary records.

SELECT TOP 1 * FROM [dbo].[Employee]
WHERE [Salary] NOT IN 
(
  SELECT DISTINCT TOP 3 [Salary] FROM [dbo].[Employee]
  ORDER BY [Salary] DESC
)
ORDER BY [Salary] DESC

select * from SalaryTbl

--1st highst salary using Arg Max function
select MAX(salary) from SalaryTbl
--Way 1 Here is another SQL query to find second highest salary using subquery and MAX KEY WORD:
--2st highst salary
select MAX(salary) from SalaryTbl where salary < (select MAX(salary) from SalaryTbl)
--Way 2  Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:
--Highst n=1,2, 3rd salary
SELECT MAX(Salary) as 'Salary' from SalaryTbl
where Salary NOT IN
(
SELECT TOP n-1 (Salary) from SalaryTbl ORDER BY Salary Desc
)

SELECT MAX(Salary) as 'Salary' from SalaryTbl
where Salary NOT IN
(
SELECT TOP 2 (Salary) from SalaryTbl ORDER BY Salary Desc
)
--Way 3 1,2rd,3 highest salary using TOP keyword of SQL Server database
--Higst 1,2rd,3 salary
select top 1 Salary from (select top 3 salary from SalaryTbl order by salary desc)  as  sal order by salary

--top show, top most 2 value  from table
select top 2 salary from SalaryTbl


Date Operation
select GETDATE()
Note: VARCHAR(10) mean’s 09/18/2013(this is 10)
SELECT CONVERT(VARCHAR(10),GETDATE(),100) AS DATEONLY
OutPut: DATEONLY
          Sep 18 2013(MonthinWord dd yyyy)
SELECT CONVERT(VARCHAR(10),GETDATE(),101) AS DATEONLY
OutPut: DATEONLY
          09/18/2013(MM/dd/yyyy)
SELECT CONVERT(VARCHAR(10),GETDATE(),102) AS DATEONLY
OutPut: DATEONLY
          2013.09.18(yyyy/MM/dd)
SELECT CONVERT(VARCHAR(10),GETDATE(),103) AS DATEONLY
OutPut: DATEONLY
          18/09/2013(dd/MM/yyyy)
SELECT CONVERT(VARCHAR(10),GETDATE(),104) AS DATEONLY
OutPut: DATEONLY
          18.09.2013(dd.MM.yyyy)
SELECT CONVERT(VARCHAR(10),GETDATE(),105) AS DATEONLY
OutPut: DATEONLY
          18-09-2013(dd-MM-yyyy)
SELECT CONVERT(VARCHAR(10),GETDATE(),106) AS DATEONLY
OutPut: DATEONLY
          18 Sep 201(dd MonthinWord yyyy)
SELECT CONVERT(VARCHAR(10),GETDATE(),107) AS DATEONLY
OutPut: DATEONLY
          Sep 18, 2013(MonthinWord dd, yyyy)




Time portion
SELECT CONVERT (VARCHAR (10), GETDATE (),108) AS HourMinuteSecond
OutPut: HourMinuteSecond
          12:20:35 (hh:mi:ss)
SELECT LTRIM(RIGHT(CONVERT (VARCHAR (20), GETDATE (), 100), 7))
Or
SELECT LEFT((SELECT CONVERT (VARCHAR (20),CAST(GETDATE () AS TIME), 100)),8)
OutPut: HourMinuteSecond
          12:35PM (hh:mi am/pm)

 

wildcard convention in sql server 2008
Wildcard mean’s Like
Wildcard character:-
%(percentage) means:- Any string of zero or more characters.
_(UnderScore) means:- Any single character.
[] mean’s:-Any single character within the specified range ([a-f]) or set ([abcdef]).
[^] maean’s:-Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

 
How to get the last inserted record id from sql server?



@@IDENTITY is the last identity inserted using the current SQL Connection. This is a good value to return from an insert stored procedure, where you just need the identity inserted for your new record, and don't care if more rows were added afterward.
SCOPE_IDENTITY is the last identity inserted using the current SQL Connection, and in the current scope -- that is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed. Frankly, I have never had a reason to use this.
IDENT_CURRENT (tablename) is the last identity inserted regardless of connection or scope. You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.

DECLARE @ID BIGINT

INSERT INTO dbo.TABLE_ID (Table_NAME)
SELECT 'Table_Products'

SELECT @ID = SCOPE_IDENTITY()

INSERT INTO dbo.Table_Products (ID, Product_Name)
SELECT @ID, 'SomeProduct'

 



INSERT INTO SampleTable(EmpName,Salary)
SELECT 'Anil','20000'
UNION ALL
SELECT 'Anil','30000'
UNION ALL
SELECT 'AJIT','40000'
UNION ALL
SELECT 'Rajan','50000'
UNION ALL
SELECT 'Ajit','60000'
Union ALL
SELECT 'ANIL','70000'
UNION ALL
SELECT 'Nishikant','80000'
(7 row(s) affected)

select * from SampleTable
ID   EmpName     Salary
---  ---------  --------- 
1    Anil        20000.00
2    Anil        30000.00
3    AJIT        40000.00
4    Rajan       50000.00
5    Ajit        60000.00
6    ANIL        70000.00
7    Nishikant   80000.00

(7 row(s) affected)
Count Duplicate Record-Rows

SELECT EmpName, COUNT(*) TotalCount
FROM SampleTable
GROUP BY EmpName
--HAVING COUNT(*) > 1
--ORDER BY COUNT(*) DESC

EmpName    TotalCount
---------  ------------- 
AJIT          2
ANIL          3
Nishikant     1
Rajan         1

(4 row(s) affected)
--Delete Duplicate Record-Rows
DELETE FROM SampleTable
WHERE ID NOT IN
(SELECT MAX(ID) FROM SampleTable GROUP BY EmpName)

ID    EmpName   Salary
---  --------   ------
4     Rajan    50000.00
5     Ajit     60000.00
6     ANIL     70000.00
7    Nishikant 80000.00

(4 row(s) affected)
--Select Record-Rows
select * from SampleTable
--Droup table SampleTable
How to find even and odd Record in sql server 2008

select distinct Category_Code,Category_Name from CategoryMaster

Category_Code        Category_Name
-------------------- ------------------- 
1                    CPU Server
2                    Monitor Server
3                    CPU
4                    MONITOR
5                    LAPTOP
6                    SCANNER
7                    PRINTER
8                    Keyboard_Logitech
9                    Basic Phone

Note:- Category_Code –Must be Identity Column.
select distinct Category_Code,Category_Name from CategoryMaster where (Category_Code%2)=0
Category_Code        Category_Name
-------------------- --------------------- 
2                    Monitor Server
4                    MONITOR
6                    SCANNER
8                    Keyboard_Logitech


select distinct Category_Code,Category_Name from CategoryMaster where (Category_Code%2)=1

Category_Code        Category_Name
-------------------- ---------------- 
1                    CPU Server
3                    CPU
5                    LAPTOP
7                    PRINTER
9                    Basic Phone



ORDER BY Clause By default asc
select * from SampleTable
order by EmpName

ID    EmpName  Salary
----  -------- -------
3     AJIT     40000.00
5     Ajit     60000.00
6     ANIL     70000.00
1     Anil     20000.00
2     Anil     30000.00
7     Nishikant80000.00
4     Rajan    50000.00

(7 row(s) affected)
select * from SampleTable
order by EmpName  DESC

Note :- order by as per name it will show desc

ID   EmpName  Salary
--  ---------- ------ 
4    Rajan    50000.00
7    Nishikant80000.00
6    ANIL     70000.00
1    Anil     20000.00
2    Anil     30000.00
3    AJIT     440000.00
5    Ajit     60000.00

(7 row(s) affected)


 
SELECT * FROM Table_2

ID                   FN
-------------------- ------------
1                    A
2                    B
3                    C
4                    D
5                    E
6                    F
(6 row(s) affected)
SELECT * FROM Table_3

id                   ln
-------------------- --------
1                    h
2                    i
3                    j
4                    k

(4 row(s) affected)
Inner Join
Note:- The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

select * from Table_2 t2
inner join Table_3 t3
on t2.id=t3.id


ID       FN       id      ln
-------- ------- -------- ----
1        A        1        h
2        B        2        i
3        C        3        j
4        D        4        k

(4 row(s) affected)
Left Join
Note:- The LEFT JOIN keyword returns all rows from the left table (table_2), with the matching rows in the right table (table_3). The result is NULL in the right side when there is no match.

select * from Table_2 t2  
left join Table_3 t3
on t3.id=t2.id

ID     FN      id    ln
------ ------ ------ ----
1      A       1     h
2      B       2     i
3      C       3     j
4      D       4     k
5      E       NULL  NULL
6      F       NULL  NULL

(6 row(s) affected)
Right Join
Note:- The RIGHT JOIN keyword returns all rows from the right table (table_3), with the matching rows in the left table (table_2). The result is NULL in the left side when there is no match.

select * from Table_3 t3  
RIGHT join Table_2 t2
on t3.id=t2.id


id      ln       ID       FN
------- -----    -------- ----
1        h        1         A
2        i        2         B
3        j        3         C
4        k        4         D
NULL     NULL     5         E
NULL     NULL     6         F

(6 row(s) affected)
Full Join(Full Outer Join)
Note:-

select * from Table_3 t3  
full join Table_2 t2
on t3.id=t2.id

id       ln   ID     FN
------- ----- ----- ----
1       h     1      A
2       i     2      B
3       j     3      C
4       k     4      D
NULL    NULL  5      E
NULL    NULL  6      F
8       l     NULL   NULL

(7 row(s) affected)



What is Temporary Tables?
These tables can be created at runtime and can do the all kinds of operations that one normal table can do.
 But, based on the table types, the scope is limited.
These tables are created inside tempdb database.



What are different type of Temporary Tables
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
  • Local Temp Table
  • Global Temp Table



What is Local Temp Tables?

Local temp tables are only available to the current connection for the user;
and they are automatically deleted when the user disconnects from instances.
Local temporary table name is stared with hash ("#") sign.

For Example:-
CREATE TABLE #LocalTempTable(UserID int, UserName varchar(50),UserAdd varchar(150))
The above script will create a temporary table in tempdb database.

 We can insert or delete records in the temporary table similar to a general table like:
insert into #LocalTempTable values ( 1, 'Nishikant','Bangalore');
 
Now select records from that table:
select * from #LocalTempTable
 
Now  clean up the data when you're done with it:
DROP TABLE #people

Example:-
 
CREATE TABLE #LocalTempTable(UserID int, UserName varchar(50),UserAdd varchar(150))
 
Insert into #LocalTempTable values (1, 'Nishikant','Bangalore');
 
Select * from #LocalTempTable
 
DROP TABLE # LocalTempTable




When we use Temporary Table?
Below are the scenarios where we can use temporary tables:
  • When we are doing large number of row manipulation in stored procedures.
  • This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
  • When we are having a complex join operation.

Points to Remember Before Using Temporary Tables

  • Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
  • Number of rows and columns need to be as minimum as needed.
  • Tables need to be deleted when they are done with their work.


What is Global Temp Tables?
Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.
For Example:-
CREATE TABLE ##NewGlobalTempTable(UserID int,UserName varchar(50),
UserAddress varchar(150))
The above script will create a temporary table in tempdb database.
 We can insert or delete records in the temporary table similar to a general table like:
insert into ##NewGlobalTempTable values ( 1, 'Abhijit','India');
Now select records from that table:
select * from ##NewGlobalTempTable
Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.



Alternative Approach: Table Variable
Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable. 
Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))
The below scripts are used to insert and read the records for Tablevariables:
 insert into @TempTableVariable values ( 1, 'Abhijit','India');
Now select records from that tablevariable:
 select * from @TempTableVariable

When to Use Table Variable Over Temp Table

Tablevariable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.


Subscribe to: Posts (Atom)

Tabs

Tab 1 Tab 2 Tab 3

Tab 1 content goes here

Tab 2 content goes here

Tab 3 content goes here
Multi-Tabbed Widget | DotNetIs