Arthur Fuller
fuller.artful at gmail.com
Tue Apr 15 14:53:48 CDT 2014
David, That appears to be an elegant solution. Since your variables use the @ prefix, I assume this code is clipped from a stored procedure, which should give you great performance despite the nested Select. Should performance become an issue, you might gain some by turning the nested Select into a table variable. But I wouldn't bother if performance is not a problem. On Tue, Apr 15, 2014 at 2:51 PM, David Emerson <newsgrps at dalyn.co.nz> wrote: > Arthur, > > In my case the form had a couple of filter fields used to filter the > recordset used for the form. I didn't want a form/subform setup because > the > filters would have been for different main tables. Here is the solution I > came up with: > > SELECT dbo.tlkpFunderServiceCodeRate.* > FROM dbo.tlkpFunderServiceCodeRate > WHERE dbo.tlkpFunderServiceCodeRate.FunderServiceCodeIDNo IN > (SELECT dbo.tlkpFunderServiceCode.FunderServiceCodeID FROM > dbo.tlkpFunderServiceCode > WHERE (dbo.tlkpFunderServiceCode.FunderIDNo = @FunderIDNo > OR > @FunderIDNo = 0) AND > (dbo.tlkpFunderServiceCode.FunderServiceCodeID = > @FunderServiceCodeIDNo OR @FunderServiceCodeIDNo = 0)) > ORDER BY dbo.tlkpFunderServiceCodeRate.FunderServCodeRateStartDate > > The rates link back to a code which links back to a funder. The two > filters > mean I could select all rates, all rates for a funder, or all rates for a > code. > > Regards > > David Emerson > Dalyn Software Ltd > Wellington, New Zealand > > Regards > > David > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur > Fuller > Sent: Tuesday, 15 April 2014 10:50 p.m. > To: Discussion concerning MS SQL Server > Subject: Re: [dba-SQLServer] Adding Records to Joined Tables > > David, > > I'm guessing that you want to do this with code rather than via a form, > since the latter makes it so easy -- just your standard form+subform. Is > that correct? If so, I still don't quite understand why you want to use a > view. Your code will need to determine the parent PK first, then use a > standard INSERT statement, passing the parent PK in, either by calling a > stored procedure that accepts a parameter for the parent PK, or by building > the INSERT statement in code and then executing. My preference would be the > stored procedure. > > Please clarify why you're using a view to accomplish this. > > Arthur > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- Arthur