Professional Web Applications Themes

Convert Exec (SQL) to Exec sp_executesql 'SQL' - Microsoft SQL / MS SQL Server

I have a dynamic sql stored in vcSQL varchar(4000) variable. Parameters are res varchar(20), sdt smalldatetime, sdt1 smalldatetime, cPlant char(3) set vcSQL = 'select at09.datetime,isnull(at09.Value,0) as [TK # 09] from ' + case res When 'Hourly' Then 'fn_PIGetHourlydataForPlant' When 'Daily' Then 'fn_PIGetDailyDataForPlant' End + '(' + dbo.fn_PIQuoteString(Plant)+',' + dbo.fn_piquotestring (convert(varchar(50),sdt)) + ',' + dbo.fn_piquotestring (convert(varchar(50),sdt1))+ ','+dbo.fn_piquotestring ('MetNESecAT09')+ ' ) as AT09 ' fn_piquotestring (from Steve Kass) is a function that puts quotes around paramters where needed to simplify the usage of quotes. When I exec (vcSQL) it runs perfect. But I want to convert it to Exec sp_executesql syntax to ...

  1. #1

    Default Convert Exec (SQL) to Exec sp_executesql 'SQL'

    I have a dynamic sql stored in vcSQL varchar(4000)
    variable.

    Parameters are res varchar(20), sdt smalldatetime, sdt1
    smalldatetime, cPlant char(3)

    set vcSQL = 'select at09.datetime,isnull(at09.Value,0) as
    [TK # 09] from ' + case res When 'Hourly'
    Then 'fn_PIGetHourlydataForPlant' When 'Daily'
    Then 'fn_PIGetDailyDataForPlant' End + '(' +
    dbo.fn_PIQuoteString(Plant)+',' + dbo.fn_piquotestring
    (convert(varchar(50),sdt)) + ',' + dbo.fn_piquotestring
    (convert(varchar(50),sdt1))+ ','+dbo.fn_piquotestring
    ('MetNESecAT09')+ ' ) as AT09 '

    fn_piquotestring (from Steve Kass) is a function that puts
    quotes around paramters where needed to simplify the usage
    of quotes.

    When I exec (vcSQL) it runs perfect.

    But I want to convert it to Exec sp_executesql syntax to
    run it as a single inline function.

    Is this even possible, as I am executing a function above
    with appropriate parameters.

    Thanks for any help.
    Ricky Guest

  2. #2

    Default Convert Exec (SQL) to Exec sp_executesql 'SQL'

    the sp_executesql expects parameter of type nvarchar
    kroky Guest

  3. #3

    Default Re: Convert Exec (SQL) to Exec sp_executesql 'SQL'

    i'm not very intimate with the "artwork" of Steve Kass so can't tell what
    fn_PIQuoteString does exactly but in your case you can use system function
    QUOTENAME quite successfully:

    '...' + QUOTENAME(sdt, '''') + '...'

    skip the CONVERT call at all -- it gets done implicitly and done well enough
    for most datatypes.

    HTH,
    </wqw>

    "Ricky" <state.mn.us> wrote in message
    news:039f01c35d3b$4b7ad2a0$gbl... 


    Vlad Guest

  4. #4

    Default Re: Convert Exec (SQL) to Exec sp_executesql 'SQL'

    Vlad Vissoultchev (myrealbox.com) writes: 

    Beware that quotename() is limited to nvarchar(129), so if you have
    longer values, you need to something else. I have not seen Steve's
    function, but assume that it accepts an nvarchar(1998) or a varchar(3998).
     

    This script:

    declare x datetime, y char(1), z char(1)
    select x = getdate(), y = '(', z = ')'
    select y + x + z

    Dies with:

    Server: Msg 241, Level 16, State 1, Line 3
    Syntax error converting datetime from character string.

    When you use datetime values in concatenations you need to convert
    to varchar.


    --
    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. exec()
    By manu in forum PHP Development
    Replies: 6
    Last Post: July 13th, 09:53 PM
  2. SSH and EXEC problem
    By Frederic Lhoest in forum PHP Development
    Replies: 0
    Last Post: September 16th, 02:17 PM
  3. How to exec PHP as CGI
    By Lowell Allen in forum PHP Development
    Replies: 0
    Last Post: August 27th, 08:49 PM
  4. Dont want to see the exec sp_executesql result
    By anders in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 24th, 01:47 PM
  5. Replies: 0
    Last Post: December 10th, 09:27 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