Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free ForumsDatabaseMicrosoft SQL Server

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old October 21st, 2004, 09:48 AM
HerrKutt HerrKutt is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 7 HerrKutt User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 m 46 sec
Reputation Power: 0
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

Reply With Quote
  #2  
Old October 21st, 2004, 10:24 AM
HerrKutt HerrKutt is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Oct 2004
Posts: 7 HerrKutt User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 7 m 46 sec
Reputation Power: 0
OOPS Stupid Me..

I figured it out already.. I forgot to initialize @strCMDOR....

Reply With Quote
Reply

Viewing: ASP Free ForumsDatabaseMicrosoft SQL Server > SQL Printing Problem (Stored Procedure Dynamic Programming)


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway