locked
MIcrosoft Access SQL to SQL Server SQL (Stored Proc) RRS feed

  • Question

  • Hi

    I am writing Stored Procs for Access Queries, i am unsure how to write  [Product List TCS]![BillPayer] in SQl Server i have posted both the Access SQL and the Server SQL, is this correct? the Product LIST TCS is Product List T in SQL (Link Tables)

    MS ACCESS SQL QUERY

    UPDATE InvoiceData

    INNER JOIN [Product List TCS] ON (InvoiceData.CustomerRef = [Product List TCS].[Cust Ref])

    AND (InvoiceData.Product = [Product List TCS].Product)

    SET InvoiceData.BillPayer = [Product List TCS]![BillPayer]

    WHERE (((InvoiceData.BillPayer) Is Null) AND (([Product List TCS].InvoiceLive)="yes"));



    Stored Proc for Update Query

    UPDATE    InvoiceData

    SET              BillPayer = N'[Product List T]![BillPayer]'

    FROM         InvoiceData INNER JOIN
                          [Product List T] ON InvoiceData.CustomerRef = [Product List T].[Cust Ref]

    AND InvoiceData.Product = [Product List T].Product

    WHERE     (InvoiceData.BillPayer IS NULL) AND ([Product List T].InvoiceLive = N'yes')

    thanks

    Wednesday, June 20, 2012 8:59 AM

Answers

  • If it's in the other table in that query, it would simply be:

      SET BillPayer = T.BillPayer

    using the aliases I introduced for imporoved legibility.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by shabbs Wednesday, June 20, 2012 10:32 AM
    Wednesday, June 20, 2012 10:15 AM

All replies

  • I don't know the business rules etc, but I doubt that this is correct. Assuming that the column BillPayer holds a name, the T-SQL codes sets this name to the string "[Product List T]![BillPayer]" with brackets, exclamations marks and all. Quite an usual name.

    I believe the ! is some references in Access to input fields or somesuch. (I don't know Access.)

    And here is an important lesson. When you are in Access and use the Jet engine, you have all in the one and same process and everything can references anything else.

    But when you work with SQL Server, you are in a different world. SQL Server runs as a separate process, often on a separate computer. SQL Server cannot access input fields in the Access form. The correct way to write this UPDATE is:

    UPDATE    InvoiceData
    SET       BillPayer = @billpayer
    FROM      InvoiceData ID
    JOIN      [Product List T] T ON ID.CustomerRef = T.[Cust Ref]
                                AND ID.Product = T.Product
    WHERE     ID.BillPayer IS NULL
      AND     T.InvoiceLive = N'yes'

    @billpayer would be a parameter to your stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 20, 2012 9:14 AM
  • Thanks for that, i understand where you're comming from,

    The BillPayer field is going to be picked up from another table which already has the BillPayer information in, would it not be

    SET BillPayer= @ [Product List T].[ BillPayer]

    • Marked as answer by shabbs Wednesday, June 20, 2012 10:31 AM
    • Unmarked as answer by shabbs Wednesday, June 20, 2012 10:31 AM
    Wednesday, June 20, 2012 9:27 AM
  • If it's in the other table in that query, it would simply be:

      SET BillPayer = T.BillPayer

    using the aliases I introduced for imporoved legibility.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by shabbs Wednesday, June 20, 2012 10:32 AM
    Wednesday, June 20, 2012 10:15 AM
  • Much appreciated
    Wednesday, June 20, 2012 10:31 AM