SQL statement

Question

SQL statement

jszulc

Grasshopper

Points: 12
More actions
February 1, 2008 at 7:09 pm

#378328

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]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

I haven't tried to decipher the dynamic SQL in too much depth but what error message (if any) are you getting. Is it just not filtering the way you expect, not filtering at all, or throwing an error?

If you can only filter by one thing at a time then perhaps you should use IF statements instead as in

if @selectedCategory = 'Last Name'

SELECT ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID

FROM dbo.Offboardv7

WHERE DEPTID='16285' and

last_name like '%' + @SearchWord + '%'

else if @selectedCategory = 'SID'

SELECT ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID

FROM dbo.Offboardv7

WHERE DEPTID='16285'

and sid like '%' + @sid + '%'

else if ....

You should avoid dynamic SQL unless you absolutely really really need it. Look up "SQL Injection" on this site or using Google. Your code was exposed to such an attack.

Also, don't use double quotes around strings as, depending on the settings of QUOTED_IDENTIFIER on your server/database/connection the behaviour could be inconsistent. Use single quotes instead.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

Y, I haven't picked it apart piece by piece either. What problems are you having?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Have a play with the following, it should help you identify where the sp is failing:

DROP TABLE #Offboardv7

GO

CREATE TABLE #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)

GO

INSERT INTO #Offboardv7 ([ID], SID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, DEPTID)

SELECT 1, 'SID1', 'SMITH', 'JOHN', 'A', '16285' UNION ALL

SELECT 2, 'SID1', 'SMITH', 'ALAN', 'A', '16285' UNION ALL

SELECT 3, 'SID1', 'JONES', 'MARY', 'A', '16285' UNION ALL

SELECT 4, 'SID2', 'SMITH', 'ANNE', 'A', '16285' UNION ALL

SELECT 5, 'SID1', 'SMITH', 'AXEL', 'A', '16284'

DECLARE @SelectedCategory nvarchar(50)

DECLARE @SearchWord nvarchar(200)

SET @SelectedCategory = 'Last Name'

SET @SearchWord = 'JONES'

SELECT [ID], SID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, DEPTID

FROM #Offboardv7

WHERE DEPTID = '16285'

AND (

@SelectedCategory IS NULL

OR (@SelectedCategory = 'Last Name' AND LAST_NAME LIKE '%' + @SearchWord + '%')

OR (@SelectedCategory = 'SID' AND SID LIKE '%' + @SearchWord + '%')

)

cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ian Yates SSCoach Points: 19738 More actions · Answer 4

Chris' way is the way most go with implementing this in practice (including me) 🙂 However if you have a number of different ways it can help to make things more efficient for the query optimiser if it has a simple where clause rather than lots of OR'd statements.

ksullivan SSCrazy Points: 2346 More actions · Answer 5

Some fixes based on guesses about what may not be working as you expect.

-- No results at all or syntax error because of the double quotes around 16285. Change to single quotes or fiddle with set quoted_identifier.

-- If only one of the 2 paramaters is passed, @sql became a null string so nothing was returned. Verify both parameters have non-null values before adding to @sql.

-- If neither parameter is passed, maybe you expect an empty set? Add a select TOP 0 sentence.

-- If instead you expect all rows when no name is passed, remove the else select top 0 lines.

-- If no parameters were passed, you got an error about expected parameters. Add defaults.

alter PROCEDURE [dbo].[sp_Employees]

@SelectedCategory nvarchar(50) = null,

@SearchWord nvarchar(200) = null

AS

DECLARE @sql varchar(1000)

SET @sql = 'SELECT ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID

FROM dbo.Offboardv7

WHERE DEPTID=''16285''

and emp_STATUS =''A''

and (ENTRY_TP = ''CTR'' or ENTRY_TP = ''FT'')

' --

if len(@SelectedCategory) + len(@SearchWord) > 0

begin

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 + '%'''

end

else

SET @sql = 'SELECT top 0 ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID

FROM dbo.Offboardv7'

Print '@SQL = ' + @sql

EXEC(@SQL)

R. Brush SSCommitted Points: 1669 More actions · Answer 6

This solution allows you to eliminate the IF's. If the first if condition is not met then the WHERE Clause simply evaluates LastName = LastName. And, likewise for the SID.

I have provided three tests that you can execute to see the results. I also commented out the EXEC so only the SQL statement is output:

ALTER PROCEDURE [dbo].[sp_Employees]

@SelectedCategory nvarchar(50),

@SearchWord nvarchar(200)

AS

--test: exec dbo.sp_Employees '',''

-- exec dbo.sp_Employees 'LastName','Smith'

-- exec dbo.sp_Employees 'SID','1001'

DECLARE @sql varchar(1000)

SET @sql = 'SELECT ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID

FROM dbo.Offboardv7

WHERE DEPTID="16285"

AND LastName ' + CASE WHEN ISNULL(@SelectedCategory,'') = 'LastName'

THEN 'LIKE ' + char(39) + '%' + @SearchWord + '%' + char(39)

ELSE ' = LastName '

END + '

AND SID ' + CASE WHEN ISNULL(@SelectedCategory,'') = 'SID'

THEN 'LIKE ' + char(39) + '%' + @SearchWord + '%' + char(39)

ELSE ' = SID '

END + ''

Print '@SQL = ' + @sql

--EXEC(@SQL)

This code was fomatted in the Post Reply textbox but is unformatted when previewed. I'm don't know how to post formatted replys. Sorry.

R. Brush SSCommitted Points: 1669 More actions · Answer 7

Here is a revised version of my example that eliminates the need for the stored procedure (SP) to know what SelectedCategories are valid ahead of time. Using this approach, if in the future you add to the possible SelectedCategories the user can pass to the SP, you won't have to modify the SP.

ALTER PROCEDURE [dbo].[sp_Employees]

@SelectedCategory nvarchar(50) = '''',

@SearchWord nvarchar(200)

AS

--test: exec dbo.sp_Employees '',''

-- exec dbo.sp_Employees 'LastName','Smith'

-- exec dbo.sp_Employees 'SID','1001'

DECLARE @sql varchar(1000)

SET @sql = 'SELECT ID,SID,LAST_NAME,FIRST_NAME,MIDDLE_INITIAL, DEPTID

FROM dbo.Offboardv7

WHERE DEPTID="16285"'

+ CASE WHEN ISNULL(@SelectedCategory,'')='' THEN '' ELSE

' AND ' + @SelectedCategory + ' LIKE ' + char(39) + '%' + @SearchWord + '%' + char(39)

END

Print '@SQL = ' + @sql

--EXEC(@SQL)