Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: access and dynamic sql

    Hi,

    To use a searchform with 15 searchfields (the may be null) in my .adp-file, I want to use dynamic sql (code below). Is this possible? How have I to use this code in access?

    Can some-one please help me?
    I'm working with access 2007 and microsoft sql server 2005 express

    Code:
    CREATE PROCEDURE search_orders_1
    @gegevens         bit = NULL,
    @getekend         bit = NULL,
    @goedgekeurd      bit = NULL,
    @productielijst   bit = NULL,
    @in_productie     bit = NULL,
    @klaar            bit = NULL,
    @geleverd         bit = NULL,
    @gefactureerd     bit = NULL,
    @dossiernr        int = NULL,
    @categorie        int = NULL,
    @klant            int = NULL,
    @tekenaar         int = NULL,
    @projectleider    int = NULL,
    @verm_bedrag      int = NULL,
    @levertermijn     int = NULL,
    @debug            bit = 0 AS
    
    DECLARE @sql       nvarchar(4000),
            @paramlist nvarchar(4000)
    
    SELECT @sql=  
       'SELECT d.dossiers_ID AS dossier, b.naam AS klant, d.werfnaam AS 
    werf,d.werfadresplaats AS plaats, s.onderdeel, s.hoeveelheid,
               s.eenheid, p.naam AS naam_tekenaar, p.voornaam AS 
    vrnaam_tekenaar,s.leverdatum AS levertermijn 
        FROM   dbo.TBL_PERSONEEL p 
        RIGHT OUTER JOIN dbo.TBL_DOSSIERS d ON p.personeels_ID = d.tekenaar_JUMA
        LEFT OUTER JOIN dbo.TBL_KLANT k ON d.klantnr = k.klantnummer
        LEFT OUTER JOIN dbo.TBL_BEDRIJF b ON k.bedrijf_ID = b.bedrijf_ID
        LEFT OUTER JOIN dbo.TBL_SUBDOSSIERS s ON d.dossiers_ID = s.dossiers_ID
        WHERE  1 = 1'
    
    IF @gegevens IS NOT NULL
       SELECT      @sql = @sql + ' AND s.gegevens = @xgegevens'
    
    IF @getekend IS NOT NULL
       SELECT      @sql = @sql + ' AND s.getekend = @xgetekend'
    
    IF @goedgekeurd IS NOT NULL
       SELECT      @sql = @sql + ' AND s.goedgekeurd = @xgoedgekeurd'
    
    IF @productielijst IS NOT NULL
       SELECT      @sql = @sql + ' AND s.productielijst = @xproductielijst'
    
    IF @in_productie IS NOT NULL
       SELECT      @sql = @sql + ' AND s.in_productie = @xin_productie'
    
    IF @klaar IS NOT NULL
       SELECT      @sql = @sql + ' AND s.klaar = @xklaar'
    
    IF @geleverd IS NOT NULL
       SELECT      @sql = @sql + ' AND s.geleverd = @xgeleverd'
    
    IF @gefactureerd IS NOT NULL
       SELECT      @sql = @sql + ' AND d.checkbox_facturatie = @xgefactureerd'
    
    IF @dossiernr IS NOT NULL
       SELECT      @sql = @sql + ' AND d.dossiers_ID = @xdossiernr'
    
    IF @categorie IS NOT NULL
       SELECT      @sql = @sql + ' AND s.productcategorie = @xcategorie'
    
    IF @klant IS NOT NULL
       SELECT      @sql = @sql + ' AND b.bedrijf_ID = @xklant'
    
    IF @tekenaar IS NOT NULL
       SELECT      @sql = @sql + ' AND d.tekenaar_JUMA = @xtekenaar'
    
    IF @projectleider IS NOT NULL
       SELECT      @sql = @sql + ' AND d.projectleider_JUMA = @xprojectleider'
    
    IF @verm_bedrag IS NOT NULL
       SELECT      @sql = @sql + ' AND s.vermoedelijk_bedrag > @xverm_bedrag'
    
    IF @levertermijn IS NOT NULL
       SELECT      @sql = @sql + ' AND s.leverdatum < CURRENT_DATE() + 
    @xlevertermijn'
    
    SELECT      @sql = @sql+ ' ORDER BY d.dossiers_ID, s.subdossiers_ID'
    
    IF @debug = 1
       PRINT @sql
    
    SELECT      @paramlist='@xgegevens        bit,
                            @xgetekend        bit, 
                            @xgoedgekeurd     bit, 
                            @xproductielijst  bit,
                            @xin_productie    bit, 
                            @xklaar           bit, 
                            @xgeleverd        bit, 
                            @xgefactureerd    bit,
                            @xdossiernr       int,
                            @xcategorie       int,
                            @xklant           int, 
                            @xtekenaar        int,
                            @xprojectleider   int,
                            @xverm_bedrag     int,
                            @xlevertermijn    int'
    
    EXEC sp_executesql @sql, @paramlist,
                       @gegevens, @getekend, @goedgekeurd, @productielijst, 
    @in_productie, @klaar, @geleverd, @gefactureerd,
                       @dossiernr, @categorie, @klant, @tekenaar, 
    @projectleider, @verm_bedrag, @levertermijn

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Moved to SQL Server as the Access part of this is really a red herring - it is a T-SQL problem.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - I may have made a mistake moving this. Do you simply want to know how to call a stored procedure from Access? The content of the stored procedure works fine and you do not need any help with that right?

Posting Permissions

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