Stored Procedures & Parameters

SQL Server / Sybase Stored Procedures & Parameters

-- Sybase Syntax to Select Parameters used in All Available Stored Procedures
select * from SYSPROCPARM

-- SQL Server Syntax to Select Parameters used in All Available Stored Procedures
select * from sys.parameters


-- Sybase Syntax to Select All Available Stored Procedures
select * from sysprocedure

-- SQL Server Syntax to Select All Available Stored Procedures
select * from sys.procedures


-- To get the list of both input and output parameters used in every
stored procedure use the following syntax

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
order by sp.proc_name

--SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
order by sp.name


-- Procedures and their Input Parameters

-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_in = 'Y'
order by sp.proc_name


-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 0
order by sp.name



-- Procedures and their Output Parameters
-- Sybase
select sp.proc_name, spa.parm_name from sysprocedure sp, SYSPROCPARM spa
where sp.proc_id = spa.proc_id
and parm_mode_out = 'Y'
order by sp.proc_name

-- SQL Server
select sp.name, spa.name from sys.procedures sp, sys.parameters spa
where sp.object_id = spa.object_id
and spa.is_output = 1
order by sp.name

Coalesce Function

Coalesce Function

Displays the first nonnull expression among its arguments

-- returns Iam Not a Null
Select Coalesce (Null, Null, 'Iam Not a Null', Null) as NotNull

This function will be used to skip the NULL columns and get the ones that have value. This function can be maximum utilised in situations where you need to get one value from a group of columns that could contain null.

For example, Let us assume that you have a contact table with office_phno, mobile_nos, home_phnos in it and you need to send them greetings. You can use Coalesce Function to get the non null number according to priority

Select Coalesce (Office_phno, mobile_nos, home_phnos, 'No Ph No Available) as ContactNo

Know the version of the DB

Database Version - SQL Server/ Sybase

Select @@Version

Print @@Version

Apart from the SQL Server Version, It also provides processor architecture, build date, and operating system for the current installation

The above information can also be retrieved from the following stored procedure

Exec xp_msver

Keywords: Version, xp_msver, System Stored Procedure

SQL Adding or Subtracting Dates

********************Adding or Subtracting Dates********************

If the requirement is to calculate the age of a person six months from now, dateadd function can help

Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, 6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 Desc

subtracting dates is equally easy:

Select BirthDate, DateDiff(year, BirthDate, Dateadd(month, -6, getdate())) as BirthDateAfterSix from HumanResources.Employee Order By 1 Desc

GetAge - Get Age Using SQL Query

How to calculate Age using SQL Query

DateDiff function can be used to get the Age from the birth date

Select BirthDate, DateDiff(year, BirthDate, getdate()) as Age from HumanResources.Employee

Tie in Max or Min Function - WITH TIES

If you have two employees with the same birth date - the youngest/oldest employee has a tie. Then use of WITH TIES clause will solve it.

If WITH TIES is also specified, all rows that contain the last value returned by the ORDER BY clause are returned, even if doing this exceeds the number specified by expression.

Top 1 could return two or even more rows in case of a Tie

Select TOP 1 WITH TIES BirthDate from HumanResources.Employee Order By 1 Desc

WITH TIES can be used to get the matching records of the last/first even if it is more than the given number or percent

WITH TIES requires an ORDER BY clause.

Youngest Employee - SQL TOP

********************Youngest & Oldest Employee********************

To get the youngest / olderst employee, two different methods are shown here.

1. Using Max and Min Functions
2. Using Top in Select Statement

'' Youngest Employee (Reference : AdventureWorks)


Select Min(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Asc
Select TOP 1 * from HumanResources.Employee Order By BirthDate Asc

Select * from Person.Contact where ContactID = (Select TOP 1 EmployeeID from HumanResources.Employee Order By BirthDate Asc)

'' Oldest Employee (Reference : AdventureWorks)


Select Max(BirthDate) from HumanResources.Employee
Select TOP 1 BirthDate from HumanResources.Employee Order By 1 Desc










Free Search Engine Submission






AddMe - Search Engine Optimization


Homerweb Search