|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
stored procedure stopped workingI have a stored procedure that I created a week or so ago and it stopped working this morning. I use it in a report and the report stopped filtering data and I traced the problem back to the sp. This is the sp: CREATE PROCEDURE [dbo].[uspReportBillingDetail] @StartDate datetime, @EndDate datetime, @License varchar(6000), @FastStart int AS /* Name: uspReportBillingDetail Description: Shows detail of mailings and redeemed for billing purposes. Inputs: @StartDate datetime, @EndDate datetime, @License, @FastStart Outputs: All mailings and redeem activity for date specified. Author: Dan Donahue */ BEGIN DECLARE @SQL varchar(8000) SET @SQL = 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, dbo.tblFranchise.varFranchiseName AS BusinessName, dbo.tblCompany.varCompanyName AS CompanyName, CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then "Mailed" Else dbo.tblPrograms.varProgramName End Item, CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost Else dbo.tblPrograms.decInitialCost End ItemCost, dbo.tblTaskDetails.dtTaskDate AS DateBilled, CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then dbo.tblRedeem.RedeemDate When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then dbo.tblTaskDetails.dtTaskDate Else NULL End RedeemDate FROM dbo.tblTaskDetails JOIN dbo.tblCommunicationTypeResults ON dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId JOIN dbo.tblCommunicationType ON dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId JOIN dbo.tblFranchise ON dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId LEFT OUTER JOIN dbo.tblRedeem ON dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' IF @License <> NULL AND @License <> '' SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + ')' IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS varchar(32)) SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, dbo.tblCompany.varCompanyName ' EXEC(@SQL) END GO The problem is that neither "IF" statement at the end works. If I comment out the line "IF @License <> NULL AND @License <> '' " and pass in a license number it works. But with otherwise it won't work. I added a "Print(@SQL)" statement and regardless of how a try to pass in a license and faststart value, they won't get appended. What I can't understand is why after working for a week, it just stopped working. Any ideas? Thanks, -- Dan D. Dan D. wrote:
Show quote > Using SS2000 SP4. Instead of saying> I have a stored procedure that I created a week or so ago and it stopped > working this morning. I use it in a report and the report stopped filtering > data and I traced the problem back to the sp. This is the sp: > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > @StartDate datetime, > @EndDate datetime, > @License varchar(6000), > @FastStart int > AS > > /* > Name: uspReportBillingDetail > Description: Shows detail of mailings and redeemed for billing purposes. > Inputs: @StartDate datetime, > @EndDate datetime, > @License, > @FastStart > Outputs: All mailings and redeem activity for date specified. > Author: Dan Donahue > */ > > BEGIN > > DECLARE @SQL varchar(8000) > > SET @SQL = > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > dbo.tblFranchise.varFranchiseName AS BusinessName, > dbo.tblCompany.varCompanyName AS CompanyName, > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > "Mailed" > Else dbo.tblPrograms.varProgramName > End Item, > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > Else dbo.tblPrograms.decInitialCost > End ItemCost, > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > dbo.tblRedeem.RedeemDate > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > dbo.tblTaskDetails.dtTaskDate > Else NULL > End RedeemDate > FROM dbo.tblTaskDetails > JOIN dbo.tblCommunicationTypeResults ON > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > JOIN dbo.tblCommunicationType ON > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > JOIN dbo.tblFranchise ON > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > LEFT OUTER JOIN dbo.tblRedeem ON > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > IF @License <> NULL AND @License <> '' > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > ')' > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > varchar(32)) > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > dbo.tblCompany.varCompanyName ' > > EXEC(@SQL) > END > GO > > The problem is that neither "IF" statement at the end works. If I comment > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > statement and regardless of how a try to pass in a license and faststart > value, they won't get appended. What I can't understand is why after working > for a week, it just stopped working. > > Any ideas? Thanks, @License <> NULL @FastStart <> NULL Use @License IS NULL @FastStart IS NULL The comparison you're doing is invalid... Small typo Tracy, you forgot the NOT
@License IS NOT NULL @FastStart IS NOT NULL Denis the SQL Menace http://sqlservercode.blogspot.com/ Tracy McKibben wrote: Show quote > Dan D. wrote: > > Using SS2000 SP4. > > I have a stored procedure that I created a week or so ago and it stopped > > working this morning. I use it in a report and the report stopped filtering > > data and I traced the problem back to the sp. This is the sp: > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > @StartDate datetime, > > @EndDate datetime, > > @License varchar(6000), > > @FastStart int > > AS > > > > /* > > Name: uspReportBillingDetail > > Description: Shows detail of mailings and redeemed for billing purposes. > > Inputs: @StartDate datetime, > > @EndDate datetime, > > @License, > > @FastStart > > Outputs: All mailings and redeem activity for date specified. > > Author: Dan Donahue > > */ > > > > BEGIN > > > > DECLARE @SQL varchar(8000) > > > > SET @SQL = > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > dbo.tblCompany.varCompanyName AS CompanyName, > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > "Mailed" > > Else dbo.tblPrograms.varProgramName > > End Item, > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > Else dbo.tblPrograms.decInitialCost > > End ItemCost, > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > dbo.tblRedeem.RedeemDate > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > dbo.tblTaskDetails.dtTaskDate > > Else NULL > > End RedeemDate > > FROM dbo.tblTaskDetails > > JOIN dbo.tblCommunicationTypeResults ON > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > JOIN dbo.tblCommunicationType ON > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > JOIN dbo.tblFranchise ON > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > LEFT OUTER JOIN dbo.tblRedeem ON > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > IF @License <> NULL AND @License <> '' > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > ')' > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > varchar(32)) > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > dbo.tblCompany.varCompanyName ' > > > > EXEC(@SQL) > > END > > GO > > > > The problem is that neither "IF" statement at the end works. If I comment > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > statement and regardless of how a try to pass in a license and faststart > > value, they won't get appended. What I can't understand is why after working > > for a week, it just stopped working. > > > > Any ideas? Thanks, > > Instead of saying > > @License <> NULL > @FastStart <> NULL > > Use > > @License IS NULL > @FastStart IS NULL > > The comparison you're doing is invalid... > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com SQL Menace wrote:
> Small typo Tracy, you forgot the NOT That's the last time I let my trained monkey do my typing...> > @License IS NOT NULL > @FastStart IS NOT NULL > Well eventually the monkeys will.....
http://www.wired.com/news/culture/0,1284,58790,00.html ;-) Who knows what else they might write (SQL anyone? ) Denis the SQL Menace http://sqlservercode.blogspot.com/ Tracy McKibben wrote: Show quote > SQL Menace wrote: > > Small typo Tracy, you forgot the NOT > > > > @License IS NOT NULL > > @FastStart IS NOT NULL > > > > That's the last time I let my trained monkey do my typing... > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com SQL Menace wrote:
> Well eventually the monkeys will..... They're already doing that! There's a group of them doing that right > http://www.wired.com/news/culture/0,1284,58790,00.html > > ;-) > > Who knows what else they might write (SQL anyone? ) > now, just down the hall from me... Dan D. wrote:
Show quote > Using SS2000 SP4. Denise gave you the right solution. But to explain why your existing> I have a stored procedure that I created a week or so ago and it stopped > working this morning. I use it in a report and the report stopped filtering > data and I traced the problem back to the sp. This is the sp: > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > @StartDate datetime, > @EndDate datetime, > @License varchar(6000), > @FastStart int > AS > > /* > Name: uspReportBillingDetail > Description: Shows detail of mailings and redeemed for billing purposes. > Inputs: @StartDate datetime, > @EndDate datetime, > @License, > @FastStart > Outputs: All mailings and redeem activity for date specified. > Author: Dan Donahue > */ > > BEGIN > > DECLARE @SQL varchar(8000) > > SET @SQL = > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > dbo.tblFranchise.varFranchiseName AS BusinessName, > dbo.tblCompany.varCompanyName AS CompanyName, > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > "Mailed" > Else dbo.tblPrograms.varProgramName > End Item, > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > Else dbo.tblPrograms.decInitialCost > End ItemCost, > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > dbo.tblRedeem.RedeemDate > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > dbo.tblTaskDetails.dtTaskDate > Else NULL > End RedeemDate > FROM dbo.tblTaskDetails > JOIN dbo.tblCommunicationTypeResults ON > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > JOIN dbo.tblCommunicationType ON > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > JOIN dbo.tblFranchise ON > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > LEFT OUTER JOIN dbo.tblRedeem ON > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > IF @License <> NULL AND @License <> '' > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > ')' > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > varchar(32)) > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > dbo.tblCompany.varCompanyName ' > > EXEC(@SQL) > END > GO > > The problem is that neither "IF" statement at the end works. If I comment > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > statement and regardless of how a try to pass in a license and faststart > value, they won't get appended. What I can't understand is why after working > for a week, it just stopped working. > > Any ideas? Thanks, > -- > Dan D. code worked and now it doesn't is because of your ansi_null settings in your connection or database has changed. Run this code and it should be self explanatory. set ansi_nulls off declare @tvalue int Set @tvalue = 1 IF @tNULL <> NULL begin print 'good' end set ansi_nulls on IF @tNULL <> NULL begin print 'wont print' end Did you mean to put @tvalue instead of @tNULL?
Is using db_option the only way to set ansi_nulls? I doubt that anyone esle here knows how to do that so I don't know how it could have changed. I ran USE master EXEC sp_dboption 'smclms_dev' and only results for torn pages, auto create statistics and auto update statistics show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp work? I changed "IF @License <> NULL AND @License <> '' " to "IF @License IS NOT NULL AND @License <> '' " and it worked without changing anything regarding ansi_nulls. Don't the two single quotes as @License <> '' also mean not null? I copied this code from another stored procedure that was working but thinking about it now, it seems redundant. Thanks for your help, -- Show quoteDan D. "Ken" wrote: > > Dan D. wrote: > > Using SS2000 SP4. > > I have a stored procedure that I created a week or so ago and it stopped > > working this morning. I use it in a report and the report stopped filtering > > data and I traced the problem back to the sp. This is the sp: > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > @StartDate datetime, > > @EndDate datetime, > > @License varchar(6000), > > @FastStart int > > AS > > > > /* > > Name: uspReportBillingDetail > > Description: Shows detail of mailings and redeemed for billing purposes. > > Inputs: @StartDate datetime, > > @EndDate datetime, > > @License, > > @FastStart > > Outputs: All mailings and redeem activity for date specified. > > Author: Dan Donahue > > */ > > > > BEGIN > > > > DECLARE @SQL varchar(8000) > > > > SET @SQL = > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > dbo.tblCompany.varCompanyName AS CompanyName, > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > "Mailed" > > Else dbo.tblPrograms.varProgramName > > End Item, > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > Else dbo.tblPrograms.decInitialCost > > End ItemCost, > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > dbo.tblRedeem.RedeemDate > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > dbo.tblTaskDetails.dtTaskDate > > Else NULL > > End RedeemDate > > FROM dbo.tblTaskDetails > > JOIN dbo.tblCommunicationTypeResults ON > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > JOIN dbo.tblCommunicationType ON > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > JOIN dbo.tblFranchise ON > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > LEFT OUTER JOIN dbo.tblRedeem ON > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > IF @License <> NULL AND @License <> '' > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > ')' > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > varchar(32)) > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > dbo.tblCompany.varCompanyName ' > > > > EXEC(@SQL) > > END > > GO > > > > The problem is that neither "IF" statement at the end works. If I comment > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > statement and regardless of how a try to pass in a license and faststart > > value, they won't get appended. What I can't understand is why after working > > for a week, it just stopped working. > > > > Any ideas? Thanks, > > -- > > Dan D. > > Denise gave you the right solution. But to explain why your existing > code worked and now it doesn't is because of your ansi_null settings in > your connection or database has changed. Run this code and it should be > self explanatory. > > > set ansi_nulls off > > declare @tvalue int > Set @tvalue = 1 > > > IF @tNULL <> NULL > begin > print 'good' > end > > > set ansi_nulls on > > > IF @tNULL <> NULL > begin > print 'wont print' > end > > Dan D. wrote:
Show quote > Did you mean to put @tvalue instead of @tNULL? No, '' is not NULL. '' is simply an empty string, NULL means nothing, > Is using db_option the only way to set ansi_nulls? > I doubt that anyone esle here knows how to do that so I don't know > how it could have changed. > > I ran > USE master > EXEC sp_dboption 'smclms_dev' > and only results for torn pages, auto create statistics and auto update > statistics > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > work? > > I changed "IF @License <> NULL AND @License <> '' " > to "IF @License IS NOT NULL AND @License <> '' " > and it worked without changing anything regarding ansi_nulls. > > Don't the two single quotes as @License <> '' also mean not null? > I copied this code from another stored procedure that was working but > thinking about it > now, it seems redundant. > > Thanks for your help, no value, unknown... Run DBCC USEROPTIONS to see your settings
SET ANSI_DEFAULTS OFF will set all of these to off SET ANSI_NULLS SET CURSOR_CLOSE_ON_COMMIT SET ANSI_NULL_DFLT_ON SET IMPLICIT_TRANSACTIONS SET ANSI_PADDING SET QUOTED_IDENTIFIER SET ANSI_WARNINGS so there are at least 2 ways to do it Denis the SQL Menace http://sqlservercode.blogspot.com/ Dan D. wrote: Show quote > Did you mean to put @tvalue instead of @tNULL? > Is using db_option the only way to set ansi_nulls? > I doubt that anyone esle here knows how to do that so I don't know > how it could have changed. > > I ran > USE master > EXEC sp_dboption 'smclms_dev' > and only results for torn pages, auto create statistics and auto update > statistics > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > work? > > I changed "IF @License <> NULL AND @License <> '' " > to "IF @License IS NOT NULL AND @License <> '' " > and it worked without changing anything regarding ansi_nulls. > > Don't the two single quotes as @License <> '' also mean not null? > I copied this code from another stored procedure that was working but > thinking about it > now, it seems redundant. > > Thanks for your help, > -- > Dan D. > > > "Ken" wrote: > > > > > Dan D. wrote: > > > Using SS2000 SP4. > > > I have a stored procedure that I created a week or so ago and it stopped > > > working this morning. I use it in a report and the report stopped filtering > > > data and I traced the problem back to the sp. This is the sp: > > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > > @StartDate datetime, > > > @EndDate datetime, > > > @License varchar(6000), > > > @FastStart int > > > AS > > > > > > /* > > > Name: uspReportBillingDetail > > > Description: Shows detail of mailings and redeemed for billing purposes. > > > Inputs: @StartDate datetime, > > > @EndDate datetime, > > > @License, > > > @FastStart > > > Outputs: All mailings and redeem activity for date specified. > > > Author: Dan Donahue > > > */ > > > > > > BEGIN > > > > > > DECLARE @SQL varchar(8000) > > > > > > SET @SQL = > > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > > dbo.tblCompany.varCompanyName AS CompanyName, > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > "Mailed" > > > Else dbo.tblPrograms.varProgramName > > > End Item, > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > > Else dbo.tblPrograms.decInitialCost > > > End ItemCost, > > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > > dbo.tblRedeem.RedeemDate > > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > > dbo.tblTaskDetails.dtTaskDate > > > Else NULL > > > End RedeemDate > > > FROM dbo.tblTaskDetails > > > JOIN dbo.tblCommunicationTypeResults ON > > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > > JOIN dbo.tblCommunicationType ON > > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > > JOIN dbo.tblFranchise ON > > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > > LEFT OUTER JOIN dbo.tblRedeem ON > > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > > > IF @License <> NULL AND @License <> '' > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > > ')' > > > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > > varchar(32)) > > > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > > dbo.tblCompany.varCompanyName ' > > > > > > EXEC(@SQL) > > > END > > > GO > > > > > > The problem is that neither "IF" statement at the end works. If I comment > > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > > statement and regardless of how a try to pass in a license and faststart > > > value, they won't get appended. What I can't understand is why after working > > > for a week, it just stopped working. > > > > > > Any ideas? Thanks, > > > -- > > > Dan D. > > > > Denise gave you the right solution. But to explain why your existing > > code worked and now it doesn't is because of your ansi_null settings in > > your connection or database has changed. Run this code and it should be > > self explanatory. > > > > > > set ansi_nulls off > > > > declare @tvalue int > > Set @tvalue = 1 > > > > > > IF @tNULL <> NULL > > begin > > print 'good' > > end > > > > > > set ansi_nulls on > > > > > > IF @tNULL <> NULL > > begin > > print 'wont print' > > end > > > > Well that returns this:
textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET I guess that means that ansi_nulls in ON but why didn't using sp_dboption give me the same thing? Thanks to everyone for your help. -- Show quoteDan D. "SQL Menace" wrote: > Run DBCC USEROPTIONS to see your settings > > > SET ANSI_DEFAULTS OFF will set all of these to off > > SET ANSI_NULLS > SET CURSOR_CLOSE_ON_COMMIT > SET ANSI_NULL_DFLT_ON > SET IMPLICIT_TRANSACTIONS > SET ANSI_PADDING > SET QUOTED_IDENTIFIER > SET ANSI_WARNINGS > > so there are at least 2 ways to do it > > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > > > Dan D. wrote: > > Did you mean to put @tvalue instead of @tNULL? > > Is using db_option the only way to set ansi_nulls? > > I doubt that anyone esle here knows how to do that so I don't know > > how it could have changed. > > > > I ran > > USE master > > EXEC sp_dboption 'smclms_dev' > > and only results for torn pages, auto create statistics and auto update > > statistics > > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > > work? > > > > I changed "IF @License <> NULL AND @License <> '' " > > to "IF @License IS NOT NULL AND @License <> '' " > > and it worked without changing anything regarding ansi_nulls. > > > > Don't the two single quotes as @License <> '' also mean not null? > > I copied this code from another stored procedure that was working but > > thinking about it > > now, it seems redundant. > > > > Thanks for your help, > > -- > > Dan D. > > > > > > "Ken" wrote: > > > > > > > > Dan D. wrote: > > > > Using SS2000 SP4. > > > > I have a stored procedure that I created a week or so ago and it stopped > > > > working this morning. I use it in a report and the report stopped filtering > > > > data and I traced the problem back to the sp. This is the sp: > > > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > > > @StartDate datetime, > > > > @EndDate datetime, > > > > @License varchar(6000), > > > > @FastStart int > > > > AS > > > > > > > > /* > > > > Name: uspReportBillingDetail > > > > Description: Shows detail of mailings and redeemed for billing purposes. > > > > Inputs: @StartDate datetime, > > > > @EndDate datetime, > > > > @License, > > > > @FastStart > > > > Outputs: All mailings and redeem activity for date specified. > > > > Author: Dan Donahue > > > > */ > > > > > > > > BEGIN > > > > > > > > DECLARE @SQL varchar(8000) > > > > > > > > SET @SQL = > > > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > > > dbo.tblCompany.varCompanyName AS CompanyName, > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > "Mailed" > > > > Else dbo.tblPrograms.varProgramName > > > > End Item, > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > > > Else dbo.tblPrograms.decInitialCost > > > > End ItemCost, > > > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > > > dbo.tblRedeem.RedeemDate > > > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > > > dbo.tblTaskDetails.dtTaskDate > > > > Else NULL > > > > End RedeemDate > > > > FROM dbo.tblTaskDetails > > > > JOIN dbo.tblCommunicationTypeResults ON > > > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > > > JOIN dbo.tblCommunicationType ON > > > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > > > JOIN dbo.tblFranchise ON > > > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > > > LEFT OUTER JOIN dbo.tblRedeem ON > > > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > > > > > IF @License <> NULL AND @License <> '' > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > > > ')' > > > > > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > > > varchar(32)) > > > > > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > > > dbo.tblCompany.varCompanyName ' > > > > > > > > EXEC(@SQL) > > > > END > > > > GO > > > > > > > > The problem is that neither "IF" statement at the end works. If I comment > > > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > > > statement and regardless of how a try to pass in a license and faststart > > > > value, they won't get appended. What I can't understand is why after working > > > > for a week, it just stopped working. > > > > > > > > Any ideas? Thanks, > > > > -- > > > > Dan D. > > > > > > Denise gave you the right solution. But to explain why your existing > > > code worked and now it doesn't is because of your ansi_null settings in > > > your connection or database has changed. Run this code and it should be > > > self explanatory. > > > > > > > > > set ansi_nulls off > > > > > > declare @tvalue int > > > Set @tvalue = 1 > > > > > > > > > IF @tNULL <> NULL > > > begin > > > print 'good' > > > end > > > > > > > > > set ansi_nulls on > > > > > > > > > IF @tNULL <> NULL > > > begin > > > print 'wont print' > > > end > > > > > > > > >From BOL SET statement settings override database option settings (set by usingsp_dboption). In addition, some connection settings are set ON automatically when a user connects to a database based on the values put into effect by the prior use of the sp_configure user options setting, or the values that apply to all ODBC and OLE/DB connections. Denis the SQL Menace http://sqlservercode.blogspot.com/ Dan D. wrote: Show quote > Well that returns this: > textsize 64512 > language us_english > dateformat mdy > datefirst 7 > quoted_identifier SET > arithabort SET > ansi_null_dflt_on SET > ansi_defaults SET > ansi_warnings SET > ansi_padding SET > ansi_nulls SET > concat_null_yields_null SET > > I guess that means that ansi_nulls in ON but why didn't using sp_dboption > give me the same thing? > > Thanks to everyone for your help. > -- > Dan D. > > > "SQL Menace" wrote: > > > Run DBCC USEROPTIONS to see your settings > > > > > > SET ANSI_DEFAULTS OFF will set all of these to off > > > > SET ANSI_NULLS > > SET CURSOR_CLOSE_ON_COMMIT > > SET ANSI_NULL_DFLT_ON > > SET IMPLICIT_TRANSACTIONS > > SET ANSI_PADDING > > SET QUOTED_IDENTIFIER > > SET ANSI_WARNINGS > > > > so there are at least 2 ways to do it > > > > > > Denis the SQL Menace > > http://sqlservercode.blogspot.com/ > > > > > > Dan D. wrote: > > > Did you mean to put @tvalue instead of @tNULL? > > > Is using db_option the only way to set ansi_nulls? > > > I doubt that anyone esle here knows how to do that so I don't know > > > how it could have changed. > > > > > > I ran > > > USE master > > > EXEC sp_dboption 'smclms_dev' > > > and only results for torn pages, auto create statistics and auto update > > > statistics > > > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > > > work? > > > > > > I changed "IF @License <> NULL AND @License <> '' " > > > to "IF @License IS NOT NULL AND @License <> '' " > > > and it worked without changing anything regarding ansi_nulls. > > > > > > Don't the two single quotes as @License <> '' also mean not null? > > > I copied this code from another stored procedure that was working but > > > thinking about it > > > now, it seems redundant. > > > > > > Thanks for your help, > > > -- > > > Dan D. > > > > > > > > > "Ken" wrote: > > > > > > > > > > > Dan D. wrote: > > > > > Using SS2000 SP4. > > > > > I have a stored procedure that I created a week or so ago and it stopped > > > > > working this morning. I use it in a report and the report stopped filtering > > > > > data and I traced the problem back to the sp. This is the sp: > > > > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > > > > @StartDate datetime, > > > > > @EndDate datetime, > > > > > @License varchar(6000), > > > > > @FastStart int > > > > > AS > > > > > > > > > > /* > > > > > Name: uspReportBillingDetail > > > > > Description: Shows detail of mailings and redeemed for billing purposes. > > > > > Inputs: @StartDate datetime, > > > > > @EndDate datetime, > > > > > @License, > > > > > @FastStart > > > > > Outputs: All mailings and redeem activity for date specified. > > > > > Author: Dan Donahue > > > > > */ > > > > > > > > > > BEGIN > > > > > > > > > > DECLARE @SQL varchar(8000) > > > > > > > > > > SET @SQL = > > > > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > > > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > > > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > > > > dbo.tblCompany.varCompanyName AS CompanyName, > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > > "Mailed" > > > > > Else dbo.tblPrograms.varProgramName > > > > > End Item, > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > > > > Else dbo.tblPrograms.decInitialCost > > > > > End ItemCost, > > > > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > > > > dbo.tblRedeem.RedeemDate > > > > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > > > > dbo.tblTaskDetails.dtTaskDate > > > > > Else NULL > > > > > End RedeemDate > > > > > FROM dbo.tblTaskDetails > > > > > JOIN dbo.tblCommunicationTypeResults ON > > > > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > > > > JOIN dbo.tblCommunicationType ON > > > > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > > > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > > > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > > > > JOIN dbo.tblFranchise ON > > > > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > > > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > > > > LEFT OUTER JOIN dbo.tblRedeem ON > > > > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > > > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > > > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > > > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > > > > > > > IF @License <> NULL AND @License <> '' > > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > > > > ')' > > > > > > > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > > > > varchar(32)) > > > > > > > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > > > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > > > > dbo.tblCompany.varCompanyName ' > > > > > > > > > > EXEC(@SQL) > > > > > END > > > > > GO > > > > > > > > > > The problem is that neither "IF" statement at the end works. If I comment > > > > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > > > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > > > > statement and regardless of how a try to pass in a license and faststart > > > > > value, they won't get appended. What I can't understand is why after working > > > > > for a week, it just stopped working. > > > > > > > > > > Any ideas? Thanks, > > > > > -- > > > > > Dan D. > > > > > > > > Denise gave you the right solution. But to explain why your existing > > > > code worked and now it doesn't is because of your ansi_null settings in > > > > your connection or database has changed. Run this code and it should be > > > > self explanatory. > > > > > > > > > > > > set ansi_nulls off > > > > > > > > declare @tvalue int > > > > Set @tvalue = 1 > > > > > > > > > > > > IF @tNULL <> NULL > > > > begin > > > > print 'good' > > > > end > > > > > > > > > > > > set ansi_nulls on > > > > > > > > > > > > IF @tNULL <> NULL > > > > begin > > > > print 'wont print' > > > > end > > > > > > > > > > > > Thanks. I read some more about that.
-- Show quoteDan D. "SQL Menace" wrote: > >From BOL > > SET statement settings override database option settings (set by using > sp_dboption). In addition, some connection settings are set ON > automatically when a user connects to a database based on the values > put into effect by the prior use of the sp_configure user options > setting, or the values that apply to all ODBC and OLE/DB connections. > > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > > > Dan D. wrote: > > Well that returns this: > > textsize 64512 > > language us_english > > dateformat mdy > > datefirst 7 > > quoted_identifier SET > > arithabort SET > > ansi_null_dflt_on SET > > ansi_defaults SET > > ansi_warnings SET > > ansi_padding SET > > ansi_nulls SET > > concat_null_yields_null SET > > > > I guess that means that ansi_nulls in ON but why didn't using sp_dboption > > give me the same thing? > > > > Thanks to everyone for your help. > > -- > > Dan D. > > > > > > "SQL Menace" wrote: > > > > > Run DBCC USEROPTIONS to see your settings > > > > > > > > > SET ANSI_DEFAULTS OFF will set all of these to off > > > > > > SET ANSI_NULLS > > > SET CURSOR_CLOSE_ON_COMMIT > > > SET ANSI_NULL_DFLT_ON > > > SET IMPLICIT_TRANSACTIONS > > > SET ANSI_PADDING > > > SET QUOTED_IDENTIFIER > > > SET ANSI_WARNINGS > > > > > > so there are at least 2 ways to do it > > > > > > > > > Denis the SQL Menace > > > http://sqlservercode.blogspot.com/ > > > > > > > > > Dan D. wrote: > > > > Did you mean to put @tvalue instead of @tNULL? > > > > Is using db_option the only way to set ansi_nulls? > > > > I doubt that anyone esle here knows how to do that so I don't know > > > > how it could have changed. > > > > > > > > I ran > > > > USE master > > > > EXEC sp_dboption 'smclms_dev' > > > > and only results for torn pages, auto create statistics and auto update > > > > statistics > > > > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > > > > work? > > > > > > > > I changed "IF @License <> NULL AND @License <> '' " > > > > to "IF @License IS NOT NULL AND @License <> '' " > > > > and it worked without changing anything regarding ansi_nulls. > > > > > > > > Don't the two single quotes as @License <> '' also mean not null? > > > > I copied this code from another stored procedure that was working but > > > > thinking about it > > > > now, it seems redundant. > > > > > > > > Thanks for your help, > > > > -- > > > > Dan D. > > > > > > > > > > > > "Ken" wrote: > > > > > > > > > > > > > > Dan D. wrote: > > > > > > Using SS2000 SP4. > > > > > > I have a stored procedure that I created a week or so ago and it stopped > > > > > > working this morning. I use it in a report and the report stopped filtering > > > > > > data and I traced the problem back to the sp. This is the sp: > > > > > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > > > > > @StartDate datetime, > > > > > > @EndDate datetime, > > > > > > @License varchar(6000), > > > > > > @FastStart int > > > > > > AS > > > > > > > > > > > > /* > > > > > > Name: uspReportBillingDetail > > > > > > Description: Shows detail of mailings and redeemed for billing purposes. > > > > > > Inputs: @StartDate datetime, > > > > > > @EndDate datetime, > > > > > > @License, > > > > > > @FastStart > > > > > > Outputs: All mailings and redeem activity for date specified. > > > > > > Author: Dan Donahue > > > > > > */ > > > > > > > > > > > > BEGIN > > > > > > > > > > > > DECLARE @SQL varchar(8000) > > > > > > > > > > > > SET @SQL = > > > > > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > > > > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > > > > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > > > > > dbo.tblCompany.varCompanyName AS CompanyName, > > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > > > "Mailed" > > > > > > Else dbo.tblPrograms.varProgramName > > > > > > End Item, > > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > > > > > Else dbo.tblPrograms.decInitialCost > > > > > > End ItemCost, > > > > > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > > > > > dbo.tblRedeem.RedeemDate > > > > > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > > > > > dbo.tblTaskDetails.dtTaskDate > > > > > > Else NULL > > > > > > End RedeemDate > > > > > > FROM dbo.tblTaskDetails > > > > > > JOIN dbo.tblCommunicationTypeResults ON > > > > > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > > > > > JOIN dbo.tblCommunicationType ON > > > > > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > > > > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > > > > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > > > > > JOIN dbo.tblFranchise ON > > > > > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > > > > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > > > > > LEFT OUTER JOIN dbo.tblRedeem ON > > > > > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > > > > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > > > > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > > > > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > > > > > > > > > IF @License <> NULL AND @License <> '' > > > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > > > > > ')' > > > > > > > > > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > > > > > varchar(32)) > > > > > > > > > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > > > > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > > > > > dbo.tblCompany.varCompanyName ' > > > > > > > > > > > > EXEC(@SQL) > > > > > > END > > > > > > GO > > > > > > > > > > > > The problem is that neither "IF" statement at the end works. If I comment > > > > > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > > > > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > > > > > statement and regardless of how a try to pass in a license and faststart > > > > > > value, they won't get appended. What I can't understand is why after working > > > > > > for a week, it just stopped working. > > > > > > > > > > > > Any ideas? Thanks, > > > > > > -- > > > > > > Dan D. > > > > > > > > > > Denise gave you the right solution. But to explain why your existing > > > > > code worked and now it doesn't is because of your ansi_null settings in > > > > > your connection or database has changed. Run this code and it should be > > > > > self explanatory. > > > > > > > > > > > > > > > set ansi_nulls off > > > > > > > > > > declare @tvalue int > > > > > Set @tvalue = 1 > > > > > > > > > > > > > > > IF @tNULL <> NULL > > > > > begin > > > > > print 'good' > > > > > end > > > > > > > > > > > > > > > set ansi_nulls on > > > > > > > > > > > > > > > IF @tNULL <> NULL > > > > > begin > > > > > print 'wont print' > > > > > end > > > > > > > > > > > > > > > > > > When I run dbcc useroptions I get this:
textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET If someone creates a stored procedure using this: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspReportActivity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[uspReportActivity] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO /* Name: uspReportActivity Description: Shows all call attempts made by ISRs during a specified time range. Inputs: @StartDate datetime, @EndDate datetime, @License varchar(1000), @ISRID varchar(1000) Outputs: All call attempts made by ISRs during a specified time range. Author: Shawn Crawford */ CREATE PROCEDURE [dbo].[uspReportActivity] @StartDate datetime, @EndDate datetime, @License varchar(1000), @ISRID varchar(1000) AS ...... do the settings in the stored procedure override the other settings but only for this stored procedure? Thanks, -- Show quoteDan D. "SQL Menace" wrote: > >From BOL > > SET statement settings override database option settings (set by using > sp_dboption). In addition, some connection settings are set ON > automatically when a user connects to a database based on the values > put into effect by the prior use of the sp_configure user options > setting, or the values that apply to all ODBC and OLE/DB connections. > > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > > > Dan D. wrote: > > Well that returns this: > > textsize 64512 > > language us_english > > dateformat mdy > > datefirst 7 > > quoted_identifier SET > > arithabort SET > > ansi_null_dflt_on SET > > ansi_defaults SET > > ansi_warnings SET > > ansi_padding SET > > ansi_nulls SET > > concat_null_yields_null SET > > > > I guess that means that ansi_nulls in ON but why didn't using sp_dboption > > give me the same thing? > > > > Thanks to everyone for your help. > > -- > > Dan D. > > > > > > "SQL Menace" wrote: > > > > > Run DBCC USEROPTIONS to see your settings > > > > > > > > > SET ANSI_DEFAULTS OFF will set all of these to off > > > > > > SET ANSI_NULLS > > > SET CURSOR_CLOSE_ON_COMMIT > > > SET ANSI_NULL_DFLT_ON > > > SET IMPLICIT_TRANSACTIONS > > > SET ANSI_PADDING > > > SET QUOTED_IDENTIFIER > > > SET ANSI_WARNINGS > > > > > > so there are at least 2 ways to do it > > > > > > > > > Denis the SQL Menace > > > http://sqlservercode.blogspot.com/ > > > > > > > > > Dan D. wrote: > > > > Did you mean to put @tvalue instead of @tNULL? > > > > Is using db_option the only way to set ansi_nulls? > > > > I doubt that anyone esle here knows how to do that so I don't know > > > > how it could have changed. > > > > > > > > I ran > > > > USE master > > > > EXEC sp_dboption 'smclms_dev' > > > > and only results for torn pages, auto create statistics and auto update > > > > statistics > > > > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > > > > work? > > > > > > > > I changed "IF @License <> NULL AND @License <> '' " > > > > to "IF @License IS NOT NULL AND @License <> '' " > > > > and it worked without changing anything regarding ansi_nulls. > > > > > > > > Don't the two single quotes as @License <> '' also mean not null? > > > > I copied this code from another stored procedure that was working but > > > > thinking about it > > > > now, it seems redundant. > > > > > > > > Thanks for your help, > > > > -- > > > > Dan D. > > > > > > > > > > > > "Ken" wrote: > > > > > > > > > > > > > > Dan D. wrote: > > > > > > Using SS2000 SP4. > > > > > > I have a stored procedure that I created a week or so ago and it stopped > > > > > > working this morning. I use it in a report and the report stopped filtering > > > > > > data and I traced the problem back to the sp. This is the sp: > > > > > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > > > > > @StartDate datetime, > > > > > > @EndDate datetime, > > > > > > @License varchar(6000), > > > > > > @FastStart int > > > > > > AS > > > > > > > > > > > > /* > > > > > > Name: uspReportBillingDetail > > > > > > Description: Shows detail of mailings and redeemed for billing purposes. > > > > > > Inputs: @StartDate datetime, > > > > > > @EndDate datetime, > > > > > > @License, > > > > > > @FastStart > > > > > > Outputs: All mailings and redeem activity for date specified. > > > > > > Author: Dan Donahue > > > > > > */ > > > > > > > > > > > > BEGIN > > > > > > > > > > > > DECLARE @SQL varchar(8000) > > > > > > > > > > > > SET @SQL = > > > > > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > > > > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > > > > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > > > > > dbo.tblCompany.varCompanyName AS CompanyName, > > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > > > "Mailed" > > > > > > Else dbo.tblPrograms.varProgramName > > > > > > End Item, > > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > > > > > Else dbo.tblPrograms.decInitialCost > > > > > > End ItemCost, > > > > > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > > > > > dbo.tblRedeem.RedeemDate > > > > > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > > > > > dbo.tblTaskDetails.dtTaskDate > > > > > > Else NULL > > > > > > End RedeemDate > > > > > > FROM dbo.tblTaskDetails > > > > > > JOIN dbo.tblCommunicationTypeResults ON > > > > > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > > > > > JOIN dbo.tblCommunicationType ON > > > > > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > > > > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > > > > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > > > > > JOIN dbo.tblFranchise ON > > > > > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > > > > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > > > > > LEFT OUTER JOIN dbo.tblRedeem ON > > > > > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > > > > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > > > > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > > > > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > > > > > > > > > IF @License <> NULL AND @License <> '' > > > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > > > > > ')' > > > > > > > > > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > > > > > varchar(32)) > > > > > > > > > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > > > > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > > > > > dbo.tblCompany.varCompanyName ' > > > > > > > > > > > > EXEC(@SQL) > > > > > > END > > > > > > GO > > > > > > > > > > > > The problem is that neither "IF" statement at the end works. If I comment > > > > > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > > > > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > > > > > statement and regardless of how a try to pass in a license and faststart > > > > > > value, they won't get appended. What I can't understand is why after working > > > > > > for a week, it just stopped working. > > > > > > > > > > > > Any ideas? Thanks, > > > > > > -- > > > > > > Dan D. > > > > > > > > > > Denise gave you the right solution. But to explain why your existing > > > > > code worked and now it doesn't is because of your ansi_null settings in > > > > > your connection or database has changed. Run this code and it should be > > > > > self explanatory. > > > > > > > > > > > > > > > set ansi_nulls off > > > > > > > > > > declare @tvalue int > > > > > Set @tvalue = 1 > > > > > > > > > > > > > > > IF @tNULL <> NULL > > > > > begin > > > > > print 'good' > > > > > end > > > > > > > > > > > > > > > set ansi_nulls on > > > > > > > > > > > > > > > IF @tNULL <> NULL > > > > > begin > > > > > print 'wont print' > > > > > end > > > > > > > > > > > > > > > > > > IF @License <> NULL AND @License <> '' "
can be replace with IF COALESCE(@License,' ') <> ' ' Denis the SQL Menace http://sqlservercode.blogspot.com/ Dan D. wrote: Show quote > Did you mean to put @tvalue instead of @tNULL? > Is using db_option the only way to set ansi_nulls? > I doubt that anyone esle here knows how to do that so I don't know > how it could have changed. > > I ran > USE master > EXEC sp_dboption 'smclms_dev' > and only results for torn pages, auto create statistics and auto update > statistics > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > work? > > I changed "IF @License <> NULL AND @License <> '' " > to "IF @License IS NOT NULL AND @License <> '' " > and it worked without changing anything regarding ansi_nulls. > > Don't the two single quotes as @License <> '' also mean not null? > I copied this code from another stored procedure that was working but > thinking about it > now, it seems redundant. > > Thanks for your help, > -- > Dan D. > > > "Ken" wrote: > > > > > Dan D. wrote: > > > Using SS2000 SP4. > > > I have a stored procedure that I created a week or so ago and it stopped > > > working this morning. I use it in a report and the report stopped filtering > > > data and I traced the problem back to the sp. This is the sp: > > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > > @StartDate datetime, > > > @EndDate datetime, > > > @License varchar(6000), > > > @FastStart int > > > AS > > > > > > /* > > > Name: uspReportBillingDetail > > > Description: Shows detail of mailings and redeemed for billing purposes. > > > Inputs: @StartDate datetime, > > > @EndDate datetime, > > > @License, > > > @FastStart > > > Outputs: All mailings and redeem activity for date specified. > > > Author: Dan Donahue > > > */ > > > > > > BEGIN > > > > > > DECLARE @SQL varchar(8000) > > > > > > SET @SQL = > > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > > dbo.tblCompany.varCompanyName AS CompanyName, > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > "Mailed" > > > Else dbo.tblPrograms.varProgramName > > > End Item, > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > > Else dbo.tblPrograms.decInitialCost > > > End ItemCost, > > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > > dbo.tblRedeem.RedeemDate > > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > > dbo.tblTaskDetails.dtTaskDate > > > Else NULL > > > End RedeemDate > > > FROM dbo.tblTaskDetails > > > JOIN dbo.tblCommunicationTypeResults ON > > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > > JOIN dbo.tblCommunicationType ON > > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > > JOIN dbo.tblFranchise ON > > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > > LEFT OUTER JOIN dbo.tblRedeem ON > > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > > > IF @License <> NULL AND @License <> '' > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > > ')' > > > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > > varchar(32)) > > > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > > dbo.tblCompany.varCompanyName ' > > > > > > EXEC(@SQL) > > > END > > > GO > > > > > > The problem is that neither "IF" statement at the end works. If I comment > > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > > statement and regardless of how a try to pass in a license and faststart > > > value, they won't get appended. What I can't understand is why after working > > > for a week, it just stopped working. > > > > > > Any ideas? Thanks, > > > -- > > > Dan D. > > > > Denise gave you the right solution. But to explain why your existing > > code worked and now it doesn't is because of your ansi_null settings in > > your connection or database has changed. Run this code and it should be > > self explanatory. > > > > > > set ansi_nulls off > > > > declare @tvalue int > > Set @tvalue = 1 > > > > > > IF @tNULL <> NULL > > begin > > print 'good' > > end > > > > > > set ansi_nulls on > > > > > > IF @tNULL <> NULL > > begin > > print 'wont print' > > end > > > > Thanks.
-- Show quoteDan D. "SQL Menace" wrote: > IF @License <> NULL AND @License <> '' " > > can be replace with > IF COALESCE(@License,' ') <> ' ' > > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > > > > Dan D. wrote: > > Did you mean to put @tvalue instead of @tNULL? > > Is using db_option the only way to set ansi_nulls? > > I doubt that anyone esle here knows how to do that so I don't know > > how it could have changed. > > > > I ran > > USE master > > EXEC sp_dboption 'smclms_dev' > > and only results for torn pages, auto create statistics and auto update > > statistics > > show up. Does that mean that ansi_nulls is off? In which case shouldn't my sp > > work? > > > > I changed "IF @License <> NULL AND @License <> '' " > > to "IF @License IS NOT NULL AND @License <> '' " > > and it worked without changing anything regarding ansi_nulls. > > > > Don't the two single quotes as @License <> '' also mean not null? > > I copied this code from another stored procedure that was working but > > thinking about it > > now, it seems redundant. > > > > Thanks for your help, > > -- > > Dan D. > > > > > > "Ken" wrote: > > > > > > > > Dan D. wrote: > > > > Using SS2000 SP4. > > > > I have a stored procedure that I created a week or so ago and it stopped > > > > working this morning. I use it in a report and the report stopped filtering > > > > data and I traced the problem back to the sp. This is the sp: > > > > CREATE PROCEDURE [dbo].[uspReportBillingDetail] > > > > @StartDate datetime, > > > > @EndDate datetime, > > > > @License varchar(6000), > > > > @FastStart int > > > > AS > > > > > > > > /* > > > > Name: uspReportBillingDetail > > > > Description: Shows detail of mailings and redeemed for billing purposes. > > > > Inputs: @StartDate datetime, > > > > @EndDate datetime, > > > > @License, > > > > @FastStart > > > > Outputs: All mailings and redeem activity for date specified. > > > > Author: Dan Donahue > > > > */ > > > > > > > > BEGIN > > > > > > > > DECLARE @SQL varchar(8000) > > > > > > > > SET @SQL = > > > > 'SELECT dbo.tblFranchise.numFranchiseId AS LicenseNumber, > > > > dbo.tblFranchise.numEnterpriseId AS EnterpriseNumber, > > > > dbo.tblFranchise.varFranchiseName AS BusinessName, > > > > dbo.tblCompany.varCompanyName AS CompanyName, > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > "Mailed" > > > > Else dbo.tblPrograms.varProgramName > > > > End Item, > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (48, 74) Then > > > > dbo.tblPrograms.decMonthlyCost - dbo.tblPrograms.decNAFCost > > > > Else dbo.tblPrograms.decInitialCost > > > > End ItemCost, > > > > dbo.tblTaskDetails.dtTaskDate AS DateBilled, > > > > CASE When dbo.tblTaskDetails.numCommunicationTypeResult in (64, 62) Then > > > > dbo.tblRedeem.RedeemDate > > > > When dbo.tblTaskDetails.numCommunicationTypeResult in (58, 60) Then > > > > dbo.tblTaskDetails.dtTaskDate > > > > Else NULL > > > > End RedeemDate > > > > FROM dbo.tblTaskDetails > > > > JOIN dbo.tblCommunicationTypeResults ON > > > > dbo.tblTaskDetails.numCommunicationTypeResult=dbo.tblCommunicationTypeResults.numCommunicationTypeResultId > > > > JOIN dbo.tblCommunicationType ON > > > > dbo.tblTaskDetails.numCommunicationType=dbo.tblCommunicationType.numCommunicationTypeId > > > > JOIN dbo.tblTasks ON dbo.tblTasks.numTaskId=dbo.tblTaskDetails.numTaskId > > > > JOIN dbo.tblCompany ON dbo.tblTasks.numCompanyId=dbo.tblCompany.numCompanyId > > > > JOIN dbo.tblFranchise ON > > > > dbo.tblTasks.numFranchiseId=dbo.tblFranchise.numFranchiseId > > > > JOIN dbo.tblPrograms ON dbo.tblTasks.numProgramId=dbo.tblPrograms.numProgramId > > > > LEFT OUTER JOIN dbo.tblRedeem ON > > > > dbo.tblTasks.numCompanyId=dbo.tblRedeem.CompanyId > > > > WHERE numCommunicationTypeResult in (48, 64, 74, 58, 60, 62) > > > > AND dbo.tblTaskDetails.dtTaskDate > ''' + CAST(@StartDate AS varchar) + ''' > > > > AND dbo.tblTaskDetails.dtTaskDate < ''' + CAST(@EndDate AS varchar) + ''' ' > > > > > > > > IF @License <> NULL AND @License <> '' > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.numFranchiseId IN (' + @License + > > > > ')' > > > > > > > > IF @FastStart <> NULL AND @FastStart <> -1 AND @FastStart <> '' > > > > SET @SQL = @SQL + ' AND dbo.tblFranchise.FastStart = ' + CAST(@FastStart AS > > > > varchar(32)) > > > > > > > > SET @SQL = @SQL + ' ORDER BY dbo.tblFranchise.numEnterpriseId, > > > > dbo.tblFranchise.numFranchiseId, dbo.tblTaskDetails.dtTaskDate, Item, > > > > dbo.tblCompany.varCompanyName ' > > > > > > > > EXEC(@SQL) > > > > END > > > > GO > > > > > > > > The problem is that neither "IF" statement at the end works. If I comment > > > > out the line "IF @License <> NULL AND @License <> '' " and pass in a license > > > > number it works. But with otherwise it won't work. I added a "Print(@SQL)" > > > > statement and regardless of how a try to pass in a license and faststart > > > > value, they won't get appended. What I can't understand is why after working > > > > for a week, it just stopped working. > > > > > > > > Any ideas? Thanks, > > > > -- > > > > Dan D. > > > > > > Denise gave you the right solution. But to explain why your existing > > > code worked and now it doesn't is because of your ansi_null settings in > > > your connection or database has changed. Run this code and it should be > > > self explanatory. > > > > > > > > > set ansi_nulls off > > > > > > declare @tvalue int > > > Set @tvalue = 1 > > > > > > > > > IF @tNULL <> NULL > > > begin > > > print 'good' > > > end > > > > > > > > > set ansi_nulls on > > > > > > > > > IF @tNULL <> NULL > > > begin > > > print 'wont print' > > > end > > > > > > > > Did you mean to put @tvalue instead of @tNULL?
Yes, sorry. Is using db_option the only way to set ansi_nulls? No, it can be set in query analyzer under tools option/ connection properties. In other words, the connection can be set. Also, I meant to say Denis and not Denise in my previous posting. Sorry Denis If you're reading this.  |
|||||||||||||||||||||||
Other interesting topics