Conditional statement built dependent on variable values

[wbodger]

I have this code

USE [CreditReports]
GO

/****** Object:  StoredProcedure [dbo].[spx_GetCustomers]    Script Date: 02/04/2016 19:23:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[spx_GetCustomers]

      @FilterCompany VARCHAR(50),
      @FilterSyspro VARCHAR(6),
      @Year VARCHAR(6),
      @Month VARCHAR(6),
      @Analyzed VARCHAR(6)

AS
-- This stored procedures pulls the customer credit reports filtered
BEGIN

      SET NOCOUNT ON;

Declare @Conditions varchar(4000);

set @Conditions = '	SELECT [ID],[Company Name],[URL],[Syspro Code],[Company Type],[D&B Number],[Next Review Due],[Date Report Pulled],[Date Analyzed],isnull([Paydex],0) as Paydex,isnull([3 Month Paydex],0) as [3 Month Paydex],isnull([Commercial Credit Class],0) as [Commercial Credit Class],
	isnull([Financial Stress Class],0) as [Financial Stress Class],isnull([Years in Business],0) as [Years in Business],isnull([Current Ratio],0) as [Current Ratio],isnull([Viability Score],0) as [Viability Score],
	isnull([Portfolio Comparison],0) as [Portfolio Comparison],isnull([Data Depth Indicator],0) as [Data Depth Indicator],isnull([Company Profile],0) as [Company Profile],isnull([Previous 12 Month Sales],0) as [Previous 12 Month Sales],
	isnull([Forecasted 12 Month Sales],0) as [Forecasted 12 Month Sales],isnull([Current Credit Terms],0) as [Current Credit Terms],isnull([Actual Credit Terms],0) as [Actual Credit Terms],[Notes],[Year] 
	FROM Customers '
IF (@FilterCompany = 'All')
set @Conditions = @Conditions + ' WHERE 1=1 '      
ELSE
set @Conditions = @Conditions + ' WHERE [Company Name] = ' + @FilterCompany + ' '

IF (@FilterSyspro <> 'All')
Set @Conditions = @Conditions + 'AND [Syspro Code] = ' + @FilterSyspro + ' '

IF (@Year <> 'All')
set @Conditions = @Conditions + 'AND Year = ' + @Year + ' '

IF (@Month <> 'All')
set @Conditions = @Conditions + 'AND DatePart(mm,[Date Report Pulled]) = ' + @Month + ' '

IF (@Analyzed <> 'All')
set @Conditions = @Conditions + 'AND [Date Analyzed] is null'

set	@Conditions = @Conditions + 'order by [Date Report Pulled] DESC'

Print @Conditions

Exec(@Conditions)

which works fine if I run the stored procedure manually, but when I call it from a web page it doesn't work

                string Analyzed = Request.QueryString["Analyzed"];
                string Year = Request.QueryString["Year"];
                string Month = Request.QueryString["Month"];
                string SysproCode = ViewState["FilterSyspro"].ToString();

                if (String.IsNullOrEmpty(Analyzed))
                    Analyzed = "All";
                if (String.IsNullOrEmpty(Year))
                    Year = "All";
                if (String.IsNullOrEmpty(Month))
                    Month = "All";
                if (String.IsNullOrEmpty(SysproCode))
                    SysproCode = "All";

                Response.Write("FilterCompany: " + ViewState["FilterCompany"] + "<br>");
                Response.Write("FilterSyspro: " + SysproCode + "<br>");
                Response.Write("Analyzed: " + Analyzed + "<br>");
                Response.Write("Year: " + Year + "<br>");
                Response.Write("Month: " + Month + "");



                DataTable dt = new DataTable();
                String strConnString = System.Configuration.ConfigurationManager
                            .ConnectionStrings["credit"].ConnectionString;
                SqlConnection con = new SqlConnection(strConnString);
                SqlDataAdapter sda = new SqlDataAdapter();
                SqlCommand cmd = new SqlCommand("spx_GetCustomers");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FilterCompany", ViewState["FilterCompany"].ToString());
                cmd.Parameters.AddWithValue("@FilterSyspro", SysproCode);
                cmd.Parameters.AddWithValue("@Year", Year);
                cmd.Parameters.AddWithValue("@Month", Month);
                cmd.Parameters.AddWithValue("@Analyzed", Analyzed);

My variables are written out correctly on the screen, I just get no output. Any ideas? Should I post this to the .Net forum instead?

Thanks in advance,
Willie

 

[bborissov]

I don't see where you execute SP in your ASP file.

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

Long shot.... try removing the print statement from the stored procedure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mharroff]

Check these 2 lines and try adding the quotes I put in red

set @Conditions = @Conditions + ' WHERE [Company Name] = ''' + @FilterCompany + ''' '
Set @Conditions = @Conditions + 'AND [Syspro Code] = ''' + @FilterSyspro + ''' '

 

[wbodger]

Hmm... neither of those helped. Given what they gave me, I am pretty sure the SQL is correct, as I can call it directly, so it looks like something with the passing of values. This is a strictly internal system, is there any way to see what SQL code it it trying to run when I call the sproc from a web page?

Thanks,
Willie

 

[gmmastros]

is there any way to see what SQL code it it trying to run when I call the sproc from a web page?

Yes. This is exactly what SQL Profiler is meant to do.

If you plan on using SQL Profiler on your production database, you should use it sparingly because it will slow down your database. Basically, what I'm saying is... start profiler at the last moment, and then stop it as soon as you've captured your query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Realized that I didn't have a catch on the asp.net side. Once I added that I saw that I had created a new stored procedure and not granted permissions to it. Granted permissions and voila!

Thanks,
wb