TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Multiple Search Dynamic SQL-Query
Miku Kumar
Apr 19
2016
Code
587
0
1
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
ALTER
PROCEDURE
[dbo].[Proc_Test]
--@Division= '''A'',''B'',''C'''
(
@
name
VARCHAR
(50) =
NULL
,
@hireDateFrom
VARCHAR
(50) =
NULL
,
@hireDateTo
VARCHAR
(50) =
NULL
,
@Country
VARCHAR
(50) =
NULL
,
@Salary
INT
=
NULL
,
@Division
VARCHAR
(50) =
NULL
)
AS
BEGIN
DECLARE
@WhrCls
VARCHAR
(5000)
SET
@WhrCls =
' WHERE 1=1 '
------------------------------------------------------------------------------------------
IF (
ISNULL
(@
name
,
''
) <>
''
)
BEGIN
SET
@WhrCls = @WhrCls +
'AND ISNULL(name,'
''
')='
''
+ @
name
+
''
''
END
------------------------------------------------------------------------------------------
IF (
ISNULL
(@hireDateFrom,
''
) <>
''
)
BEGIN
SET
@WhrCls = @WhrCls
+
'AND CONVERT(Date,CONVERT(VARCHAR,hireDate,1)) >= CONVERT(DATE,'
''
+ @hireDateFrom +
''
')'
END
------------------------------------------------------------------------------------------
IF (
ISNULL
(@hireDateTo,
''
) <>
''
)
BEGIN
SET
@WhrCls = @WhrCls
+
'AND CONVERT(Date,CONVERT(VARCHAR,hireDate,1)) <= CONVERT(DATE,'
''
+ @hireDateTo +
''
')'
END
------------------------------------------------------------------------------------------
IF (
ISNULL
(@Country,
''
) <>
''
)
BEGIN
SET
@WhrCls = @WhrCls +
'AND Country LIKE '
'%'
+ @Country
+
'%'
''
END
-------------------------------------------------------------------------------------------
IF (
ISNULL
(@Salary,
''
) <>
''
)
BEGIN
SET
@WhrCls = @WhrCls +
'AND ISNULL(Salary,'
''
')='
''
+ @Salary
+
''
''
END
-------------------------------------------------------------------------------------------
IF (
ISNULL
(@Division,
''
) <>
''
)
BEGIN
SET
@WhrCls = @WhrCls +
' AND Division IN('
+ @Division +
') '
END
-------------------------------------------------------------------------------------------
DECLARE
@Stmt
VARCHAR
(5000)
SET
@Stmt = '
SELECT
name
,
hireDate ,
CASE
WHEN
Country=
''
India
''
THEN
''
Hindustan
''
WHEN
Country=
''
UK
''
THEN
''
England
''
WHEN
Country=
''
USA
''
THEN
''
America
''
WHEN
Country=
''
Japan
''
THEN
''
Japanies
''
END
AS
[Country],
Division,
Salary
FROM
dbo.Tbl_Emp ' + @WhrCls
PRINT ( @Stmt )
EXEC
(@Stmt)
END
Multiple Search