November 13, 2007 at 2:36 pm
Hello all,
I have a stored procedure that begins:
Create Procedure AllocationAssistant_SetAllocations
(
@RoadShowID int,
@RoadShowEventID int = NULL,
@SellerID int = NULL,
@EmployerID int = NULL,
@JobsAllocated int = NULL,
@SellerProjection int = NULL,
@SellerEnrolled int = NULL,
@ChangedByUserID int = NULL,
@ChangedBySellerID int = NULL,
@ChangeComments nvarchar(255) = NULL
)
AS
SET NOCOUNT ON
SET ANSI_NULLS OFF
--First check if this row already exists
IF NOT EXISTS
(
SELECT 1 FROM dbo.tblRoadShowAllocation
WHERE
RoadShowID = @RoadShowID AND
RoadShowEventID = @RoadShowEventID AND
SellerID = @SellerID AND
EmployerID = @EmployerID
)
--This is a new record insert
BEGIN ...
The problem is that @RoadShowEventID and @SellerID can be null. If ANSI_NULLS are OFF the test works when either @RoadShowEventID or @SellerID are NULL.
But in the context of the stored procedure I cant't seem to SET ANSI_NULLS OFF. SQL Serve insists on turning them on at the top of the procedure and I can't put GO
after my SET ANSI_NULLS OFF because it is in the procedure!
Is there a way to rewrite the SELECT so that ANSI_NULLS don't matter. Maybe using ISNULL?
Thanks!
Jonathan
November 13, 2007 at 3:04 pm
You're supposed to set that before the stored proc. The procedure will emulate whatever the ANSI_NULLS setting was at the time when it was created.
So, -
SET ANSI_NULLS ON
GO
create procedure
blah
...
go
SET ANSI_NULLS OFF
go
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 14, 2007 at 9:21 am
Thanks! I was confused about that. Works now.
One thing though; if a user ALTERs the procedure with Managment Studio the server starts the script with SET ANSI_NULLS ON. (I think this might be cotrolled with a setting when the SQL Server was installed). If the script is then executed would the procedure then assume ANSI_NULLS are on or would the original CREATE script still win?
November 14, 2007 at 9:25 am
Greetings Jonathan,
The crux of the problem is comparing NULL using logical operators. NULL does not compare!
I think something on the order of the following modification will remedy your issue:
If you want the SELECT to 'fail' when either of them is NULL then:
WHERE ...
(
(RoadShowEventID = @RoadShowEventID AND @RoadShowEventID IS NOT NULL)
AND -- Change this to OR to succeed if either/both are NULL
(SellerID = @SellerID AND @SellerID IS NOT NULL)
) ...
As a suggestion, I would change the IF NOT EXISTS(SELECT 1...) to IF EXISTS(SELECT *) and reverse the new/existing row logic.
HTH,
Art
November 14, 2007 at 9:41 am
I agree about NULL not comparing and am not thrilled with I am doing here. The data in this table is really odd, however. In some cases RoadShowEventID and/or SellerID can be NULL in the table and still play a role in uniquely identifying the row. (Codd would be horrified). So I need to compare the values for the input parameters to the values in the table even if they are NULL.
November 14, 2007 at 9:50 am
Hi Jonathan
Yes, you could use ISNULL to swap null values for "unrealistic" integer values for your ID's, so that the test would fail. However, if any of your ID parameters evaluate to null, does it make sense to go so far as testing for existence of the row?
IF @RoadShowID IS NULL
OR @RoadShowEventID IS NULL
OR @SellerID IS NULL
OR @EmployerID IS NULL
-- need all 4 integer params, so fail here
RETURN 0
Cheers
ChrisM
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
November 14, 2007 at 9:53 am
Use the OR version...
You can leave ANSI NULLs ON and it will still work.
Test it to be sure, dink with the AND/OR but the "column = @var AND @var IS NOT NULL" code will allow the use of indexes in the query plan and solve the one or more NULL parameters issue.
For a semi-exhaustive treatise...
November 14, 2007 at 10:17 am
Sanity check here ...
The relevant code in my proc looks like this
--First check if this row already exists
IF EXISTS
(
SELECT 1 FROM dbo.tblRoadShowAllocation
WHERE
RoadShowID = @RoadShowID AND
RoadShowEventID = @EventID AND
SellerID = @SellerID AND
EmployerID = @EmployerID
)
BEGIN
--This is an update of an existing record
(I took the advice and changed to IF EXISTS and swithced IF ELSE code).
RoadShowEventID and SellerID can be NULL in the table and still play a role in uniquely identifing the row. So when @RoadShowEventID and @SellerID are NULL I need to compare to the values in the table.
How can the OR ISNull accomplish this?
Thanks for you help!
November 14, 2007 at 10:21 am
Are you saying the column must match the parameter -OR- both the column and the parameter are null?
Wouldn't that be...
WHERE (RoadShowEventID = @RoadShowEventID OR (RoadShowEventID IS NULL AND @RoadShowEventID IS NULL)
AND ...
November 14, 2007 at 10:38 am
I mean the data can look like this
RoadShowID RoadShowEventID SellerID EmployerID
35 NULL NULL 567
35 345 NULL 567
35 NULL 456 567
35 345 456 567
I need to find a row based on the input parameters for these four columns.
Thanks again.
November 14, 2007 at 10:49 am
If I'm understanding this correctly, I believe that would be...
WHERE RoadShowID = @RoadShowID
AND (RoadShowEventID = @RoadShowEventID OR (RoadShowEventID IS NULL AND @RoadShowEventID IS NULL))
AND (SellerID = @SellerID OR (SellerID IS NULL AND @SellerID IS NULL))
AND EmployerID = @EmployerID
if RoadShowID and EmployerID can be null too, then
WHERE (RoadShowID = @RoadShowID OR (RoadShowID IS NULL AND @RoadShowID IS NULL))
AND (RoadShowEventID = @RoadShowEventID OR (RoadShowEventID IS NULL AND @RoadShowEventID IS NULL))
AND (SellerID = @SellerID OR (SellerID IS NULL AND @SellerID IS NULL))
AND (EmployerID = @EmployerID OR (EmployerID IS NULL AND @EmployerID IS NULL))
November 14, 2007 at 11:17 am
Thanks! That does what I need to do without worrying about ANSI_NULLS.
Thanks for the help everyone.
November 14, 2007 at 2:56 pm
From SQL Server Books Online:
"For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed."
This means that it uses the ANSI_NULLS setting that was in effect at the time the procedure was created, and the SET ANSI_NULLS statement does nothing inside a stored procedure. If you want the stored procedure to have ANSI_NULLS on, do this in your code:
SET ANSI_NULLS ON
GO
CREATE PROCEDURE ...rest of procedure code...
November 15, 2007 at 6:00 am
SELECT 1 FROM dbo.tblRoadShowAllocation
WHERE
RoadShowID = @RoadShowID AND
RoadShowEventID = @RoadShowEventID AND
SellerID = @SellerID AND
EmployerID = @EmployerID
)
The problem is that @RoadShowEventID and @SellerID can be null. If ANSI_NULLS are OFF the test works when either @RoadShowEventID or @SellerID are NULL.
I guess the intention is that if the inputs are null then all values for that input will be allowed - I find the neatest (though possibly not optimal) SQL for this sort of thing is to do it like:
SELECT 1 FROM dbo.tblRoadShowAllocation
WHERE
RoadShowID = IsNull(@RoadShowID,) AND
RoadShowEventID = IsNull(@RoadShowEventID,RoadShowEventID) AND
SellerID = IsNull(@SellerID,SellerID ) AND
EmployerID = IsNull(@EmployerID,EmployerID)
Of course this assume that in the ID values in the DB itself there are no Nulls - only that the sproc parm may have Nulls and if parm is Null we want it to consider all rows for that particular parm
James Horsley
Workflow Consulting Limited
November 15, 2007 at 6:33 am
You could try this:
SELECT 1 FROM dbo.tblRoadShowAllocation
WHERE
Coalesce(RoadShowID,-1) = Coalesce(@RoadShowID,-1) AND
Coalesce(RoadShowEventID,-1) = Coalesce(@EventID,-1) AND
Coalesce(SellerID,-1) = Coalesce(@SellerID,-1) AND
Coalesce(EmployerID,-1) = Coalesce(@EmployerID,-1)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply