Thanks, guys. (goes away to start playing with a stored procedure in Query Analyzer) :)
~Dina ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, July 03, 2003 11:24 AM Subject: RE: Returning a value from a stored procedure. > 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
