asp page calling sql server 2000

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

Development Tools

Other Development Tools

asp page calling sql server 2000

Author

Topic

dave mack
Starting Member

3 Posts

Posted - 2005-02-23 : 11:15:51

The following sproc works fine from Query Analyzer and an ADP. The idea is to have the asp page call this sproc with the first three params required and the forth param "@Assigned_Unit" being optionally selected from a dropdown of units on the asp side (note they are all numbers)

_________________________________________
This is the call from the asp page:

set rsCountsByUnit = conn.execute("EXEC rep_IA_CountsByUnit '" & company & "','" & startdate & "','" & enddate & "','" & unit & "'")

I have tried to pass a NULL value into <unit> with no luck. I have also tried to make a <ALL UNITS> selection on the asp units dropdown with a value of 1000.

The 1000 will transfer to the sproc but I do not know how to handle it.

1) Is there is a way to pass a null value from the asp to the sproc?
2) Is there a way to test the @Assigned_Unit param in the sproc for a value of 1000 and then convert it to NULL?
___________________________________
The sproc is:

ALTER PROCEDURE dbo.rep_IA_CountsByUnit
(@company char(1),
@startdate smalldatetime,
@enddate smalldatetime,
@Assigned_Unit int=null)

AS SELECT dbo.UNITS.UNIT_NAME, COUNT(dbo.ASSIGNMENTS.ASSIGNED_UNIT) AS [COUNT]
FROM dbo.ASSIGNMENTS INNER JOIN
dbo.UNITS ON dbo.ASSIGNMENTS.ASSIGNED_UNIT = dbo.UNITS.PRIMARY_UNIT_ID
WHERE (dbo.UNITS.COMPANY = @company) AND (dbo.ASSIGNMENTS.DATE_REQUESTED BETWEEN @startdate AND @enddate)

GROUP BY dbo.UNITS.UNIT_NAME, dbo.ASSIGNMENTS.Assigned_Unit
HAVING (@Assigned_Unit IS NULL) OR
(@Assigned_Unit = [Assigned_Unit])

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-24 : 02:00:24

1) yes - just omit it from the call is easiest.
set rsCountsByUnit = conn.execute("EXEC rep_IA_CountsByUnit '" & company & "','" & startdate & "','" & enddate & "'")

or pass null
set rsCountsByUnit = conn.execute("EXEC rep_IA_CountsByUnit '" & company & "','" & startdate & "','" & enddate & "',null")

2) yes
if @Assigned_Unit = 1000 select @Assigned_Unit = null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources