Stored Procedure in Dreamweaver Won't Work!!

[TheStriker]

I just created a stored procedure with a dynamic WHERE clause using the COALESCE function. This works great in SQL Server 2000 however when I call the same stored procedure from ASP via Dreamweaver to populate the parameters (e.g. Request.Form("variable")) etc., the dataset does not appear. I was wondering if anybody knows whether the COALESCE function is causing a problem in Dreamweaver or why does it work on SQL Server and not in ASP/Dreamweaver. It seems the values are not being carried over to Dreamweaver for some reason. Has anybody else had this problem?

Thanks in advance

 

[lebisol]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_9dph.asp

will give u some refs. so u can check the SQL

in DW u have to type some variable manually to test it or there will be no set returned.....
on the Page itself u can use request.form assuming that u have something in the form but in DW interface default value is not entered.
HTH if not then post some code....
All the best!


:--------------------------------------------------------------------------:

ok,ok...I did shoot the deputy but he told me he was the sheriff!
:--------------------------------------------------------------------------:

 

[TheStriker]

Yes, that would be helpful wouldn't it? LOL

Here is the faulty code that the stored procedure contains. It works in SQL Server but when I call it in Dreamweaver, nothing happens.

ALTER PROCEDURE dbo.CustomBDC
(@SiteCode nvarchar(255),
@OfficeType nvarchar(255),
@Region nvarchar(255),
@ServerModel nvarchar(50),
@Domain nvarchar(255),
@MachineName nvarchar(7),
@InsBegDt smalldatetime(4),
@InsEndDt smalldatetime(4),
@ShipBegDt smalldatetime(4),
@ShipEndDt smalldatetime(4))
AS 
SET NOCOUNT ON 
SELECT dbo.TokenRing_BDCs.Site_Code,
dbo.TokenRing_BDCs.Office_type, 
dbo.TokenRing_BDCs.Address, 
dbo.TokenRing_BDCs.City, 
dbo.TokenRing_BDCs.State, 
dbo.TokenRing_BDCs.Zip, 
dbo.TokenRing_BDCs.Pri_Contact,
dbo.TokenRing_BDCs.Pri_Phone,
dbo.TokenRing_BDCs.Sec_Contact,
dbo.TokenRing_BDCs.Sec_Phone,
dbo.TokenRing_BDCs.Site_Name,
dbo.TokenRing_BDCs.Server_Model,
dbo.TokenRing_BDCs.Est_ShipDate,
dbo.TokenRing_BDCs.Installation_Date,
dbo.TokenRing_BDCs.Domain, 
dbo.TokenRing_BDCs.MachineName, 
dbo.Site.sRegion
FROM dbo.Site LEFT OUTER JOIN
dbo.TokenRing_BDCs ON dbo.Site.sSiteCode = dbo.TokenRing_BDCs.Site_Code
WHERE(dbo.TokenRing_BDCs.Office_type = COALESCE (@OfficeType, dbo.TokenRing_BDCs.Office_type)) AND (dbo.Site.sRegion = COALESCE (@Region, dbo.Site.sRegion)) AND(dbo.TokenRing_BDCs.Server_Model = COALESCE (@ServerModel, dbo.TokenRing_BDCs.Server_Model)) AND (dbo.TokenRing_BDCs.Domain = COALESCE (@Domain, dbo.TokenRing_BDCs.Domain)) AND(dbo.TokenRing_BDCs.MachineName = COALESCE (@MachineName, dbo.TokenRing_BDCs.MachineName)) AND 
(dbo.TokenRing_BDCs.Installation_Date BETWEEN COALESCE (@InsBegDt, dbo.TokenRing_BDCs.Installation_Date) AND COALESCE (@InsEndDt,dbo.TokenRing_BDCs.Installation_Date)) AND (dbo.TokenRing_BDCs.Est_ShipDate BETWEEN COALESCE (@ShipBegDt,dbo.TokenRing_BDCs.Est_ShipDate) AND COALESCE (@ShipEndDt, dbo.TokenRing_BDCs.Est_ShipDate)) AND 
(dbo.TokenRing_BDCs.Site_Code = COALESCE (@SiteCode, dbo.TokenRing_BDCs.Site_Code))

I was thinking the COALESCE function may be causing the mishap. Let me know what you think the problem may be. Thanks again.