Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008

    Unanswered: Parsing array of values to SP in sql 7


    I have to create a SP in sql server 7

    The secenario is that,
    A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)

    UserID AcctID Level1 level2
    test testee N Y

    the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.

    After checking all the strings ,it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows

    Eg:the string of value from application will be as

    (SVDB, N, Y; VGBY, Y, B; SCFV, Y, S; SRVD, Y, N; .....)

    The SP have to get the fist set of data i.e SVDB, N, Y , check if SVDB exists in the table for that login, if yes update else insert.

    Finally do acheck and delete the Acctid not available in above list

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Nottinghamshire, UK
    SqlDBA everything about the process your explaining here feels so....... well.... wrong !!

    It is not particularly difficult to write TSQL to do as you wish, search these forums and you'll find a number of ways to split out an array of values.

    However I ask if your able to approach this requirement differently.

    ie. lose the procedural approach that your taking and start thinking like a DBA - SET BASED!

    possibly get your app to write your data from into a log table first, then do a set based JOIN operation to update your target tables. then flag your rows as commited, much easier for tracing/debugging & performance.

    the amount of data your dealing with now may not cause you a performance problem at the moment but it's a good idea to lose any bad procedural thinking habits.

    **Using Cursors kills performance**

    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    While Ive written code to do it, stored procedures shouldn't accept parameters that are "arrays" to be parsed. Sometimes this is a worthwhile "last ditch effort" to fix a production failure, but it is not a good design practice.


Posting Permissions

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