Stored Procedure Parameters Not Working

[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 and 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.

Thanks in advance

 

[mrdenny]

It should work fine. If it's not working, then something isn't getting passed over correctly. An easy trouble shooting method for ASP and SQL is to output the exact SQL command that is being executed by the ASP script and copy and paste that into QA and run it. That will probably give you a better error message.

You are probably missing a quote or something like that.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TheStriker]

mrdenny,

I should point out that I don't get an error message when I call it, it just does nothing and returns no records but when I execute it in SQL Server it works without a hitch. It does take an average of about 2 to 3 seconds to process and I was wondering if that was the problem.

 

[mrdenny]

shouldn't be. If it was a timeout you would get an error message. Not to mention that the default timeout is 60 seconds. Run profiler then run it from the web page. Make sure that it's actually trying to run the procedure.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TheStriker]

OK, I've tried just about everything and for some reason it just will not carry over the values and populate the parameters for the stored procedure. I've tried using the stored procedure itself in Dreamweaver, using just the SQL, replacing Dreamweaver code with the values themselves but to no avail. This is really baffling me. How it can work flawlessly in one place and not work at all with the same code on another is beyond me. Any suggestions or advice is appreciated on this one.

 

[SQLSister]

As a guess I would say that the problem lies in how you are calling it in Dreamweaver. YOu said it didn't work even when you hard coded the values, so I suspect it is never sending the stored procedure to SQL Server to run.
YOu can confirm that using Profiler as suggested earlier.


Questions about posting. See faq183-874

 

[vongrunt]

And if it comes to server, blind guessing: '' and NULL aren't the same, especially for COALESCE() function.

 

[TheStriker]

I use the Dreamweaver Dialog Windows to call it and I've never had a problem before with it. However this is the first time I've used COALESCE, which pretty much isolates IT as the problem.

I think the problem also may be that Dreamweaver requires a default value for the parameters. I've tried using NULL and the wildcard character(%) and it still does nothing. I've tried using the Profiler but I'm not in the sysadmin group so I can only use the Query Analyzer.

 

[SQLSister]

Well you can test to see if it is the coalesce function, by temporarily removing it and then running the sp. If it runs, then it is that function if it doesn't then you have a problem somewhere else.

You could also test how it is calling the sp by changin the sp name (and parameter values) to the name of one that works somewhere else and see if it runs. If a known good sp doesn;t run then the problem would definitely be in how it is valling it.

Questions about posting. See faq183-874

 

[TheStriker]

Could it be that I am using COALESCE on columns with nvarchar datatype and not using a NULL value. If so, how do I pass a NULL value with nvarchar columns for COALESCE to work?

 

[vongrunt]

We can guess until our galaxy collapses into massive black hole ... can you post stored procedure code?

 

[TheStriker]

OK Here is the code from the stored procedure.

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))

This works in SQL Server but not in Dreamweaver. I'm wondering if you can use COALESCE with nvarchar datatypes. Feel free to advise.

 

[vongrunt]

COALESCE() works with any data type:

select coalesce( null, N'Hey, it works!' )
select coalesce( N'blah', N'No, this won''t show up' )

It appears you used it here basically to avoid dynamic SQL - if any of input arguments is NULL, COALESCE() will short-circuit this part of WHERE condition as "always true". All arguments are required, everything looks fine.

My guess is that one or more "missing" input arguments is set to '' or maybe even 'NULL' rather than NULL - this is often the case with web sites/applications. If you don't have access to profiler, then... screw Dreamweaver, can you manually insert Response.Write my_SQLStatement: Response.End immediately before SP call and see what's going on?

 

[TheStriker]

Here is what is sent when the stored procedure is called from Dreamweaver:

{call dbo.CustomBDC('SiteCode','OfficeType','Region','ServerModel','Domain',
'MachineName',InsBegDt,InsEndDt,ShipBegDt,ShipEndDt)}

Notice the smalldatetime variables don't have quotes around them for obvious reasons. However, if I remove the quotes from the nvarchar variables, I get the message: 'error converting nvarchar to smalldatetime'. I realize that with variables in quotes the NULL value will confuse the COALESCE function, and so will return 'NULL' which is invalid. My next move is to try to find a workaround of some sort. Any ideas? Feel free to correct my theory.