May 27, 2004 at 9:23 am
I hope this is an easy one. I am looking for a way to set variables to null in a Stored procedure for a search engine in a web page design.
Basically a multiple search stored procedure that would use some or all of the variables
Would I declare the variable as null if so could someone let me know how
Here is what I need. (let's use Northwind for instance)
I would like to see which employee lived in this region or that region but only have to put in one variable in the stored procedure so If one was blank(no value specified) then continue with the store procedure and continue.
Hopefully this makes since.
Please help and thank you in advance.
May 27, 2004 at 10:09 am
CREATE PROCEDURE sp_Test
@Var1 VARCHAR(100)
@Var2 DATETIME = NULL
AS
will work for what you want. Just make sure that you build in code traps to handle the NULL casing
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 27, 2004 at 10:30 am
See http://www.sqlservercentral.com/scripts/contributions/632.asp for a sample of the function fnStringCompare . This function helps simplifie the code. Now if you want to pass more than region, that is pass lastname, city etc just add the same kind of logic as region and use the fnStringCompare function to do the compare either = or IS NULL. The cool thing about this function is is works with whatever kind of compare you what (>, <, =, <> etc)
then try
if exists (select * from dbo.sysobjects
where id = object_id(N'dbo.Employees_SelectAllWLogic')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.Employees_SelectAllWLogic
GO
-------------------------------------------------------------------------
-- Stored procedure that will select one or more existing rows
-- from the table 'Employees'
-- based on a foreign key field.
-- Gets: @iRegion int
-- Returns: @iErrorCode int
-------------------------------------------------------------------------
CREATE PROCEDURE dbo.Employees_SelectAllWLogic
@cRegion varchar(15),
@iErrorCode int OUTPUT
AS
SET NOCOUNT ON
DECLARE @opRegion varchar(7)
IF @cREGION IS NULL
SET @opRegion = 'IS NULL'
ELSE
SET @opRegion = '='
-- SELECT one or more existing rows from the table.
SELECT
EmployeeID,
LastName,
FirstName,
Title,
TitleOfCourtesy,
BirthDate,
HireDate,
Address,
City,
Region,
PostalCode,
Country,
HomePhone,
Extension,
Photo,
Notes,
ReportsTo,
PhotoPath
FROM dbo.Employees
WHERE
dbo.fnStringCompare(Region, @opRegion, @cRegion) = 1
-- Get the Error Code for the statement just executed.
SELECT @iErrorCode=@@ERROR
GO
Francis
May 30, 2004 at 9:26 am
You could use the following approach in your WHERE clause:
WHERE (EmployeeID = @EmployeeID OR @EmployeeID IS NULL) AND ...
HTH,
Igor
May 31, 2004 at 8:20 am
If @EmployeeID is set to NULL this would return all rows in table. I'm not sure this is what is wanted ... or is it? I thought if @EmployeeID (or @CRegionid or whatever) were set to NULL the statement would retuns only rows with a NULL in that column.
Another alternative is to use the statement
SET ANSI_NULLS OFF
Then
SELECT ...
FROM dbo.Employees
WHERE (Region = @cRegion )
will compare NULL to NULL and as if they are equal.
Francis
June 1, 2004 at 1:56 pm
In some cases this is where using an XML parameter works really well. You can simply have the calling application build the XML string with all the parameters you want to search for and use the OPENXML in a join on the field in question. If the XML Param is null switch to a different query and return all rows.
CREATE PROCEDURE Foo
(
@xmldoc TEXT
)
AS
IF @XML IS NULL
BEGIN
SELECT * FROM MyTable...
RETURN
END
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc
SELECT *
FROM MyTable
JOIN OPENXML (@idoc, ...)
WITH (...) ON f1 = f2
RETURN
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply