Professional Web Applications Themes

Using ISNULL in Dynamic SQL - Microsoft SQL / MS SQL Server

Hi, I have dynamic sql in a sproc that takes a few optional parameters. One of the parameters is an NVARCHAR and it's default value is NULL. In the dynamic query string I check for null so that if the parameter is not passed, I can get all records. Something like this: Alter Procedure MySproc(MyChar NVARCHAR(50)=NULL, other parameters...) AS DECLARE SQL NVARCHAR(3000) SQL='SELECT fields FROM tblBlah WHERE fldChar = ISNULL(' + QUOTENAME(MyChar,'''') + ', fldChar)' The problem is that when I don't pass in anything for the NVARCHAR parameter, it surrounds the word NULL with quotes, which is not evaluated ...

  1. #1

    Default Using ISNULL in Dynamic SQL

    Hi,

    I have dynamic sql in a sproc that takes a few optional parameters.
    One of the parameters is an NVARCHAR and it's default value is NULL.
    In the dynamic query string I check for null so that if the parameter
    is not passed, I can get all records. Something like this:

    Alter Procedure MySproc(MyChar NVARCHAR(50)=NULL, other
    parameters...)
    AS
    DECLARE SQL NVARCHAR(3000)
    SQL='SELECT fields FROM tblBlah WHERE fldChar = ISNULL(' +
    QUOTENAME(MyChar,'''') + ', fldChar)'

    The problem is that when I don't pass in anything for the NVARCHAR
    parameter, it surrounds the word NULL with quotes, which is not
    evaluated as NULL by ISNULL. If I leave off the QUOTENAME function,
    then when I do pass in a value for the parameter, it doesn't get the
    quotes, which doesn't seem to be legit for the query. Should I not use
    the default NULL value? Is there a better way of doing this?
    Barry Guest

  2. #2

    Default Re: Using ISNULL in Dynamic SQL

    This should work:
    Alter Procedure MySproc(MyChar NVARCHAR(50)=NULL, other
    parameters...)
    AS
    DECLARE SQL NVARCHAR(3000)
    set SQL='SELECT fields FROM tblBlah WHERE fldChar = ' +
    isnull(MyChar,'')+...............

    --
    -Vishal
    "Barry" <com> wrote in message
    news:google.com... 


    Vishal Guest

  3. #3

    Default Re: Using ISNULL in Dynamic SQL

    Srinivas,

    Unfortunately, my company's &$*! firewall prevents me from getting to
    Anith's site.

    I think I've also concluded that I need to manually construct my where
    clause. I was hoping to avoid it because the code is already fairly long
    and this would probably double its size.

    Slide a pot of coffee under my door every few hours; I'll be out in
    about a week.

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

  4. Moderated Post

    Default Re: Using ISNULL in Dynamic SQL

    Removed by Administrator
    Barry Guest
    Moderated Post

Similar Threads

  1. SELECT ISNULL DATE
    By mikeap in forum Coldfusion Database Access
    Replies: 4
    Last Post: November 29th, 06:25 PM
  2. dynamic drop downlists in an editable dynamic datagrid.
    By mkhans@gmail.com in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 22nd, 09:01 PM
  3. How do I check for nulls? IsNull??
    By bflophil in forum Macromedia ColdFusion
    Replies: 0
    Last Post: February 18th, 11:22 PM
  4. IsNull bug in ASP?
    By Bob Cottis in forum ASP
    Replies: 2
    Last Post: October 15th, 12:31 PM
  5. Using IF and IsNull statements in SELECT
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 03:43 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