Professional Web Applications Themes

Dynamic SQL use of parameter/variable - Microsoft SQL / MS SQL Server

Hi, I have an INT parameter in a sproc. I check it's value and change it to NULL if it's zero so that I can use ISNULL in my select statement. This worked well until I needed to convert this whole mess to dynamic SQL. Now I'm confused as to how to deal with this parameter. (I'm using this for an ASP.Net page) My question is, how can I alter the value of my parameter in my IF statement and then use it later? It looks like this: ALTER Procedure spYieldGridDynamic ( prmPageSize SMALLINT, prmCurrentPage INT, prmFromDate DATETIME, prmToDate DATETIME, ...

  1. #1

    Default Dynamic SQL use of parameter/variable

    Hi,

    I have an INT parameter in a sproc. I check it's value and change it to
    NULL if it's zero so that I can use ISNULL in my select statement. This
    worked well until I needed to convert this whole mess to dynamic SQL.
    Now I'm confused as to how to deal with this parameter. (I'm using this
    for an ASP.Net page) My question is, how can I alter the value of my
    parameter in my IF statement and then use it later? It looks like this:

    ALTER Procedure spYieldGridDynamic
    (
    prmPageSize SMALLINT,
    prmCurrentPage INT,
    prmFromDate DATETIME,
    prmToDate DATETIME,
    prmBatch INT = NULL,
    prmMaterial VARCHAR(50)
    --prmField VARCHAR(50)=NULL,
    --prmOperator VARCHAR(4)=NULL,
    --prmCriterion VARCHAR(50)=NULL
    )
    AS
    DECLARE SQL NVARCHAR(3000)
    SELECT SQL='IF ' + CONVERT(VARCHAR,prmBatch) + ' =0 SET prmBatch =
    NULL CREATE TABLE #OmegaBatchTemp '
    SELECT SQL=SQL + '(IncreasingID INT Identity(1,1), Batch INT ) '
    SELECT SQL=SQL + 'INSERT INTO #OmegaBatchTemp (Batch) SELECT fldBatch
    '
    SELECT SQL=SQL + 'FROM tblOmegaBatches WHERE fldMaterial =
    ISNULL('''+ prmMaterial + ''', fldMaterial) '
    SELECT SQL=SQL + 'ORDER BY fldBatch DESC SELECT o.fldBatch AS
    "Batch", o.fldMaterial AS "Material", '
    SELECT SQL=SQL + 'o.fldStartTime AS "Date", ISNULL((SELECT
    SUM(s.fldQuantity) FROM tblOmegaScrap s WHERE s.fldBatch =
    o.fldBatch),0) AS "ScrapUnits", '
    SELECT SQL=SQL + 'ISNULL(fldPanelQuantity,0) AS "PanelsUnits",
    ISNULL(STR((CONVERT(REAL,o.fldPanelQuantity) - '
    SELECT SQL=SQL + 'ISNULL((SELECT SUM(s.fldQuantity) FROM
    tblOmegaScrap s WHERE s.fldBatch = o.fldBatch) ,0)) '
    SELECT SQL=SQL + '/ o.fldPanelQuantity * 100, 10,1),0) AS "Yield",
    ISNULL((CONVERT(REAL,o.fldPanelQuantity) * '
    SELECT SQL=SQL + '(SELECT (fldActualWidth * fldActualLength) FROM
    tblMaterials m WHERE m.fldMaterial = o.fldMaterial)/144), 0) AS
    "PanelsArea", '
    SELECT SQL=SQL + 'ISNULL ((SELECT SUM(s.fldQuantity) FROM
    tblOmegaScrap s WHERE s.fldBatch = o.fldBatch) ,0) * '
    SELECT SQL=SQL + '(SELECT (fldActualWidth * fldActualLength) FROM
    tblMaterials m WHERE m.fldMaterial = o.fldMaterial)/144 AS "ScrapArea" '
    SELECT SQL=SQL + 'FROM tblOmegaBatches o INNER JOIN #OmegaBatchTemp i
    ON o.fldBatch = i.Batch '
    SELECT SQL=SQL + 'WHERE i.IncreasingId > '+
    CONVERT(VARCHAR,prmPageSize) + ' * ' + CONVERT(VARCHAR,prmCurrentPage)
    + ' '
    SELECT SQL=SQL + 'AND i.IncreasingId <= '+
    CONVERT(VARCHAR,prmPageSize) + ' * ('+ CONVERT(VARCHAR,prmCurrentPage)
    + ' + 1) '
    SELECT SQL=SQL + 'AND o.fldBatch = ISNULL(' +
    CONVERT(VARCHAR,prmBatch) + ' , o.fldBatch) AND o.fldStartTime >=
    ISNULL(' + CONVERT(VARCHAR,prmFromDate) + ', o.fldStartTime) '
    SELECT SQL=SQL + 'AND o.fldStartTime <= ISNULL(' +
    CONVERT(VARCHAR,prmToDate) + ', o.fldStartTime) AND o.fldMaterial =
    ISNULL(''' + prmMaterial + ''', o.fldMaterial) '
    SELECT SQL=SQL + 'ORDER BY o.fldBatch DESC'
    print SQL
    EXEC SQL
    RETURN



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Barry Guest

  2. #2

    Default Re: Dynamic SQL use of parameter/variable

    It a lot simpler to use the NULLIF function:

    NULLIF(' + prmBatch + ', 0)

    or set prmBatch to NULL before you create the dynamic SQL.

    EXEC has it's own scope, so you can't pass parameters into it.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Barry" <com> wrote in message
    news:phx.gbl... 


    Jacco Guest

  3. #3

    Default Re: Dynamic SQL use of parameter/variable

    I figured out that I can do my check and reassignment before I start
    constructing the string:

    IF prmBatch = 0 SET prmBatch=NULL
    SELECT SQL = ....

    I would surely prefer if I could set it to null as the default, but this
    is an INT and I get an error that it cannot be converted.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Barry Guest

  4. #4

    Default Re: Dynamic SQL use of parameter/variable

    Narayana,

    I'm using dynamic SQL because I plan to add three parameters. This is
    the basis for a report and I need to build a statement from the
    selection criteria. The three fields in the report's selection that I
    will add are:

    Field - representing a few columns in my table.
    Operator - Booleans such as >, >=, =, etc.
    Criterion - The last part of the statement. The best way I could think
    of to do this was using dynamic SQL in a sproc.

    I'm trying to get the thing working without those new fields before I
    complicate it even more.

    I fixed my problem by checking my value before building the string.

    I have a new problem, though. I cannot see my print statement to verify
    the string. If I back the print statement up a few lines, it works. The
    threshold seems to be around 1,200 characters, after which it stops
    printing.

    Thanks,
    Barry

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Barry Guest

  5. #5

    Default Re: Dynamic SQL use of parameter/variable

    Barry (com) writes: 

    Could be that you have set an output limit in Query yzer. Check under
    Tools->Options->Results.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. variable in an object parameter?
    By NoMailJP in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: April 12th, 05:04 PM
  2. session variable and URL parameter question
    By Fiogo in forum Coldfusion - Getting Started
    Replies: 0
    Last Post: May 5th, 11:28 AM
  3. Replies: 0
    Last Post: October 31st, 08:10 PM
  4. variable as a parameter
    By raul76@gmx.de in forum PHP Development
    Replies: 3
    Last Post: October 10th, 05:41 PM
  5. variable in function parameter
    By 386-Dx in forum PHP Development
    Replies: 4
    Last Post: July 29th, 07:17 AM

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