quote:

---

Originally posted by jszulc

I can't get this stored procedure to work. It is supposed to take 2 parameters and based on user selections return all employees belonging to Dept '16285' based on parameter search criteria. Any help is appreciated. I would like to also check for STATUS ='A' and ENTRY_TP = 'CTR' or 'FT'

ALTER PROCEDURE [dbo].[sp_Employees]
@SelectedCategory nvarchar(50),
@SearchWord nvarchar(200)

AS
DECLARE @SQL varchar(1000)

SET @SQL = 'SELECT ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID FROM dbo.Offboardv7 WHERE DEPTID="16285" '

IF (((@SelectedCategory IS NOT NULL) OR (@SelectedCategory != '')) AND @SelectedCategory = 'Last Name')
SET @SQL = @SQL + ' AND Last_Name like ''%' + @SearchWord + '%'''
IF (((@SelectedCategory IS NOT NULL) OR (@SelectedCategory != '')) AND @SelectedCategory = 'SID')
SET @SQL = @SQL + ' AND SID like ''%' + @SearchWord + '%'''

Print '@SQL = ' + @SQL
EXEC(@SQL)

***********************************************************
Table:
CREATE TABLE [dbo].[Offboardv7](
[ID] [int] NOT NULL,
[SID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LAST_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIRST_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MIDDLE_INITIAL] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[L_O_B] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEPT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEPTID] [int] NULL,
[EMP_STATUS] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PHONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ENTRY_TP] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NEW_COST_CENTER] [int] NULL
) ON [PRIMARY]

---

ALTER PROCEDURE [dbo].[sp_Employees]
@SelectedCategory nvarchar(50),
@SearchWord nvarchar(200)

AS

SELECT  ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID 
FROM dbo.Offboardv7 
WHERE DEPTID='16285' 
AND (CASE WHEN @SelectedCategory = 'Last Name' 
         THEN Last_Name 
         WHEN @SelectedCategory = 'SID'
         THEN SID
         END  LIKE '%' + @SearchWord + '%'
     OR @SelectedCategory ='' 
     OR @SelectedCategory IS NULL)

GO