stored procedure not returning recordset

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 2005 Forums

Transact-SQL (2005)

stored procedure not returning recordset

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 10:48:15

The stored procedure I created returns all records from the table if no parameters are passed, as expected. When I pass in the only parameter, I get 0 records returned when there should be one or more returned. I'm sure it's something simple in my syntax, but I don't see it.

This call returns all records:
exec webservices_BENEFICIAL_USES_DM_SELECT

This call returns 0 records, when it should return 1:
exec webservices_BENEFICIAL_USES_DM_SELECT @DISPOSAL_AREA_NAME='Cell 8'

Here is the stored procedure:
ALTER PROCEDURE [dbo].[webservices_BENEFICIAL_USES_DM_SELECT]
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME DISPOSAL_AREA_NAME_TYPE = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
IF @DISPOSAL_AREA_NAME IS NULL
BEGIN
SELECT *
FROM BENEFICIAL_USES_DM
END
ELSE
BEGIN
SELECT *
FROM BENEFICIAL_USES_DM
WHERE DISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME'
END

END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-12 : 11:15:06

Remove the single quotes from
WHERE DISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME'

should be
WHERE DISPOSAL_AREA_NAME = @DISPOSAL_AREA_NAME

Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 11:15:49

WHERE DISPOSAL_AREA_NAME = '@DISPOSAL_AREA_NAME'

should be

WHERE DISPOSAL_AREA_NAME = @DISPOSAL_AREA_NAME

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 11:16:33

Madhivanan

Failing to plan is Planning to fail

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-06-12 : 11:20:31

Thank you for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources