Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Calling a query with parameters

    Hi! I need to call a query query1 which accepts a parameter par1 from a query query2 in SQL. Is this possible? This query returns a scalar, and I need to put this in a iif like:

    Code:
    iif(query1(par1)=0,something,something_else)
    But this is not working, of course. I read in the documentation, and I think what I need is the EXECUTE SQL statement, which accepts parameters separated by commas, so I tried:

    Code:
    iif(EXECUTE query1 par1,something,something_else)
    but this is not correct either. Is there anybody who knows what I'm doing wrong?
    Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I have yet to identify what you are doing right.

    Starting at the beginning.... what exactly are you trying to accomplish?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Sep 2009
    Posts
    16
    Well, I know none of the lines I reported in the first message work....

    I just want to call a query in SQL, let's say query1, from inside another query, let's say query2, passing a parameter to query1. I need to do this often. If I call query1 from inside query2 without giving the parameter it requires, Access asks me to insert the parameter. But I would like to pass this parameter to query1 from inside query2 directly. Is this possible?

    Like I wanted to call a function which requires a parameter from inside another function in C.

    Thanks for your answer!
    Last edited by Luc484; 10-06-09 at 22:02.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Contrarily to C, SQL is not a procedural language. Moreover Access can only use a subset of the SQL language. If you need to use the result of a query inside another one why don't you include it as a subquery?

    Another soution would consist in using a static function or a class to transmit the value Query1 yields to Query2 but this is beyond SQL. Ex:

    Public Function Query1() As Variant





    Also keep in mind that the Execute method does not return anything (no recordset, no scalar value) and can only be used with action queries (see Access documentation).
    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops, something is missing!

    Code:
    Public Function Query1() As Variant
    
        Dim rst as DAO.Recordset
        
        Set rst = CurrentDB.OpenRecordset("Query1", DBOpenSnapshot, DBSeeChanges)
        Query1 = rst!<Something>
        rst.Close
        Set rst = Nothing
    
    End Function
    You can then use Query1() in Query2
    Have a nice day!

  6. #6
    Join Date
    Sep 2009
    Posts
    16
    Indeed that's what I did to solve till now: a subquery. But, anyway... do this apply even to stored procedures?
    Thank you very much for your help!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally speaking a subquery can be written in a form such as:
    Code:
    SELECT * FROM Table1 WHERE Table1.Field1 IN (SELECT TOP 10 [FieldX] FROM Table2)
    If you use stored procedures, meaning that you're working with a true SQL engine (SQL Server, MySQL, Oracle etc.), there are fewer limitations in what you can do, as you can declare cursors and variables that can catch the product of a SQL statement and pass it to another one etc.

    Code:
       - - - - - 
        DECLARE @PK
        DECLARE c CURSOR FOR
            SELECT SysID FROM [Containers]
            WHERE SysID = @SysID
        OPEN c
        FETCH NEXT FROM c INTO @PK
        Close c
        DEALLOCATE c
        - - - - -
    However, a subquery is a better solution and cursors should be avoided if possible, due to their poor performances (this is not entirely true for Oracle but is a base rule for MS SQL Server).
    Have a nice day!

  8. #8
    Join Date
    Sep 2009
    Posts
    16
    Maybe Access doesn't use stored procedures, even if I define them, in fact, it translates the PROCEDURE statement with the PARAMETERS statement. Anyway, I'm able to use the results of a query in another query, for instance (call this query2):
    Code:
    SELECT COUNT(*)
    FROM query1;
    given query1 is defined, for instance as:
    Code:
    SELECT *
    FROM table1
    WHERE table1.field1="value1";
    This works correctly. My problem is when I have query1 defined like:
    Code:
    PARAMETERS param1;
    SELECT *
    FROM table1
    WHERE table1.field1=param1;
    In this case, if I run it, Access asks me for param1 (this is obvious). And if I use query2, which calls query1, it asks me correctly to insert param1, as query1 requires it.
    What I would like is to automatically pass param1 to query1 when executing query2. It seems strange to me this is not possible...

    Anyway, the solution of the subquery is good, but if it was possible I would prefer to reuse other queries.
    Thanks again for all your answers!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know, the only method to pass a value to a parameter of a query is by the use of VBA, like this:

    1) Query1 is defined as:
    Code:
    PARAMETERS Param1 Text ( 255 );
    DELETE *
    FROM dbo_CF_DATA
    WHERE dbo_CF_DATA.NUMERO=Param1;
    2) In VBA:
    Code:
    Function TestQuery1()
    
        Dim qdf As QueryDef
        Dim Param1 As String
        
        Param1 = "15252-A"
        Set qdf = CurrentDb.QueryDefs!Query1
        qdf.Parameters("Param1").Value = Param1
        qdf.Execute dbSeeChanges
        Set qdf = Nothing
    
    End Function
    Which is senseless as you could as well use:
    Code:
    Function TestQuery1()
    
        Dim Param1 As String
        Dim strSQL As String
        
        Param1 = "15252-A"   
        strSQL = "DELETE * FROM dbo_CF_DATA WHERE dbo_CF_DATA.NUMERO = '" & Param1 & "'"
        CurrentDb.Execute strSQL, dbSeeChanges
    
    End Function
    Have a nice day!

  10. #10
    Join Date
    Sep 2009
    Posts
    16
    Thank you very much for your help. If possible, I would like to use SQL only, in case I want to use something different from Access, which is only the beginning. I will try other ways then, thanks anyway!

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I still think that if we knew what business goal you are trying to accomplish, there will be a much easier answer, but it might not be pure SQL.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •