SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Error in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 09/23/2007 :  15:01:13  Show Profile  Reply with Quote
Guys,

I working on stored procedure which handles conditional where clause based in null or not null values
paased. My stored procedure is based of the example - PROCEDURE [dbo].[Emp_Svc_Cost] from

http://www.yukonxml.com/chapters/apress/reportingservices/bestpractices/

but when I try to create the stored procedure I get an error

"Msg 102, Level 15, State 1, Procedure SP_RPT, Line 28
Incorrect syntax near 'END'."

CREATE PROCEDURE SP_RPT
@CASE_NUMBER VARCHAR(20)=NULL,
@PARTY_ID BIGINT=NULL,
@LAST_NAME VARCHAR(250)=NULL
AS
SELECT [CASE_ID],[CASE_NUMBER],[CASE_TYP_CD],[CASE_TYP_DESC],[CASE_STATUS_CD]
,[CASE_STS_DESC],[COURT_CD],[COURT_NAME],[PARTY_ID],[LAST_NAME],[MIDDLE_NAME]
,[FIRST_NAME],[DISPLAYNAME],[CI_ANOTD_OFNS_REF_NUM],[CI_ANOTD_OFNS_EFF_DT]
,[ANOTD_OFNS_EFF_DT],[ANOTD_OFNS_REF_NUM],[CNT_DISPOSITION_DT]
,[DI_ANOTD_OFNS_REF_NUM],[DI_ANOTD_OFNS_EFF_DT],[CI_OFFENSE_CLASS_CD]
,[CI_OFFENSE_CLASS_DESC],[CI_OFFENSE_TYP_CD],[CI_OFFENSE_TYP_DESC],[OFFENSE_CLASS_CD]
,[OFFENSE_CLASS_DESC],[OFFENSE_TYP_CD],[OFFENSE_TYP_DESC],[DI_OFFENSE_CLASS_CD]
,[DI_OFFENSE_CLASS_DESC],[DI_OFFENSE_TYP_CD],[DI_OFFENSE_TYP_DESC],[VCC_CD]
,[VCC_CHARGEDESCRIPTION],[DISPOSITION_DT],[DISPOSITION_CD],[DISPOSITION_DESC]
,[DISPOSITION_BY_CD],[DISPOSITION_BY_DESC],[ID]
FROM [RPT_V_DISPJUDGEMENT]
WHERE
(ISNULL(CASE_NUMBER, 0) = ISNULL(@CASE_NUMBER, ISNULL(CASE_NUMBER, 0)))
AND
(ISNULL(PARTY_ID, 0) = ISNULL(@PARTY_ID, ISNULL(PARTY_ID, 0)))
AND
(ISNULL(LAST_NAME, 0) = ISNULL(@LAST_NAME, ISNULL(LAST_NAME, 0))
-- CASE TO CHECK IF NON REQUIRED FIELDS WHERE PASSED
AND
1=CASE
WHEN (@PARTY_ID IS NULL) THEN 1
ELSE 0
END
AND
1=CASE
WHEN (@LAST_NAME IS NULL) THEN 1
ELSE 0
END
GO

Any suggestions and inputs would help

Thanks

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 09/23/2007 :  17:32:55  Show Profile  Reply with Quote
Use case in where clause?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/24/2007 :  02:26:37  Show Profile  Reply with Quote
(ISNULL(LAST_NAME, 0) = ISNULL(@LAST_NAME, ISNULL(LAST_NAME, 0))

is missing a closing bracket

Kristen
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/24/2007 :  02:28:44  Show Profile  Reply with Quote
By the by, this is a DREADFUL piece of code on many levels, and thus you may be learning some bad habits!

Kristen
Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 09/24/2007 :  09:05:54  Show Profile  Reply with Quote
Kristen,

I understand that this is not effective way of doing conditional 'where clause', but is there any way out to implement
this. Pro Sql Server Reporting Services authors recommend doing this way in the below link.

http://www.yukonxml.com/chapters/apress/reportingservices/bestpractices/

Is there any better way to accomplish conditional where clause.

Thanks
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/24/2007 :  09:50:50  Show Profile  Reply with Quote
Well, being picky!", but this looks bad to me:

AND
1=CASE
WHEN (@PARTY_ID IS NULL) THEN 1
ELSE 0
END

why not just do

AND @PARTY_ID IS NULL

note that there is a strong chance that the Optimiser will not handle the CASE as efficiently.

and this:

(ISNULL(CASE_NUMBER, 0) = ISNULL(@CASE_NUMBER, ISNULL(CASE_NUMBER, 0)))

is bad because if CASE_NUMBER is indexed all that encapsulating with function calls will mean that the Optimiser can't use any indexes, and will scan the whole table instead, potentially with disastrous performance results.

For these sorts of "variable parameters" I reckon the best bet is:

(@CASE_NUMBER IS NULL OR CASE_NUMBER = @CASE_NUMBER)
AND ...

(Note that the IS NULL test comes first, as that is "cheaper" than actually checking the index for "CASE_NUMBER = NULL" ) this works if the user provides a parameter for @CASE_NUMBER only matching records are retrieved, and if the user provides NULL for this parameter then ALL records match [for this criteria].

Only downside I am aware of is that there is no way to explicitly match NULL values in this column; but that's never been an issue for my users, either they want a specific value, or "anything" (but never "Blanks only")

This is an OK means of wild-card matching, but the optimiser may still not do a brilliant job.

So if performance is still rubbish then there are two further routes that I use:

First is to pre-select PKs into a temporary table depending on what parameters are provided. So if you have, say, a Name Search function that lets you search on Name, ZipCode and Telephone Number you may decide that the telephone number is the "best" match - only likely to yield a few matches; ZipCode is next, and finally Name (because lets assume it will use a wild-card match, which is slow).

So you can do:

IF @Phone IS NOT NULL
BEGIN
... Get PKs matching @Phone into #TEMP
END
ELSE
IF @ZipCode IS NOT NULL
BEGIN
... Get PKs matching @ZipCode into #TEMP
END
ELSE
BEGIN
... Get PKs for records with Name LIKE '%' + @Name + '%' into #TEMP
END

... then ...

SELECT Col1, Col2, ...
FROM #TEMP AS T
JOIN MyAddressTable AS A
ON A.MyPK = T.MyPK
WHERE
(@Phone IS NULL OR MyPhone = @Phone)
AND (@ZipCode IS NULL OR MyZipCode = @ZipCode)
AND (@Name IS NULL OR Name LIKE '%' + @Name + '%')
ORDER BY ...

DIM strSQL ' Build syntax for SQL command

srtSQL = 'SELECT Col1, Col2, ... '
srtSQL = srtSQL & 'FROM MyAddressTable '
srtSQL = srtSQL & 'WHERE 1=1 '
IF FormFieldPhone <> "" Then srtSQL = srtSQL & 'AND MyPhone = @Phone '
IF FormZipCode <> "" Then srtSQL = srtSQL & 'AND MyZipCode = @ZipCode '
IF FormName <> "" Then srtSQL = srtSQL & 'AND Name LIKE '%' + @Name + '%'
srtSQL = srtSQL & 'ORDER BY ...'

' Prepare SQL Execution command:
srtSQL = "sp_ExecuteSQL N'" & srtSQL & "', " _
    & "'@Phone varchar(50), @ZipCode varchar(10), @Name varchar(50)', " _
    & "@ZipCode = '" & FormFieldPhone & "', "
    & "@Phone = '" & FormZipCode & "', "
    & "@Name = '" & FormName & "' "

then you need to execute strSQL with whatever connection method you use. strSQL will contain something like this:

sp_ExecuteSQL N'SELECT Col1, Col2, ... 
    FROM MyAddressTable 
    WHERE 1=1 
        AND MyZipCode = @ZipCode
    ORDER BY ...',
    '@Phone varchar(50), @ZipCode varchar(10), @Name varchar(50)',
    @ZipCode = '1234',
    @Phone = '',
    @Name = ''

Notice that only @ZipCode is used, representing the single form field parameter that the user has provided, and the VB has only included ZipCode in the WHERE clause, but there is no need to suppress parameters for the other fields that might have been potentially included.

The Query Optimiser will cache the query for ZipCode (Note that the actual ZipCode does NOT appear in the WHERE clause, it is provided as the parameter @ZipCode instead, and therefore ALL ZipCode queries will send this EXACT same syntax, and thus match the cached query plan, which will be reused.)

If someone else searches for Name, or Phone, that won't (yet) be in the cached query plan, so a new query plan will be built , and cached. Thus popular variations of this query will all be cached, and the unpopular ones - Tough! - the user will have to wait a bit longer to get their results.

BUT ... because the WHERE clause is now so "slim" there is a much better chance that Indexes get used and so on, so even for "unpopular" queries the results are still likely to be way faster than the "wildcard SProc" discussed earlier.

Another couple of things:

CREATE PROCEDURE SP_RPT

don't start your Sproc names with "sp_", SQL server treats these names a special way, and the result is inefficient.

FROM [RPT_V_DISPJUDGEMENT]

Always include the Owner Name, to give the maximum chance that your query will be cached. So you should use:

FROM dbo.[RPT_V_DISPJUDGEMENT]

otherwise there is a risk that the system will first search for a table, and cached query plan, for [YourLoginName].[RPT_V_DISPJUDGEMENT], which it won't find, and then repeat the process to look for "dbo.xxx"

Kristen
Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 09/24/2007 :  14:58:30  Show Profile  Reply with Quote
well documented, thank you that helps

Go to Top of Page

johnsql
Posting Yak Master

USA
161 Posts

Posted - 10/01/2007 :  15:21:59  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

Well, being picky!", but this looks bad to me:

AND
1=CASE
WHEN (@PARTY_ID IS NULL) THEN 1
ELSE 0
END

why not just do

AND @PARTY_ID IS NULL

note that there is a strong chance that the Optimiser will not handle the CASE as efficiently.

and this:

(ISNULL(CASE_NUMBER, 0) = ISNULL(@CASE_NUMBER, ISNULL(CASE_NUMBER, 0)))

is bad because if CASE_NUMBER is indexed all that encapsulating with function calls will mean that the Optimiser can't use any indexes, and will scan the whole table instead, potentially with disastrous performance results.

For these sorts of "variable parameters" I reckon the best bet is:

(@CASE_NUMBER IS NULL OR CASE_NUMBER = @CASE_NUMBER)
AND ...

(Note that the IS NULL test comes first, as that is "cheaper" than actually checking the index for "CASE_NUMBER = NULL" ) this works if the user provides a parameter for @CASE_NUMBER only matching records are retrieved, and if the user provides NULL for this parameter then ALL records match [for this criteria].

Only downside I am aware of is that there is no way to explicitly match NULL values in this column; but that's never been an issue for my users, either they want a specific value, or "anything" (but never "Blanks only")

This is an OK means of wild-card matching, but the optimiser may still not do a brilliant job.

So if performance is still rubbish then there are two further routes that I use:

First is to pre-select PKs into a temporary table depending on what parameters are provided. So if you have, say, a Name Search function that lets you search on Name, ZipCode and Telephone Number you may decide that the telephone number is the "best" match - only likely to yield a few matches; ZipCode is next, and finally Name (because lets assume it will use a wild-card match, which is slow).

So you can do:

IF @Phone IS NOT NULL
BEGIN
... Get PKs matching @Phone into #TEMP
END
ELSE
IF @ZipCode IS NOT NULL
BEGIN
... Get PKs matching @ZipCode into #TEMP
END
ELSE
BEGIN
... Get PKs for records with Name LIKE '%' + @Name + '%' into #TEMP
END

... then ...

SELECT Col1, Col2, ...
FROM #TEMP AS T
JOIN MyAddressTable AS A
ON A.MyPK = T.MyPK
WHERE
(@Phone IS NULL OR MyPhone = @Phone)
AND (@ZipCode IS NULL OR MyZipCode = @ZipCode)
AND (@Name IS NULL OR Name LIKE '%' + @Name + '%')
ORDER BY ...

DIM strSQL ' Build syntax for SQL command

srtSQL = 'SELECT Col1, Col2, ... '
srtSQL = srtSQL & 'FROM MyAddressTable '
srtSQL = srtSQL & 'WHERE 1=1 '
IF FormFieldPhone <> "" Then srtSQL = srtSQL & 'AND MyPhone = @Phone '
IF FormZipCode <> "" Then srtSQL = srtSQL & 'AND MyZipCode = @ZipCode '
IF FormName <> "" Then srtSQL = srtSQL & 'AND Name LIKE '%' + @Name + '%'
srtSQL = srtSQL & 'ORDER BY ...'

' Prepare SQL Execution command:
srtSQL = "sp_ExecuteSQL N'" & srtSQL & "', " _
    & "'@Phone varchar(50), @ZipCode varchar(10), @Name varchar(50)', " _
    & "@ZipCode = '" & FormFieldPhone & "', "
    & "@Phone = '" & FormZipCode & "', "
    & "@Name = '" & FormName & "' "

then you need to execute strSQL with whatever connection method you use. strSQL will contain something like this:

sp_ExecuteSQL N'SELECT Col1, Col2, ... 
    FROM MyAddressTable 
    WHERE 1=1 
        AND MyZipCode = @ZipCode
    ORDER BY ...',
    '@Phone varchar(50), @ZipCode varchar(10), @Name varchar(50)',
    @ZipCode = '1234',
    @Phone = '',
    @Name = ''

Notice that only @ZipCode is used, representing the single form field parameter that the user has provided, and the VB has only included ZipCode in the WHERE clause, but there is no need to suppress parameters for the other fields that might have been potentially included.

The Query Optimiser will cache the query for ZipCode (Note that the actual ZipCode does NOT appear in the WHERE clause, it is provided as the parameter @ZipCode instead, and therefore ALL ZipCode queries will send this EXACT same syntax, and thus match the cached query plan, which will be reused.)

If someone else searches for Name, or Phone, that won't (yet) be in the cached query plan, so a new query plan will be built , and cached. Thus popular variations of this query will all be cached, and the unpopular ones - Tough! - the user will have to wait a bit longer to get their results.

BUT ... because the WHERE clause is now so "slim" there is a much better chance that Indexes get used and so on, so even for "unpopular" queries the results are still likely to be way faster than the "wildcard SProc" discussed earlier.

Another couple of things:

CREATE PROCEDURE SP_RPT

don't start your Sproc names with "sp_", SQL server treats these names a special way, and the result is inefficient.

FROM [RPT_V_DISPJUDGEMENT]

Always include the Owner Name, to give the maximum chance that your query will be cached. So you should use:

FROM dbo.[RPT_V_DISPJUDGEMENT]

otherwise there is a risk that the system will first search for a table, and cached query plan, for [YourLoginName].[RPT_V_DISPJUDGEMENT], which it won't find, and then repeat the process to look for "dbo.xxx"

Kristen



This is an excellent article. There is a lot of things to learn from it.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/01/2007 :  15:25:46  Show Profile  Reply with Quote
One of my first jobs was working for a company that had lousy operations and work-flow. Great fun to work for, but I reckon I learnt FAR more than I would have done working for an company with a really slick work-flow - I would never had been able to see WHY they were so slick
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000