Parameter Masking in Dynamic SQL Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parameter Masking in Dynamic SQL Stored Procedure

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-09 : 01:55:20

I have created the following stored procedure to count items in a table based on certain types of queries by passing it parameters.

CREATE PROCEDURE [dbo].[UF2_Thumbnail_Count]
@par1 int = NULL,
@par2 int = NULL,
@par3 int = NULL,
@par4 int = NULL,
@par5 int = NULL,
@par6 int = NULL,
@par7 int = NULL WITH RECOMPILE
AS

SELECT Count (*) AS ImageCount
FROM UF_rt_Images
WHERE ((Image_Active = 1)
AND (Image_Color = @par1 OR @par1 IS NULL)
AND ((dt_Category1_ID = @par2 OR @par2 IS NULL)
OR (dt_Category2_ID = @par2 OR @par2 IS NULL)
OR (dt_Category3_ID = @par2 OR @par2 IS NULL))
AND (Project_ID = @par3 OR @par3 IS NULL)
AND (Image_Adult = @par4 OR @par4 IS NULL)
AND (Staff_ChoiceBit = @par5 OR @par5 IS NULL)
AND (Editors_Choice = @par6 OR @par6 IS NULL)
AND (Image_Allowed_Top_Rated = @par7 OR @par7 IS NULL))
GO

It is about 90% there. The problem that I am having is that if there is nothing in the particular parameter being passed to the SP, it still has an effect on filtering the data. Is there a way to get it to just ignore the data if it gets a NULL? In reading that is what I thought this configuration would do, but apparently not. Am I doing something wrong?

As an example the first parameter is either 0, 1 or 2, I would like to just have it ignore this column returning all records, or count the records only if it is a 1 or a 0 etc... same with the other columns.

Any help would be appreciated.
Phil

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-09 : 09:04:36

I don't see why NULLs would affect the results. Your stategy is sound.

quote:
if there is nothing in the particular parameter being passed to the SP

Are you sure your application isn't passing zeros (or empty strings) instead of NULL? Try execing the proc in Query Analyzer with NULLs and see if your results are still incorrect. If it works as expected in QA, you can use Sql Profiler to capture exactly what your application is passing to Sql Server.

BTW, why are you using "with recompile"?

Be One with the Optimizer
TG

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-09 : 17:09:54

Thanks for the encouraging words, I thought it should work too, but for some reason it doesn't. Oh well will keep plugging away at it.

As for the recompile I was not sure if it would use the old cached proc or create a new one, was more of an act of desperation to see if that was it.

I do a response.write of the string sent to the proc from the asp page calling it and it looks to be ok, i see 2,,,,,,30,1 for example the blank areas being the nulls if no data is present. I assume this is correct, do I need to put some other delimiters in there such as a ' around each param? This has been a 24 pack of Coca Cola stored procedure so far and not sure I can take much more ;=}

Thanks for your help,
Phil

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-09 : 17:26:11

Passing:
EXEC UF2_Thumbnail_Count 2,'','','','','',30,1
Is not the same as passing:
EXEC UF2_Thumbnail_Count 2,NULL,NULL,NULL,NULL,NULL,30,1

Can You execute the sprocs from Query Analyzer ?
You will see the difference there.

rockmoose

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-09 : 17:44:23

Well actually it not passing the 2,'','','','','',30,1 it is passing 2,,,,,30,1 I have the asp page passing a NULL for each item if it is not used by setting the variable to a NULL like this par1 = NULL

It doesnt show up as anything that is visible. Is this the correct way to set a variable to a NULL? I will give it a go in the Query Analyzer and see what it is doing.

THanks,

Phil

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-09 : 18:02:44

You need to pass the query exactly like this:
EXEC UF2_Thumbnail_Count 2,NULL,NULL,NULL,NULL,NULL,30,1

Or with named parameters like this:
EXEC UF2_Thumbnail_Count @par1=2, @par6=30, @par7=1

Try it in QA,
Also like TG suggested Profiler is an excellent tool to monitor queries sent to SQL Server.
If You haven't tried it yet, I recommend You do so.

rockmoose

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-09 : 18:50:35

The datatype of the variable @par is int, will this allow me to send the word NULL? If I change it to a varchar type, to pass the word NULL will it still be able to do the AND function in the WHERE query as the field it is checking is an int field also?

Does the @par1 = NULL declaration in the beginning of the proc set it to NULL if there is nothing there?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-09 : 19:02:11

You can pass NULL to any datatype. (Its not a string "NULL")
Yes, "@par1 int = null" means NULL is the default if nothing is passed. Without the default value, an error is raised if you don't pass anything for that parameter.

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-09 : 19:10:57

Passing 'NULL' means the string "NULL".
Passing NULL means passing the value NULL, this has special meaning in SQL.
(somewhat like when an object is not instantiated it's pointer is a null pointer)

It's hard to describe nothing

, nothing is nothing!

rockmoose

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-10 : 00:35:05

Well I got it to work. Apparently while NULL is a NULL it isn't a NULL when it is nothing even though it is told to be a NULL so I made a Psuedo NULL and it works great. (Sound like Rumsfeld in the sentence dont I, Very Scarey!)

This is what I added.

CREATE PROCEDURE [dbo].[UF2_Thumbnail_Count]
@par1 int = NULL,
@par2 int = NULL,
@par3 int = NULL,
@par4 int = NULL,
@par5 int = NULL,
@par6 int = NULL,
@par7 int = NULL

AS
declare @para1 int
declare @para2 int
declare @para3 int
declare @para4 int
declare @para5 int
declare @para6 int
declare @para7 int

IF (@par1 = 9999)
SELECT @para1 = NULL
ELSE
SELECT @para1= @par1

IF (@par2 = 9999)
SELECT @para2 = NULL
ELSE
SELECT @para2= @par2

IF (@par3 = 9999)
SELECT @para3 = NULL
ELSE
SELECT @para3 = @par3

IF (@par4 = 9999)
SELECT @para4 = NULL
ELSE
SELECT @para4 = @par4

IF (@par5 = 9999)
SELECT @para5 = NULL
ELSE
SELECT @para5 = @par5

IF (@par6 = 9999)
SELECT @para6 = NULL
ELSE
SELECT @para6 = @par6

IF (@par7 = 9999)
SELECT @para7 = NULL
ELSE
SELECT @para7 = @par7

SELECT Count (*) AS ImageCount
FROM UF_rt_Images
WHERE ((Image_Active = 1)
AND (Image_Color = @para1 OR @para1 IS NULL)
AND ((dt_Category1_ID = @para2 OR @para2 IS NULL)
OR (dt_Category2_ID = @para2 OR @para2 IS NULL)
OR (dt_Category3_ID = @para2 OR @para2 IS NULL))
AND (Project_ID = @para3 OR @para3 IS NULL)
AND (Image_Adult = @para4 OR @para4 IS NULL)
AND (Staff_ChoiceBit = @para5 OR @para5 IS NULL)
AND (Editors_Choice = @para6 OR @para6 IS NULL)
AND (Image_Allowed_Top_Rated = @para7 OR @para7 IS NULL))
GO

Basically I send 9999 instead of a NULL and then force it to be a NULL on ths SQL server side where it recognizes a NULL for what it is. Seems to work great, very fast return with large tables.

Phil

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-10 : 15:49:30

Ok, You got it to work, good.

But really, if You managed to get the parameter passing correct from the calling code,
then You would not have to do this extra work.
(Did You manage to try out the Profiler tool?)

rockmoose

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2005-04-10 : 16:46:52

I have not used the Profiler yet, however as far as the command string to the SP goes, I do a trim on each parameter before it is sent to the SP to get rid of any invisible spaces that may have creeped in.

I did use the query analyzer to determine the problem though, I ran this query first to see if my counts were correct based on no input to the stored procedure just relying on the default NULL for the input parameters.

@par1 int = NULL,
@par2 int = NULL,
@par3 int = NULL,
@par4 int = NULL,
@par5 int = NULL,
@par6 int = NULL,
@par7 int = NULL

SELECT Count (*) AS ImageCount
FROM UF_rt_Images
WHERE ((Image_Active = 1)
AND (Image_Color = @par1 OR @par1 IS NULL)
AND ((dt_Category1_ID = @par2 OR @par2 IS NULL)
OR (dt_Category2_ID = @par2 OR @par2 IS NULL)
OR (dt_Category3_ID = @par2 OR @par2 IS NULL))
AND (Project_ID = @par3 OR @par3 IS NULL)
AND (Image_Adult = @par4 OR @par4 IS NULL)
AND (Staff_ChoiceBit = @par5 OR @par5 IS NULL)
AND (Editors_Choice = @par6 OR @par6 IS NULL)
AND (Image_Allowed_Top_Rated = @par7 OR @par7 IS NULL))

Which gave me incorrect results as I was seeing with the stored procedure. It was not until I did an explicit DECLARE @parN and SELECT @parN = NULL that it started to work. This tells me that the initial parmeter declaration @parN int = NULL didn't really set it to NULL. As this was done in the Query Analyzer, it was not effected by the parameters being passed to it. Go Figure!

Would be nice to figure this out though so I wouldnt have to fake it.

On another note, I am now adding a Dynamic ORDER BY to a similar SP using the CASE method and it seems to be having problems of its own, queries that run at .07 seconds take upward of 20 seconds even when selecting the same ORDER BY parameters between hard coded and dynamic. That is todays problem, time to get another case of Coca Cola.

Phil

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-10 : 17:04:12

Confusing behavior, but I am sure there is a logical explanation somewhere...

CREATE PROCEDURE spTestNull
	@par1 INT = NULL
AS
SET NOCOUNT ON

SELECT
	'HIT' AS result
WHERE
	(@par1 = 1 OR @par1 IS NULL)
GO


EXEC spTestNull @par1 = 23	-- No Rows because no match
EXEC spTestNull @par1 = ''	-- No rows because empty string is not the same as NULL and is implicitly converted to 0
EXEC spTestNull @par1 = NULL	-- Match, because we want match when NULL
EXEC spTestNull @par1 = default	-- Match, uses the default value declared in the procedure (NULL)
EXEC spTestNull			-- Match, equivalent to previous example
EXEC spTestNull @par1 = 1	-- Match
GO

DROP PROCEDURE spTestNull
GO

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources