You say tomato I say Tomatoe.....its a matter for what you know and learn...they all work the same.
-----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED] Sent: 03 July 2003 17:10 To: SQL Subject: Re: Returning a value from a stored procedure. Thanks, Neil...but maybe I'm not being clear about what I'm confused about. :) It's the piece of code in the SELECT that's throwing me, not the WHERE clause. I mean, in the docs, there's a sample procedure that looks like this: SELECT 'Title Name' = title FROM titles yet this procedure used: SELECT @affiliateID = A.AffiliateID, @ratecodeID = B.RateCodeID, @AffiliateName = A.AffiliateName Are you saying this last SELECT can be written like this? SELECT a.affiliateid = @affiliateid, ... Actually, I think I'm being incredibly dense this morning. :) All this is doing is saying set this parameter equal to this value in the database, right? ~Dina ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, July 03, 2003 10:54 AM Subject: RE: Returning a value from a stored procedure. > 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 Get the mailserver that powers this list at http://www.coolfusion.com
