Pagination and Switch Case in Where clause – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with 1 Comment on Pagination and Switch Case in Where clause – SQL Server

Example of using pagination and switch case in where clause in SQL server

After a long time i am going to write any article on SQL Server. That last article i think was before 18 months. As currently i am exploring the Salesforce and its API’s, i caught in situation to create a stored procedure and provide the offset feature, that means i needed the pagination support in my stored procedure. There is no direct keyword available in SQL Server something like LIMIT in mysql. And therefore i thought to share my solution with community. So, here we go…

I am considering that there will be table named “Employee” in which i want to search by employees firstName with pagesize and offset attribute. Continue reading “Pagination and Switch Case in Where clause – SQL Server”

Resolve error – ORA-28000: the account is locked

Author posted by Jitendra on Posted on under category Categories Oracle, SQL and tagged as Tags , with 1 Comment on Resolve error – ORA-28000: the account is locked

How to Resolve error – ORA-28000: the account is locked

It is the chance that you might come across the error “ORA-28000: the account is locked“. To unlock the account, run below sql query in Oracle using admin user.

Lets say that you want to unlock account “om” in oracle

alter user om account unlock;
grant connect, resource to om;

I hope it would be helpful for the readers.

Convert rows into comma separated values column – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags , with Leave a comment on Convert rows into comma separated values column – SQL Server

Convert rows into comma separated values column – SQL Server

Converting rows values into comma separated column value is required lots of time:

example, i want to convert :

Name
------------------------------
India
USA
Japan
China
Switzerland

to

Continue reading “Convert rows into comma separated values column – SQL Server”

Get Foreign Key related details for the Table

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with Leave a comment on Get Foreign Key related details for the Table

Get Foreign Key related details for the Table in SQL Server

Getting the foreign key detail of particular table is the most common tasks used by DBA or database developers. every time go to left panel, select table and right click to get foreign key details wastes most of the time.

So below is the script which will return the detail about foreign key of particular table:

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id)='TABLE_NAME'

Drop Function if exist – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with Leave a comment on Drop Function if exist – SQL Server

Drop the user define function from database if it exists.

Below script will help to drop the scalar function present in SQL Server 2005 if exist.

It is always good practice to write the drop statement before creating any stored procedure or the database function.
Lets say the function name is fn_GetFirstDayOfWeek

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetFirstDayOfWeek]')
AND type in (N'FN', N'IF',N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetFirstDayOfWeek]

Get the FortNight Start and End date – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with 2 Comments on Get the FortNight Start and End date – SQL Server

Get the Fort night start and end date on the basis of input provided. if date falls in first 15 day then it will give the First Fort night start and end date and if date falls after 15 day then it gives respective start and end date.

in some scenario we comes in very strange requirement. with me it happens. i needed to calculate date in which fort night the date is coming and on the basis of that calculate start and end date. so i have created below script. I hope it will be useful to readers.


DECLARE @InputDate DATETIME
SET @InputDate = '17-Jul-2010'

DECLARE @FortStartDate DATETIME
DECLARE @FortEndDate DATETIME

IF DAY(@InputDate) < 16 --First FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
SET @FortEndDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/15' AS DATETIME)
END
ELSE --Second FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/16' AS DATETIME)
SET @FortEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))
END

SELECT @FortStartDate as 'StartDate'
SELECT @FortEndDate as 'EndDate'

Get First Day and Last Day of Week – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with Leave a comment on Get First Day and Last Day of Week – SQL Server

Scalar function to get the first Day and Last day of the Week in SQL Server

This time lets create the Scalar function in SQL Server to get the First Day and Last Day of the Week.

Create below Scalar function in SQL Server to get the First Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DateTime

	-- Add the T-SQL statements to compute the return value here
	-- 1 -> Sunday, 7 -> Saturday
	SELECT @Result = DATEADD(DAY, 1- DATEPART(DW, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

Create below Scalar function in SQL Server to get the Last Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DateTime

	-- Add the T-SQL statements to compute the return value here
	-- 1 -> Sunday, 7 -> Saturday
	SELECT @Result = DATEADD(DAY, 7- DATEPART(DW, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

To get the values from function, run below script

 select dbo.GetFirstDayWeek(GETDATE()) as StartDate
 select dbo.GetLastDayWeek(GETDATE()) as EndDate

as i said, remember that to call function we have to call like dbo.functionName

Get First Day and Last Day of Month – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with 1 Comment on Get First Day and Last Day of Month – SQL Server

Get the first day and last day of the month in SQL Server using Scalar functions

One of the features i love about SQL Server is function supports. Divide your task in functions just like other programming language. In this article i am going to show you that how to display the First Day and Last Day of Month in SQL Server.

Create a below function to get the First Day of Month:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

-- Return the result of the function
RETURN @Result

END
GO

Now, Create Below Function to get the Last day of month.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))

-- Return the result of the function
RETURN @Result

END
GO

To get the values from function, run below script

select dbo.GetFirstDay(GETDATE()) as StartDate
select dbo.GetLastDay(GETDATE()) as EndDate

remember that to call function we have to call like dbo.functionName

Drop Stored Procedure if exist in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with Leave a comment on Drop Stored Procedure if exist in SQL Server

How to drop the stored procedure in SQL Server if exist

Its very frequent that we need to check whether particular store procedure exist in database or not?

If stored procedure exist then delete the stored procedure, otherwise we have to toggle between Create Procedure and Alter Procedure.

Below is the SQL to delete the stored procedure if exist.

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SPNAME]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

DROP PROCEDURE dbo.SPNAME

END

Search for Table in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags , with Leave a comment on Search for Table in SQL Server

In organization, where huge number of tables present in database. its very hard to search the particular table.

Here few SQL queries to search the table name by pattern.

Search For Table whose name starts with A

SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE
OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
AND OBJECT_NAME(OBJECT_ID) like 'A%'

Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily. I frequently run following query to verify that all the developers are creating all the tables with Clustered Index.

SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE
INDEX_ID = 0 AND
OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
List of Non clustered Tables in SQL Server 2005
List of Non clustered Tables in SQL Server 2005