It works either way....You can have a constant on either side. Ie...this is fine..
SELECT * FROM table WHERE 1 = tablecolumn as is SELECT * FROM table WHERE tablecolumn = 1 You can also use logic such as SELECT * FROM table WHERE 1 = 1 etc... -----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED] Sent: 03 July 2003 16:48 To: SQL Subject: Re: Returning a value from a stored procedure. Woohoo! Now that you've solved Dave's problem, would you please explain the purpose of this bit? SELECT @affiliateID = A.AffiliateID, @ratecodeID = B.RateCodeID, @AffiliateName = A.AffiliateName Seems like it should be the other way around... SELECT a.affiliateid = @affiliateid .... TIA, ~Dina ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, July 03, 2003 10:40 AM Subject: RE: Returning a value from a stored procedure. > I AM A MUPPET > > it was staring me in the face all the time... Your select is performing a > join and in the WHERE clause you are using GetDate() and the condition is > >= for records in the DB....this will NEVER EVER be true as SQL Server looks > at the WHOLE string of a date - including the Time which means your > recordset will be empty. > > If you explicitly enter a date - it will work no problems thus : > > CREATE PROCEDURE dbo.sp_test > @ratecode char (10), > @referringdomain varchar (100), > @AffiliateName varchar (100) OUT > AS > DECLARE @affiliateID int, @ratecodeID int > SET @AffiliateName = '' > SELECT @affiliateID = A.AffiliateID, > @ratecodeID = B.RateCodeID, > @AffiliateName = A.AffiliateName > FROM dbo.tbl_A A > INNER JOIN dbo.tbl_B B ON A.AffiliateID = B.AffiliateID > WHERE (A.ReferringDomain = '@referringdomain') > AND (B.RateCode = '@ratecode') > AND (B.ExpirationDate >= '10/10/2002' > > IF @AffiliateName <> '' > INSERT INTO dbo.tbl_C (affiliateID, ratecodeID) > VALUES (@affiliateID,@ratecodeID) > > > > > > -----Original Message----- > From: Bosky, Dave [mailto:[EMAIL PROTECTED] > Sent: 03 July 2003 16:21 > To: SQL > Subject: RE: Returning a value from a stored procedure. > > > I have the insert statement commented out until I figure out why the select > statement is failing. > Something with the line. > SELECT @affiliateID = A.AffiliateID, @ratecodeID = B.RateCodeID, > @AffiliateName = A.AffiliateName > > -----Original Message----- > From: Andy Ewings [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 03, 2003 11:21 AM > To: SQL > Subject: RE: Returning a value from a stored procedure. > > > So is a record being inserted into the table as your logic is doing in the > SP? > > > -----Original Message----- > From: Bosky, Dave [mailto:[EMAIL PROTECTED] > Sent: 03 July 2003 15:18 > To: SQL > Subject: Returning a value from a stored procedure. > > > Are there any SQL guru's that can help me with this stored procedure? > I'm trying to return '@AffiliateName' but it always returns ''. > I know the query matches one record but the value is never saved to the out > variable '@AffiliateName'. > > ------------------------------------------------------------------ > <CFSTOREDPROC PROCEDURE="sp_test" DATASOURCE="dsn"> <CFPROCPARAM > DBVARNAME="@ratecode" TYPE="In" > CFSQLTYPE="cf_sql_char" null="no" VALUE="qtip"> > <CFPROCPARAM DBVARNAME="@referringdomain" TYPE="In" > CFSQLTYPE="cf_sql_varchar" null="no" VALUE="yahoo"> > <CFPROCPARAM DBVARNAME="@AffiliateName" TYPE="out" > CFSQLTYPE="cf_sql_varchar" variable="AffiliateName"> > </CFSTOREDPROC> > ------------------------------------------------------------------ > CREATE PROCEDURE dbo.sp_test > @ratecode char (10), > @referringdomain varchar (100), > @AffiliateName varchar (100) OUT > AS > DECLARE @affiliateID int, @ratecodeID int > SET @AffiliateName = '' > SELECT @affiliateID = A.AffiliateID, > @ratecodeID = B.RateCodeID, > @AffiliateName = A.AffiliateName > FROM dbo.tbl_A A > INNER JOIN dbo.tbl_B B ON A.AffiliateID = B.AffiliateID > WHERE (A.ReferringDomain = '@referringdomain') > AND (B.RateCode = '@ratecode') > AND (B.ExpirationDate >= getdate()) > IF @AffiliateName <> '' > INSERT INTO dbo.tbl_C (affiliateID, ratecodeID) > VALUES (@affiliateID,@ratecodeID) > ------------------------------------------------------------------ > > Regards, > Dave Bosky > > > > HTC Disclaimer: The information contained in this message may be privileged > and confidential and protected from disclosure. If the reader of this > message is not the intended recipient, or an employee or agent responsible > for delivering this message to the intended recipient, you are hereby > notified that any dissemination, distribution or copying of this > communication is strictly prohibited. If you have received this > communication in error, please notify us immediately by replying to the > message and deleting it from your computer. Thank you. > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
