|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
SQL Printing Problem (Stored Procedure Dynamic Programming)
Ive put this into the query Analyzer just to see that the str variables (strCMDAND & strCMDOR) are being built properly
but near the end before the declare @strPropID varchar(35) declare @strPDPM varchar(10) declare @strBusinessGroup varchar(40) declare @strLocation varchar(20) declare @strStudyType varchar(20) declare @strStatus varchar(6) declare @strProposalName varchar(35) declare @dtmProposalDateMin datetime declare @dtmProposalDateMax datetime declare @strContact varchar(40) declare @strCompany varchar(40) DECLARE @strCMDAND varChar(8000) DECLARE @strCMDOR varChar(8000) SET @strCMDAND = 'SELECT a.TITLE, a.CONTSUPREF,a. ZIP,a. ADDRESS1,a.COUNTRY, a.STATE, a.ADDRESS2, a.EXT, a.LINKACCT, a.NOTES, a.ACCOUNTNO, b.COMPANY, b.CONTACT, b.CITY, b.PHONE1, b.PHONE2, b.PHONE3, b.STATE as c1STATE, b.COUNTRY as c1COUNTRY FROM CONTSUPP a INNER JOIN CONTACT1 b on a.ACCOUNTNO = b.ACCOUNTNO WHERE ( ' --1 IF (@strPropID IS NOT NULL) BEGIN SET @strCMDAND = @strCMDOR +'(a.TITLE LIKE ''%'' + @strPropID + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(a.TITLE = NULL) AND ' END --2 IF (@strPDPM IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.ZIP LIKE ''%'' + @strPDPM + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(a.ZIP = NULL) AND ' END --3 IF (@strBusinessGroup IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.ADDRESS1 LIKE ''%'' + @strBusinessGroup + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(a.ADDRESS1 = NULL) AND ' --print(@strCMDAND) END --4 IF (@strLocation IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '((a.COUNTRY LIKE ''%'' + @strLocation + ''%'') OR (a.STATE LIKE ''%'' + @strLocation + ''%'')) AND ' SET @strCMDOR = @strCMDOR + '((a.COUNTRY = NULL) OR (a.STATE = NULL)) ' END --5 IF (@strStudyType IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.ADDRESS2 LIKE ''%'' + @strStudyType + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(a.ADDRESS2 = NULL) AND ' END --6 IF(@strStatus IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(a.EXT LIKE ''%'' + @strStatus + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(a.EXT = NULL) AND ' END --7 IF (@strProposalName IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + 'a.CONTSUPREF LIKE ''%'' + @strProposalName + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(a.CONTSUPREF = NULL) AND ' END --8 IF ((@dtmProposalDateMin IS NOT NULL) OR (@dtmProposalDateMax IS NOT NULL)) BEGIN SET @strCMDAND = @strCMDAND + '(a.LASTDATE > @dtmProposalDateMin) AND (a.LASTDATE < @dtmProposalDateMax) AND ' SET @strCMDOR = @strCMDOR + '(a.LASTDATE = NULL) AND ' END --9 IF (@strContact IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND + '(b.CONTACT LIKE ''%'' + @strContact + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(b.CONTACT = NULL) AND ' END --10 IF (@strCompany IS NOT NULL) BEGIN SET @strCMDAND = @strCMDAND+ '(b.COMPANY LIKE ''%'' + @strCompany + ''%'') AND ' SET @strCMDOR = @strCMDOR + '(b.COMPANY = NULL) AND ' END --Print(@strCMDAND) --Put the entire string together so that it can be executed. 1=1 is for the extra AND that will be at the end of the next condition since we dont know when it will stop adding conditions SET @strCMDAND = @strCMDAND + '1=1 OR ('+@strCMDOR+'1=1)) AND (a.RECTYPE = ''P'') AND (a.CONTACT = ''PROPOSAL'')' Print('Hello') Print(@strCMDAND) GO This print statement will not work just a blank blank comes out but the Hello does appear.. its very strange the variable will print before the set statement but not after.. Some Help would be appreciated ![]() The print statement will be changed to EXEC in the SP |
|
#2
|
|||
|
|||
|
OOPS Stupid Me..
I figured it out already.. I forgot to initialize @strCMDOR....
![]() |
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > SQL Printing Problem (Stored Procedure Dynamic Programming) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|