Store procedure parameter - SQL Server Forums

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

SQL Server Development (2000)

Store procedure parameter

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2007-04-05 : 15:23:59

i am using a store procedure to query a data base passing a parameter from an asp page form field but it is not displaying the correct data.

STORE PROCEDURE:

CREATE PROCEDURE dbo.sp_get_grants_category @category INT=null AS

BEGIN
IF (@category IS NULL)
SELECT * FROM tblGrantCategory, vwGrants WHERE tblGrantCategory.categoryNum = vwGrants.categoryNum ORDER BY vwGrants.Status, vwGrants.GrantAmount

ELSE
SELECT * FROM tblGrantCategory INNER JOIN
vwGrants ON tblGrantCategory.categoryNum = vwGrants.categoryNum WHERE vwGrants.categoryNum = @category ORDER BY vwGrants.Status, vwGrants.GrantAmount
END
GO

I am passing the value @category from the asp page like this:

Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = Conn
oCmd.CommandType = 4
oCmd.CommandText = "sp_get_grants_category"
oCmd.Parameters.Refresh
oCmd.Parameters(1) = Request.QueryString("DeptCategory")

NOTE: ONLY THE (IF) STATEMENT IS EXECUTED EVEN IF A VALUE IS PASSED.
IF I HARD CODE THE VALUE IN THE STORE PROCEDURE AS
@category INT=20 THE (ELSE) STATEMENT IS EXECUTED.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-05 : 16:38:01

You think you are passing in a parameter value, but you are not. Somehow, you are passing in NULL instead. But the problem would be in your asp code, not in the procedure.
Try adding RETURN ISNULL(@category, 99999) to the end of your procedure, and see what codes come out.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources