Professional Web Applications Themes

Advanced Stored procedure - Microsoft SQL / MS SQL Server

WHERE ((actFreeSearch IS NULL AND OI.OffInIsDisabled=1) OR (CO.CoName= ActFreeSearch)) "Eric Bracke" <bds.ericskynet.be> wrote in message news:ef$jJ7XRDHA.1552TK2MSFTNGP10.phx.gbl... Hi there, Is it possible to create a SP with a 'adaptive' WHERE clausule ? ex (see the WHERE part (this doesn't works!!)): CREATE PROCEDURE dbo.BTsp_GetOffersIn_CONSULT ( ActFreeSearch nvarchar(50)=null ) AS SELECT OI.OfferInID, OI.OffInModelID, OI.OffInBasePrice, OI.OffInCurrID, OI.OffInQuant, OI.OffInCompID, CO.CoName, OI.OffInDuty, OI.OffInFTID, OI.OffInCity, OI.OffInDate, OI.OffInRemarks, OI.OffINDiscount, OI.OffInBManCommPerc, MOB.MoBrand FROM dbo.BTOffersIn OI INNER JOIN dbo.BTModels MO ON OI.OffInModelID = MO.ModelID INNER JOIN dbo.BTModelBrands MOB ON MO.MoBrandID = MOB.ModelBrandID INNER JOIN dbo.BTCompanies CO ON OI.OffInCompID = CO.CompID INNER JOIN dbo.BTCurrencies CU ON OI.OffInCurrID = CU.CurrID INNER JOIN ...

  1. #1

    Default Re: Advanced Stored procedure

    WHERE ((actFreeSearch IS NULL AND OI.OffInIsDisabled=1)
    OR (CO.CoName= ActFreeSearch))






    "Eric Bracke" <bds.ericskynet.be> wrote in message
    news:ef$jJ7XRDHA.1552TK2MSFTNGP10.phx.gbl...
    Hi there,

    Is it possible to create a SP with a 'adaptive' WHERE clausule ?

    ex (see the WHERE part (this doesn't works!!)):

    CREATE PROCEDURE dbo.BTsp_GetOffersIn_CONSULT
    (
    ActFreeSearch nvarchar(50)=null
    )

    AS



    SELECT
    OI.OfferInID,
    OI.OffInModelID,
    OI.OffInBasePrice,
    OI.OffInCurrID,
    OI.OffInQuant,
    OI.OffInCompID,
    CO.CoName,
    OI.OffInDuty,
    OI.OffInFTID,
    OI.OffInCity,
    OI.OffInDate,
    OI.OffInRemarks,
    OI.OffINDiscount,
    OI.OffInBManCommPerc,
    MOB.MoBrand



    FROM
    dbo.BTOffersIn OI
    INNER JOIN
    dbo.BTModels MO ON OI.OffInModelID = MO.ModelID
    INNER JOIN
    dbo.BTModelBrands MOB ON MO.MoBrandID = MOB.ModelBrandID
    INNER JOIN
    dbo.BTCompanies CO ON OI.OffInCompID = CO.CompID
    INNER JOIN
    dbo.BTCurrencies CU ON OI.OffInCurrID = CU.CurrID
    INNER JOIN
    dbo.BTFreightTypes FT ON OI.OffInFTID = FT.FreightTypeID

    WHERE

    CASE ActFreeSearch
    WHEN ''
    THEN
    (OI.OffInIsDisabled = 1)
    ELSE
    (CO.CoName= ActFreeSearch)
    END

    ORDER BY
    OI.OffInModelID,
    OI.OffInNetPrice / CU.CuBuyRate


    Thanks in advance,

    Eric



    Aaron Bertrand - MVP Guest

  2. #2

    Default Advanced Stored procedure

    the following incorporates dynamic SQL should work fine
    for you.

    Jordan

    CREATE PROCEDURE dbo.BTsp_GetOffersIn_CONSULT

    ActFreeSearch nvarchar(500)

    AS

    DECLARE sql nvarchar(4000)

    SET sql = N'

    SELECT
    OI.OfferInID,
    OI.OffInModelID,
    OI.OffInBasePrice,
    OI.OffInCurrID,
    OI.OffInQuant,
    OI.OffInCompID,
    CO.CoName,
    OI.OffInDuty,
    OI.OffInFTID,
    OI.OffInCity,
    OI.OffInDate,
    OI.OffInRemarks,
    OI.OffINDiscount,
    OI.OffInBManCommPerc,
    MOB.MoBrand

    FROM
    dbo.BTOffersIn OI
    INNER JOIN
    dbo.BTModels MO ON OI.OffInModelID = MO.ModelID
    INNER JOIN
    dbo.BTModelBrands MOB ON MO.MoBrandID = MOB.ModelBrandID
    INNER JOIN
    dbo.BTCompanies CO ON OI.OffInCompID = CO.CompID
    INNER JOIN
    dbo.BTCurrencies CU ON OI.OffInCurrID = CU.CurrID
    INNER JOIN
    dbo.BTFreightTypes FT ON OI.OffInFTID = FT.FreightTypeID

    WHERE

    CASE '+ ActFreeSearch + N'
    WHEN ''''
    THEN
    (OI.OffInIsDisabled = 1)
    ELSE
    (CO.CoName= ActFreeSearch)
    END

    ORDER BY
    OI.OffInModelID,
    OI.OffInNetPrice / CU.CuBuyRate'

    exec sp_executesql sql,
    N'ActFreeSearch', ActFreeSEarch
    >-----Original Message-----
    >Hi there,
    >
    >Is it possible to create a SP with a 'adaptive' WHERE
    clausule ?
    >
    >ex (see the WHERE part (this doesn't works!!)):
    >
    >CREATE PROCEDURE dbo.BTsp_GetOffersIn_CONSULT
    > (
    > ActFreeSearch nvarchar(50)=null
    > )
    >
    >AS
    >
    >
    >
    > SELECT
    > OI.OfferInID,
    > OI.OffInModelID,
    > OI.OffInBasePrice,
    > OI.OffInCurrID,
    > OI.OffInQuant,
    > OI.OffInCompID,
    > CO.CoName,
    > OI.OffInDuty,
    > OI.OffInFTID,
    > OI.OffInCity,
    > OI.OffInDate,
    > OI.OffInRemarks,
    > OI.OffINDiscount,
    > OI.OffInBManCommPerc,
    > MOB.MoBrand
    >
    >
    >
    > FROM
    > dbo.BTOffersIn OI
    > INNER JOIN
    > dbo.BTModels MO ON OI.OffInModelID = MO.ModelID
    > INNER JOIN
    > dbo.BTModelBrands MOB ON MO.MoBrandID =
    MOB.ModelBrandID
    > INNER JOIN
    > dbo.BTCompanies CO ON OI.OffInCompID = CO.CompID
    > INNER JOIN
    > dbo.BTCurrencies CU ON OI.OffInCurrID = CU.CurrID
    > INNER JOIN
    > dbo.BTFreightTypes FT ON OI.OffInFTID =
    FT.FreightTypeID
    >
    > WHERE
    >
    > CASE ActFreeSearch
    > WHEN ''
    > THEN
    > (OI.OffInIsDisabled = 1)
    > ELSE
    > (CO.CoName= ActFreeSearch)
    > END
    >
    > ORDER BY
    > OI.OffInModelID,
    > OI.OffInNetPrice / CU.CuBuyRate
    >
    >
    >Thanks in advance,
    >
    >Eric
    >
    >
    JMNUSS Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  3. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 PM
  4. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  5. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139