paramater in a stored procedure

[June Macleod]

I am using MS Access 2003 with MSDE 2000.

I have a stored procedure which creates a temporary table based on two
paramaters passed to it. The first paramater will always be present but the
second might not be.

I want to return records based on the contents of a field named UOMID.
(smallint).

This field will either have a value of NULL, 1, 2 or 3

If a value of 1 2 or 3 is passed I extract all the NULL value records
together with those records whoes UOMID matches the input value.

The code below works fine provided a value of 1,2 or 3 is passed. However,
if no value is passed then no records at all are returned whereas I would
like ALL records returned.

Any help in setting up this procedure would be much appreciated.


ALTER PROCEDURE qryforDetailedReport_S2

(

@parameter1 smallint =1,

@parameter2 NVARCHAR(5)=null )

AS

/* SET NOCOUNT ON */

declare @errnum int

if @parameter2 is null

select @parameter2= '1,2,3'



INSERT INTO
##tmpQuestions(quniqueid,questionid,uomid,catid,gquestionorder,questiontext,
questiontype,pointsvalue,catname,groupnum)

SELECT dbo.QQuestions.qUniqueID, dbo.QQuestions.questionID,
dbo.QQuestions.uomID, dbo.QQuestions.catID, dbo.QQuestions.gquestionOrder,

dbo.QQuestions.questionText, dbo.QQuestions.questionType,
dbo.QQuestions.pointsValue,dbo.##tmpCategoriesGroups.catname,dbo.QQuestions.
groupid

FROM dbo.QQuestions inner JOIN

dbo.##tmpCategoriesGroups ON dbo.QQuestions.catID =
dbo.##tmpCategoriesGroups.catID

WHERE (dbo.QQuestions.inUse = 1) AND (dbo.QQuestions.langID = @parameter1)
AND (dbo.QQuestions.uomid IN( @parameter2)) OR

(dbo.QQuestions.inUse = 1) AND (dbo.QQuestions.langID = @parameter1) AND
(dbo.QQuestions.uomid IS NULL)

ORDER BY dbo.##tmpCategoriesGroups.catname,dbo.QQuestions.questionID,
dbo.QQuestions.gquestionOrder

RETURN

 

[Brendan Reynolds]

This question might be better asked in a SQL Server forum. For whatever it
might be worth, though, I expect that you probably can't pass multiple
values in one parameter like that. I think I'd probably approach it by
defining alternative SELECT statements instead, something like ...

ALTER PROCEDURE dbo.spTest
(@Test int = null)
AS
IF @Test IS NULL
SELECT dbo.tblTest.*
FROM dbo.tblTest
ELSE
SELECT dbo.tblTest.* FROM dbo.tblTest
WHERE (TestNum = @Test)