|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Problem With Stored ProcedureHowevber, if I execute the Select portion in QA and plug in the value for @eventlist and set @gemus to 1, it returns the correct 2 email addresses but when I Execute the SP from QA and use those values I get nothing? ====================================== Alter Procedure dbo.GetJudgeEmails @eventlist varchar(70), @gemus bit = 0, @gevis bit = 0 AS -- Create a temporary table CREATE TABLE #Tmp1 ( email varchar(65) ) If @gemus = 1 Begin Insert Into #Tmp1 Select N1.email FROM Names N1 Inner Join Judges As J1 ON J1.NameID = N1.NameID Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID Where E1.EventID IN (@eventlist) End Select Distinct * From #Tmp1 Drop Table #Tmp1 Hi
The problem is that you can not use variable @eventlist with the IN clause. The IN clause tries to match everything to a singe value of E1.EventID. If you want to do that, you need to use dynamic SQL. Why are you using a Temp Table? You can do the SELECT DISTINCT inline in your select. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... > When I execute the stored procedue shown below it never returns any > values? Howevber, if I execute the Select portion in QA and plug in the > value for @eventlist and set @gemus to 1, it returns the correct 2 email > addresses but when I Execute the SP from QA and use those values I get > nothing? > > > ====================================== > Alter Procedure dbo.GetJudgeEmails > > @eventlist varchar(70), > @gemus bit = 0, > @gevis bit = 0 > > AS > -- Create a temporary table > CREATE TABLE #Tmp1 ( > email varchar(65) > ) > > If @gemus = 1 > Begin > Insert Into #Tmp1 Select N1.email FROM Names N1 > Inner Join Judges As J1 ON J1.NameID = N1.NameID > Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID > Where E1.EventID IN (@eventlist) > End > > Select Distinct * From #Tmp1 > > Drop Table #Tmp1 > Mike;
Thanks for the quick and helpful response. The value of @eventlist is a string such as 'N1', 'N2', 'S1' and I want those cases where E1.EventID is one of those values? I guess I don't understand why that doesn't work but I am sure you are right and I'll try re-writing it as dynamic. As for using the temp table, the actual SP is quite a bit more complex with multiple conditions so I used the temp table to collect all the emails and then do a Select Distinct from there. Again, thanks for the help Wayne Show quote "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message news:eSoeaOwfFHA.1204@TK2MSFTNGP12.phx.gbl... > Hi > > The problem is that you can not use variable @eventlist with the IN > clause. The IN clause tries to match everything to a singe value of > E1.EventID. > > If you want to do that, you need to use dynamic SQL. > > Why are you using a Temp Table? You can do the SELECT DISTINCT inline in > your select. > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > IM: m***@epprecht.net > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message > news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... >> When I execute the stored procedue shown below it never returns any >> values? Howevber, if I execute the Select portion in QA and plug in the >> value for @eventlist and set @gemus to 1, it returns the correct 2 email >> addresses but when I Execute the SP from QA and use those values I get >> nothing? >> >> >> ====================================== >> Alter Procedure dbo.GetJudgeEmails >> >> @eventlist varchar(70), >> @gemus bit = 0, >> @gevis bit = 0 >> >> AS >> -- Create a temporary table >> CREATE TABLE #Tmp1 ( >> email varchar(65) >> ) >> >> If @gemus = 1 >> Begin >> Insert Into #Tmp1 Select N1.email FROM Names N1 >> Inner Join Judges As J1 ON J1.NameID = N1.NameID >> Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID >> Where E1.EventID IN (@eventlist) >> End >> >> Select Distinct * From #Tmp1 >> >> Drop Table #Tmp1 >> > > There is not much to understand really<g>. It simply does not allow a
variable for the IN clause and must be explicitly listed or used with a subselect. That is just the way the code is written and probably won't change anytime soon. Even though there are many who would love to see it change<g>. -- Show quoteAndrew J. Kelly SQL MVP "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message news:eo9g5WwfFHA.3944@TK2MSFTNGP10.phx.gbl... > Mike; > > Thanks for the quick and helpful response. The value of @eventlist is a > string such as 'N1', 'N2', 'S1' and I want those cases where E1.EventID is > one of those values? I guess I don't understand why that doesn't work but > I am sure you are right and I'll try re-writing it as dynamic. > > As for using the temp table, the actual SP is quite a bit more complex > with multiple conditions so I used the temp table to collect all the > emails and then do a Select Distinct from there. > > Again, thanks for the help > > Wayne > > "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message > news:eSoeaOwfFHA.1204@TK2MSFTNGP12.phx.gbl... >> Hi >> >> The problem is that you can not use variable @eventlist with the IN >> clause. The IN clause tries to match everything to a singe value of >> E1.EventID. >> >> If you want to do that, you need to use dynamic SQL. >> >> Why are you using a Temp Table? You can do the SELECT DISTINCT inline in >> your select. >> >> Regards >> -------------------------------- >> Mike Epprecht, Microsoft SQL Server MVP >> Zurich, Switzerland >> >> IM: m***@epprecht.net >> >> MVP Program: http://www.microsoft.com/mvp >> >> Blog: http://www.msmvps.com/epprecht/ >> >> "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message >> news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... >>> When I execute the stored procedue shown below it never returns any >>> values? Howevber, if I execute the Select portion in QA and plug in the >>> value for @eventlist and set @gemus to 1, it returns the correct 2 email >>> addresses but when I Execute the SP from QA and use those values I get >>> nothing? >>> >>> >>> ====================================== >>> Alter Procedure dbo.GetJudgeEmails >>> >>> @eventlist varchar(70), >>> @gemus bit = 0, >>> @gevis bit = 0 >>> >>> AS >>> -- Create a temporary table >>> CREATE TABLE #Tmp1 ( >>> email varchar(65) >>> ) >>> >>> If @gemus = 1 >>> Begin >>> Insert Into #Tmp1 Select N1.email FROM Names N1 >>> Inner Join Judges As J1 ON J1.NameID = N1.NameID >>> Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID >>> Where E1.EventID IN (@eventlist) >>> End >>> >>> Select Distinct * From #Tmp1 >>> >>> Drop Table #Tmp1 >>> >> >> > > The fn_split() function on Listing 1 of
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt could be the job for this: select * from A where A.Id in (select value from dbo.fn_Split ('3,4,5', ',')) Show quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:O8uEp6wfFHA.3656@TK2MSFTNGP09.phx.gbl... > There is not much to understand really<g>. It simply does not allow a > variable for the IN clause and must be explicitly listed or used with a > subselect. That is just the way the code is written and probably won't > change anytime soon. Even though there are many who would love to see it > change<g>. > > -- > Andrew J. Kelly SQL MVP > > > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message > news:eo9g5WwfFHA.3944@TK2MSFTNGP10.phx.gbl... >> Mike; >> >> Thanks for the quick and helpful response. The value of @eventlist is a >> string such as 'N1', 'N2', 'S1' and I want those cases where E1.EventID >> is one of those values? I guess I don't understand why that doesn't work >> but I am sure you are right and I'll try re-writing it as dynamic. >> >> As for using the temp table, the actual SP is quite a bit more complex >> with multiple conditions so I used the temp table to collect all the >> emails and then do a Select Distinct from there. >> >> Again, thanks for the help >> >> Wayne >> >> "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message >> news:eSoeaOwfFHA.1204@TK2MSFTNGP12.phx.gbl... >>> Hi >>> >>> The problem is that you can not use variable @eventlist with the IN >>> clause. The IN clause tries to match everything to a singe value of >>> E1.EventID. >>> >>> If you want to do that, you need to use dynamic SQL. >>> >>> Why are you using a Temp Table? You can do the SELECT DISTINCT inline in >>> your select. >>> >>> Regards >>> -------------------------------- >>> Mike Epprecht, Microsoft SQL Server MVP >>> Zurich, Switzerland >>> >>> IM: m***@epprecht.net >>> >>> MVP Program: http://www.microsoft.com/mvp >>> >>> Blog: http://www.msmvps.com/epprecht/ >>> >>> "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message >>> news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... >>>> When I execute the stored procedue shown below it never returns any >>>> values? Howevber, if I execute the Select portion in QA and plug in the >>>> value for @eventlist and set @gemus to 1, it returns the correct 2 >>>> email addresses but when I Execute the SP from QA and use those values >>>> I get nothing? >>>> >>>> >>>> ====================================== >>>> Alter Procedure dbo.GetJudgeEmails >>>> >>>> @eventlist varchar(70), >>>> @gemus bit = 0, >>>> @gevis bit = 0 >>>> >>>> AS >>>> -- Create a temporary table >>>> CREATE TABLE #Tmp1 ( >>>> email varchar(65) >>>> ) >>>> >>>> If @gemus = 1 >>>> Begin >>>> Insert Into #Tmp1 Select N1.email FROM Names N1 >>>> Inner Join Judges As J1 ON J1.NameID = N1.NameID >>>> Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID >>>> Where E1.EventID IN (@eventlist) >>>> End >>>> >>>> Select Distinct * From #Tmp1 >>>> >>>> Drop Table #Tmp1 >>>> >>> >>> >> >> > > >> That is just the way the code is written and probably won't change anytime soon. Even though there are many who would love to see it change << It would require destroying the standards, and converting every SQLproduct over to an interpreter and not a compiler. However, if SQL Server would implement more of the old SQL-92 syntax, you could write row and table constructors like this in an INSERT INTO statement in the front end. INSERT INTO Events (event_id) VALUES (1), (2), (3), (42); and in the code use .WHERE event_id IN (SELECT event_id FROM Events WHERE event_id IS NOT NULL) No need to change to world, just implement a minor part of a 10+ year old standard. > As for using the temp table, the actual SP is quite a bit more complex If you're using SQL Server 2000, you can use a table variable instead of awith > multiple conditions so I used the temp table to collect all the emails and > then do a Select Distinct from there. temp table. One benefit of using a table variable is that its visibility is limited to the stored procedure, whereas a temp table is seen by all called procedures, which can cause conflicts. Show quote "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message news:eo9g5WwfFHA.3944@TK2MSFTNGP10.phx.gbl... > Mike; > > Thanks for the quick and helpful response. The value of @eventlist is a > string such as 'N1', 'N2', 'S1' and I want those cases where E1.EventID is > one of those values? I guess I don't understand why that doesn't work but I > am sure you are right and I'll try re-writing it as dynamic. > > As for using the temp table, the actual SP is quite a bit more complex with > multiple conditions so I used the temp table to collect all the emails and > then do a Select Distinct from there. > > Again, thanks for the help > > Wayne > > "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message > news:eSoeaOwfFHA.1204@TK2MSFTNGP12.phx.gbl... > > Hi > > > > The problem is that you can not use variable @eventlist with the IN > > clause. The IN clause tries to match everything to a singe value of > > E1.EventID. > > > > If you want to do that, you need to use dynamic SQL. > > > > Why are you using a Temp Table? You can do the SELECT DISTINCT inline in > > your select. > > > > Regards > > -------------------------------- > > Mike Epprecht, Microsoft SQL Server MVP > > Zurich, Switzerland > > > > IM: m***@epprecht.net > > > > MVP Program: http://www.microsoft.com/mvp > > > > Blog: http://www.msmvps.com/epprecht/ > > > > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message > > news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... > >> When I execute the stored procedue shown below it never returns any > >> values? Howevber, if I execute the Select portion in QA and plug in the > >> value for @eventlist and set @gemus to 1, it returns the correct 2 > >> addresses but when I Execute the SP from QA and use those values I get > >> nothing? > >> > >> > >> ====================================== > >> Alter Procedure dbo.GetJudgeEmails > >> > >> @eventlist varchar(70), > >> @gemus bit = 0, > >> @gevis bit = 0 > >> > >> AS > >> -- Create a temporary table > >> CREATE TABLE #Tmp1 ( > >> email varchar(65) > >> ) > >> > >> If @gemus = 1 > >> Begin > >> Insert Into #Tmp1 Select N1.email FROM Names N1 > >> Inner Join Judges As J1 ON J1.NameID = N1.NameID > >> Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID > >> Where E1.EventID IN (@eventlist) > >> End > >> > >> Select Distinct * From #Tmp1 > >> > >> Drop Table #Tmp1 > >> > > > > > > Brian,
A temp table created in a stored procedure is only seen by that sp or ones that it calls directly. Other than that the scope of the table variable is essentially the same as a local temp table. One of the biggest advantages of table variables is that they will not cause recompiles that are due to deferred name resolution or statistics. -- Show quoteAndrew J. Kelly SQL MVP "Brian Selzer" <br***@selzer-software.com> wrote in message news:%23T7%23Kj5fFHA.3124@TK2MSFTNGP12.phx.gbl... >> As for using the temp table, the actual SP is quite a bit more complex > with >> multiple conditions so I used the temp table to collect all the emails >> and >> then do a Select Distinct from there. > > If you're using SQL Server 2000, you can use a table variable instead of a > temp table. One benefit of using a table variable is that its visibility > is > limited to the stored procedure, whereas a temp table is seen by all > called > procedures, which can cause conflicts. > > > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message > news:eo9g5WwfFHA.3944@TK2MSFTNGP10.phx.gbl... >> Mike; >> >> Thanks for the quick and helpful response. The value of @eventlist is a >> string such as 'N1', 'N2', 'S1' and I want those cases where E1.EventID >> is >> one of those values? I guess I don't understand why that doesn't work but > I >> am sure you are right and I'll try re-writing it as dynamic. >> >> As for using the temp table, the actual SP is quite a bit more complex > with >> multiple conditions so I used the temp table to collect all the emails >> and >> then do a Select Distinct from there. >> >> Again, thanks for the help >> >> Wayne >> >> "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message >> news:eSoeaOwfFHA.1204@TK2MSFTNGP12.phx.gbl... >> > Hi >> > >> > The problem is that you can not use variable @eventlist with the IN >> > clause. The IN clause tries to match everything to a singe value of >> > E1.EventID. >> > >> > If you want to do that, you need to use dynamic SQL. >> > >> > Why are you using a Temp Table? You can do the SELECT DISTINCT inline >> > in >> > your select. >> > >> > Regards >> > -------------------------------- >> > Mike Epprecht, Microsoft SQL Server MVP >> > Zurich, Switzerland >> > >> > IM: m***@epprecht.net >> > >> > MVP Program: http://www.microsoft.com/mvp >> > >> > Blog: http://www.msmvps.com/epprecht/ >> > >> > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message >> > news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... >> >> When I execute the stored procedue shown below it never returns any >> >> values? Howevber, if I execute the Select portion in QA and plug in >> >> the >> >> value for @eventlist and set @gemus to 1, it returns the correct 2 >> >> addresses but when I Execute the SP from QA and use those values I get >> >> nothing? >> >> >> >> >> >> ====================================== >> >> Alter Procedure dbo.GetJudgeEmails >> >> >> >> @eventlist varchar(70), >> >> @gemus bit = 0, >> >> @gevis bit = 0 >> >> >> >> AS >> >> -- Create a temporary table >> >> CREATE TABLE #Tmp1 ( >> >> email varchar(65) >> >> ) >> >> >> >> If @gemus = 1 >> >> Begin >> >> Insert Into #Tmp1 Select N1.email FROM Names N1 >> >> Inner Join Judges As J1 ON J1.NameID = N1.NameID >> >> Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID >> >> Where E1.EventID IN (@eventlist) >> >> End >> >> >> >> Select Distinct * From #Tmp1 >> >> >> >> Drop Table #Tmp1 >> >> >> > >> > >> >> > > > A temp table created in a stored procedure is only seen by that sp or ones It is also seen by any stored procedures called indirectly as well, that is,> that it calls directly. by any procedures that are called by a stored procedure or any procedure called by a procedure called by the procedure that created the temp table, and so on. I was unaware that they also reduced recompiles. I'll have to look into that. Show quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:#5yvbw8fFHA.3788@tk2msftngp13.phx.gbl... > Brian, > > A temp table created in a stored procedure is only seen by that sp or ones > that it calls directly. Other than that the scope of the table variable is > essentially the same as a local temp table. One of the biggest advantages > of table variables is that they will not cause recompiles that are due to > deferred name resolution or statistics. > > -- > Andrew J. Kelly SQL MVP > > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:%23T7%23Kj5fFHA.3124@TK2MSFTNGP12.phx.gbl... > >> As for using the temp table, the actual SP is quite a bit more complex > > with > >> multiple conditions so I used the temp table to collect all the emails > >> and > >> then do a Select Distinct from there. > > > > If you're using SQL Server 2000, you can use a table variable instead of a > > temp table. One benefit of using a table variable is that its visibility > > is > > limited to the stored procedure, whereas a temp table is seen by all > > called > > procedures, which can cause conflicts. > > > > > > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message > > news:eo9g5WwfFHA.3944@TK2MSFTNGP10.phx.gbl... > >> Mike; > >> > >> Thanks for the quick and helpful response. The value of @eventlist is a > >> string such as 'N1', 'N2', 'S1' and I want those cases where E1.EventID > >> is > >> one of those values? I guess I don't understand why that doesn't work but > > I > >> am sure you are right and I'll try re-writing it as dynamic. > >> > >> As for using the temp table, the actual SP is quite a bit more complex > > with > >> multiple conditions so I used the temp table to collect all the emails > >> and > >> then do a Select Distinct from there. > >> > >> Again, thanks for the help > >> > >> Wayne > >> > >> "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message > >> news:eSoeaOwfFHA.1204@TK2MSFTNGP12.phx.gbl... > >> > Hi > >> > > >> > The problem is that you can not use variable @eventlist with the IN > >> > clause. The IN clause tries to match everything to a singe value of > >> > E1.EventID. > >> > > >> > If you want to do that, you need to use dynamic SQL. > >> > > >> > Why are you using a Temp Table? You can do the SELECT DISTINCT inline > >> > in > >> > your select. > >> > > >> > Regards > >> > -------------------------------- > >> > Mike Epprecht, Microsoft SQL Server MVP > >> > Zurich, Switzerland > >> > > >> > IM: m***@epprecht.net > >> > > >> > MVP Program: http://www.microsoft.com/mvp > >> > > >> > Blog: http://www.msmvps.com/epprecht/ > >> > > >> > "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message > >> > news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... > >> >> When I execute the stored procedue shown below it never returns any > >> >> values? Howevber, if I execute the Select portion in QA and plug in > >> >> the > >> >> value for @eventlist and set @gemus to 1, it returns the correct 2 > >> >> addresses but when I Execute the SP from QA and use those values I get > >> >> nothing? > >> >> > >> >> > >> >> ====================================== > >> >> Alter Procedure dbo.GetJudgeEmails > >> >> > >> >> @eventlist varchar(70), > >> >> @gemus bit = 0, > >> >> @gevis bit = 0 > >> >> > >> >> AS > >> >> -- Create a temporary table > >> >> CREATE TABLE #Tmp1 ( > >> >> email varchar(65) > >> >> ) > >> >> > >> >> If @gemus = 1 > >> >> Begin > >> >> Insert Into #Tmp1 Select N1.email FROM Names N1 > >> >> Inner Join Judges As J1 ON J1.NameID = N1.NameID > >> >> Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID > >> >> Where E1.EventID IN (@eventlist) > >> >> End > >> >> > >> >> Select Distinct * From #Tmp1 > >> >> > >> >> Drop Table #Tmp1 > >> >> > >> > > >> > > >> > >> > > > > > > This code is pretty bad. You have used programming tricks from a host
of non-SQL languages and almost completely avoided code that lkooks like SQL at all 1) SQL is a compiled language, not an interpreter or script language as you seem to think. Have you ever worked with a compiled language before? 2) You have a table called "names", but names of what? Judges? Fish? Have you had a basic data modeling course or read a summary of ISO-11179? For example, the data element name "name_id" is absurd. An identifier is unique to an entity in the schema while a name is a nominal scale attribute which can occur in may places. Thus "customer_name" would make sense. Hey, why not string a ton of modifiers on a data element and have "name_id_value_type" like a string of adjective that have no noun to modify. I see that a gemus is really a judge_id, but that it changed its name when it moved to another table. 3) Why are you using assembly language bit flags in SQL? Why did you name a table "EventList"? -- a list is a physical data structure that does not exist in SQL. 4) Why do you allocate a needless temp table, as if you were still writing a COBOL program with scratch files? Build a table with one column of event ids and use it in a stored procedure like this, with foobar and floob changed to meaningful data element name components. CREATE PROCEDURE GetJudgeEmails () AS SELECT DISTINCT N1.email FROM FoobarNames AS N1, Judges AS J1, EventJudgeAssignments AS E1 WHERE J1.floob_name = N1.floob_name AND E1.judge_id = J1.judge_id AND E1.event_id IN (SELECT event_id FROM Events); --CELKO-- wrote:
Show quote > Build a table with one column of event ids and use it in a stored Please help me understand this point: why use IN (subquery) rather than> procedure like this, with foobar and floob changed to meaningful data > element name components. > > CREATE PROCEDURE GetJudgeEmails () > AS > SELECT DISTINCT N1.email > FROM FoobarNames AS N1, > Judges AS J1, > EventJudgeAssignments AS E1 > WHERE J1.floob_name = N1.floob_name > AND E1.judge_id = J1.judge_id > AND E1.event_id > IN (SELECT event_id FROM Events); a JOIN? Put another way, why not SELECT DISTINCT N1.email FROM FoobarNames AS N1, Judges AS J1, EventJudgeAssignments AS E1, Events AS E2 WHERE J1.floob_name = N1.floob_name AND E1.judge_id = J1.judge_id AND E1.event_id = E2.event_id Thank you. >> why use IN (subquery) rather than a JOIN? << Same-same. A good optimizer will do them both the same way. I justkept the original query structure for demonstration purposes. --CELKO-- wrote:
> >> why use IN (subquery) rather than a JOIN? << I thought so. Thanks for confirming.> > Same-same. A good optimizer will do them both the same way. I just > kept the original query structure for demonstration purposes. Thanks to all for the suggestions and information.
Wayne Show quote "Wayne Wengert" <wayneSKIPSPAM@wengert.org> wrote in message news:Og$KAGwfFHA.2152@TK2MSFTNGP14.phx.gbl... > When I execute the stored procedue shown below it never returns any > values? Howevber, if I execute the Select portion in QA and plug in the > value for @eventlist and set @gemus to 1, it returns the correct 2 email > addresses but when I Execute the SP from QA and use those values I get > nothing? > > > ====================================== > Alter Procedure dbo.GetJudgeEmails > > @eventlist varchar(70), > @gemus bit = 0, > @gevis bit = 0 > > AS > -- Create a temporary table > CREATE TABLE #Tmp1 ( > email varchar(65) > ) > > If @gemus = 1 > Begin > Insert Into #Tmp1 Select N1.email FROM Names N1 > Inner Join Judges As J1 ON J1.NameID = N1.NameID > Inner Join EventJudgeAssignments AS E1 ON E1.GEMus = J1.JudgeID > Where E1.EventID IN (@eventlist) > End > > Select Distinct * From #Tmp1 > > Drop Table #Tmp1 >  
Other interesting topics
|
|||||||||||||||||||||||
