Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: Building Dynamic Sql in Stored Proc issue

    Hi all,

    I'm gonna need some help with this one.

    I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:

    SELECT N_Client.Prefix,
    IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
    dbo.N_CLIENT.TITLE,
    dbo.N_COMPANY.COMPANY_NAME,
    dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
    dbo.N_DIVISION.DIVISION_NAME,
    dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
    dbo.N_CLIENT.DIRECT_PHONE,
    dbo.N_CLIENT.EMAIL,
    dbo.N_CLIENT.TIER_ID,
    (SELECT COUNT(Client_ID)
    FROM N_Alumni
    WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
    (SELECT COUNT(Client_ID)
    FROM N_XREF_Client_Activity
    WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
    (SELECT BAH_EMP_NID
    FROM N_XREF_Client_Activity
    WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
    (SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
    FROM N_Vw_Client_BAH_Contact
    WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
    dbo.N_CLIENT.SURNAME AS Client_Surname,
    dbo.N_CLIENT.FIRST_NAME,
    dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
    dbo.N_CLIENT.COMPANY_ID,
    dbo.N_CLIENT.DIVISION_ID,
    dbo.N_CLIENT.BUSINESS_UNIT_ID,
    dbo.N_COMPANY.GROUP_ID,
    dbo.N_CLIENT.COUNTRY,
    dbo.N_GROUP.GROUP_NAME,
    dbo.N_CLIENT.CLIENT_ID,
    (SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
    FROM N_Vw_Client_BAH_Contact
    WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
    (SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
    FROM N_Vw_Client_BAH_Contact
    WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
    (SELECT NMT_Practice_Code
    FROM N_Vw_Client_BAH_Contact
    WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
    (SELECT NMT_Practice_Name
    FROM N_Vw_Client_BAH_Contact
    WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
    #returnTable.AddlFullName,
    #returnTable.AddlEMPNID,
    #returnTable.FunctionID as Function_ID,
    #returnTable.FunctionName as Function_Name,
    (SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
    FROM N_Vw_Client_BAH_Contact
    WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
    (SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
    FROM N_Vw_Client_BAH_Contact
    WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
    ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
    dbo.N_COMPANY.TARGET_COMPANY_FLAG,
    dbo.N_COMPANY.INDUSTRY_ID,
    N_Client.Address1,
    N_Client.Address2,
    N_Client.Address3,
    N_Client.Address4,
    N_Client.Address5,
    N_Client.City,
    N_Client.State,
    N_Client.Postal_Code,
    N_Client.Country,
    N_Client.Region,
    N_Client.Office_Code,
    N_Client.Broderick_Target_Flag
    FROM dbo.N_CLIENT
    INNER JOIN
    dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
    LEFT OUTER JOIN
    dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
    LEFT OUTER JOIN
    dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
    LEFT OUTER JOIN
    dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
    LEFT OUTER JOIN
    #returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
    LEFT OUTER JOIN
    dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
    ORDER BY N_Client.client_id
    Where upper(title) like '%parameter_value%'
    and company_id = 'parameter_value'
    and Nmt_practice_code = 'parameter_value'
    ...............and so on

    What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
    title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.

    So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can store your parameters into a 15-column temporary table and then join the main query with it.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Alternativley....use and exec statemnt ( not for the weak of stomach )

    declare @var_1 varch(10), @var_2 varchar (10)
    set @var_1 = 'value_1'
    set @var_2 = 'value_2'

    exec( ' select * from table where x = ' +@var_1+ ' and y = ' +@var_2+ '')

    so :

    exec('..................
    Where upper(title) like %'+@parameter_value'%
    and company_id = '@parameter_value'
    and Nmt_practice_code = '@parameter_value'')

    You effectively have to enclose the whole statement in an exec statement.
    Its messy this way, and you will take a while to debig to get it right.

    rdjabarov has a good idea......its more elegant than my approach.....

Posting Permissions

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