Professional Web Applications Themes

Stored Procedure and Variables... - Microsoft SQL / MS SQL Server

Hello, im trying to build a variables string for a stored procedure like this: .. .. .. SELECT count(*) AS ObjSNR FROM SD_Objekte WHERE SD_Objekte.ObjTyp IN (17,21,20) <<<<<< HERE IS THE Problem!!!!! AND SD_Objekte.ObjLSNr = Liegenschaft AND SD_Objekte.ObjLage IN (1,2,3,4,5) AND SD_Objekte.ObjSNr IN ( SELECT RV_Belegung. rvobjnr FROM RV_Belegung WHERE (RV_Belegung.RVStart <= startdat) AND case when nurFrei <>'' then RV_Belegung.RVStatus else '' END = case when nurFrei <> '' then nurfrei else '' END ) this line is working.... SD_Objekte.ObjTyp IN (17,21,20) <<<<<< HERE IS THE Problem!!!!! this line not var as varchar(250) var="17,21,20" SD_Objekte.ObjTyp IN (var) <<<<<< HERE IS ...

  1. #1

    Default Stored Procedure and Variables...

    Hello,
    im trying to build a variables string for a stored procedure like this:
    ..
    ..
    ..
    SELECT count(*) AS ObjSNR
    FROM SD_Objekte
    WHERE SD_Objekte.ObjTyp IN (17,21,20) <<<<<< HERE IS THE Problem!!!!!
    AND SD_Objekte.ObjLSNr = Liegenschaft
    AND SD_Objekte.ObjLage IN (1,2,3,4,5)
    AND SD_Objekte.ObjSNr IN
    (
    SELECT RV_Belegung. rvobjnr
    FROM RV_Belegung
    WHERE (RV_Belegung.RVStart <= startdat)
    AND case when nurFrei <>'' then RV_Belegung.RVStatus else '' END =
    case when nurFrei <> '' then nurfrei else '' END
    )


    this line is working....
    SD_Objekte.ObjTyp IN (17,21,20) <<<<<< HERE IS THE Problem!!!!!

    this line not
    var as varchar(250)
    var="17,21,20"
    SD_Objekte.ObjTyp IN (var) <<<<<< HERE IS THE Problem!!!!!


    the datatype of objtyp is integer the content of my variable is varchar
    (17,20,21) the result is an error because its not possible to cast from
    varchar to int
    is there any way to solve this problem??

    Thanx Michael


    Michael Guest

  2. #2

    Default Re: Stored Procedure and Variables...

    > this line is working.... 

    You may try the sp_executesql - something like this

    declare sQuery as nvarchar(4000), var as varchar(250)
    set var = '17,21,20'
    set sQuery = 'SELECT * FROM SD_Objekte WHERE ObjTyp IN (' + var + ')'

    exec sp_executesql sQuery

    Serg


    Serg Guest

  3. #3

    Default Re: Stored Procedure and Variables...

    http://www.algonet.se/~sommar/arrays-in-sql.html

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


    "Michael Welz" <de> wrote in message
    news:phx.gbl... 
    startdat) 


    Jacco Guest

  4. #4

    Default Re: Stored Procedure and Variables...

    Hello,

    "Serg Prokhorov" <ru> schrieb im Newsbeitrag
    news:phx.gbl... 
    >
    > You may try the sp_executesql - something like this
    >
    > declare sQuery as nvarchar(4000), var as varchar(250)
    > set var = '17,21,20'
    > set sQuery = 'SELECT * FROM SD_Objekte WHERE ObjTyp IN (' + var + ')'
    >
    > exec sp_executesql sQuery[/ref]

    sp_executesql has a lot of troubles built in.
    e.g. You cant get any results of the select, you need client permiossions
    on the objects used and so on.

    For this case there are three other solutions:

    1. if the numer of entries in the IN list is fixed you can use: ObjTyp IN
    (var1,var2,var3,...)
    Set unused vars to null

    2. use a temporary Table and ObjTyp IN (SELECT ObjTyp FROM TempTables)

    3. (the best one)
    Write WHERE PATINDEX('%' + CONVERT(Varchar,ObjTyp) + ',%',var + ',') >
    0



     


    Rainer Guest

  5. #5

    Default Re: Stored Procedure and Variables...

    > > You may try the sp_executesql - something like this 
    >
    > sp_executesql has a lot of troubles built in.
    > e.g. You cant get any results of the select, you need client permiossions
    > on the objects used and so on.[/ref]
    About getting results from sp_executesql - it returns a recordset. And if
    you want to use a join or something similar, then you can include the whole
    query in sp_executesql..

    But about the use of PATINDEX - you call it "the best" method - i've tried
    it. A simple select by the primary key (like
    SELECT *
    FROM dbo.Showing
    WHERE ObjectID IN ( 1, 31159, 159469 )
    )

    that returns 2 records, made 100 times in a loop, took on my database approx
    17 seconds with PATINDEX and about 0.1 second with sp_executesql - I think
    such terrible decrease in performance is worth some effort with setting
    security properly?


     


    Serg Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. Replies: 6
    Last Post: September 27th, 03:55 PM
  3. is it a bug in Stored Procedure?
    By Rickey in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 16th, 01:47 PM
  4. Stored Procedure Help
    By Jack in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 14th, 06:33 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