Professional Web Applications Themes

Stored Procedures - Microsoft SQL / MS SQL Server

I am new to Stored Procedures and I need some help please. I am trying to create a stored procedure that would change sensitive data of multiple tables to privatize then for testing and developing purposes. Basically for a given table say: Students I'll have another table PrivateStudents that will have the same columns as students plus a reference to Students primary key. I create a Cursor for Students and go through all rows. For each row I have a second cursor that selects the record in PrivateStudents with the current Students Primary key number. Then I update Students with ...

  1. #1

    Default Stored Procedures

    I am new to Stored Procedures and I need some help please.

    I am trying to create a stored procedure that would
    change sensitive data of multiple tables to privatize
    then for testing and developing purposes.
    Basically for a given table say: Students I'll have
    another table PrivateStudents that will have the same
    columns as students plus a reference to Students primary
    key.
    I create a Cursor for Students and go through all rows.
    For each row I have a second cursor that selects the
    record in PrivateStudents with the current Students
    Primary key number.
    Then I update Students with PrivateStudents data.

    My problem is that I want to pass those two tables as
    parameters (real table, sample data table) and so columns
    and column names will vary.
    Is there a way to do this using stored procedures?
    Could I for example say: Column(1), Column(2), Column(n)
    instead of using column name?
    I'll appreciate any suggestions.

    Vladi Guest

  2. #2

    Default stored procedures

    Hi all,

    I am new to stored procedures so please bear with me..

    I have created a new stored proc like this:

    create procedure abc(this_id nvarchar(250), this_desc nvarchar(250))
    as select * from tbl_journal
    where 0 = 0
    and title = this_id
    and description = this_desc
    go


    My question is...I want to make the parameters optional, so that if one
    value isn't passed, the stored proc will just ignore the clause for that
    parameter.

    For example, if this_id is passed to the stored proc, but this_desc is
    not, I want the stored proc to execute:

    select * from tbl_journal
    where 0 = 0
    and title = this_id

    Also, when I call the stored proc and pass only one of the parameters, I
    get an error saying that it expected all parameters.

    Thanks for any help.
    Hammy



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

  3. #3

    Default Re: stored procedures

    Here is one method that you can use:




    CREATE PROCEDURE abc
    this_id nvarchar(250) = NULL, this_desc nvarchar(250) = NULL
    AS

    IF this_id IS NULL AND this_desc IS NULL
    BEGIN
    select * from tbl_journal
    RETURN (0)
    END

    IF this_id IS NULL AND this_desc IS NOT NULL
    BEGIN
    select * from tbl_journal WHERE description = this_desc
    RETURN (0)
    END

    IF this_id IS NOT NULL AND this_desc IS NULL
    BEGIN
    select * from tbl_journal WHERE title = this_id
    RETURN (0)
    END

    IF this_id IS NOT NULL AND this_desc IS NOT NULL
    BEGIN
    select * from tbl_journal WHERE title = this_id AND description = this_desc
    RETURN (0)
    END
    GO



    --
    Keith, SQL Server MVP

    "Hammy Hammy" <ca> wrote in message news:uGoVW$phx.gbl... 
    Keith Guest

  4. #4

    Default Re: stored procedures

    You can find an extensive discussion of this problem, including all the
    performance implications, in this article by Erland Sommarskog:
    http://www.algonet.se/~sommar/dyn-search.html

    "Hammy Hammy" <ca> wrote in message
    news:uGoVW$phx.gbl... 


    Jacco Guest

  5. #5

    Default Stored Procedures

    This is a question that may not have a black and white answer.

    Our company is developing an application that imports data from many
    different legacy systems into a standard data structure. One client may
    have many locations with disparate information systems, and our application
    loads them each into their own SQL database with each having the same table
    structures, relationships, reporting capabilities, etc.

    When it comes to using stored procedures, our company's current method is to
    have the same SP in use for all databases. Each SP will then essentially be
    a big CASE statement that checks the legacy system type before acting. The
    thinking is to allow for easier SP maintenance. However, when it comes to
    converting and importing the data, it then requires our interface code to be
    smart enough to know what type of data system it is dealing with, then
    insert the data accordingly.

    The two options are:
    1) Have one stored procedure accross all databases, regardless of legacy
    system, and have the interface code know what it needs to know about the
    data it is importing. This allows for easier SP maintenance and exact SQL
    database structure.
    2) Have each SQL database know what type of system it is dealing with and
    have SPs that deal with that specific system. Then the interface code can
    be general and call the same SP name (which in turn is unique code for each
    database) regardless of the legacy system.

    Is one "better" than the other, or is it just preference?

    Thanks in advance.

    Dan Colbert
    Senior Process Engineer
    JDA eHealth Systems, Inc.


    Dan Guest

  6. #6

    Default Re: Stored Procedures

    why do you do 'select' if you do not want to return the resultset. perhaps,
    you want to post some code + sample data so we can see what you're trying to
    do.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Alejandro Becker" <com.ar> wrote in message
    news:phx.gbl... 


    oj Guest

  7. #7

    Default Re: Stored Procedures

    I do select. then I do IF and then I do another select.

    Thanks

    "oj" <com> wrote in message
    news:%23$phx.gbl... 
    perhaps, 
    to [/ref]
    one. 
    >
    >[/ref]


    Alejandro Guest

  8. #8

    Default stored procedures

    Hi,
    I wrote the code in asp page.
    How to write this in stored procedures if I pass string to sql server?

    strValue="1999,12,2003,10,2000,2,1988,7"
    strAry=Split(strValue,",",-1)
    maxCount=UBound(strAry)

    strClause="(year='" & strAry(0) & "' and month='" & strAry(1) & "')"

    for iCount=3 to maxCount
    if iCount mod 2 = 1 then
    strClause = strClause & " or (year='" & strAry(iCount-1) & "' and month='"
    & strAry(iCount) & "')"
    end if
    next

    result is:
    (year='1999' and month='12') or (year='2003' and month='10') or (year='2000'
    and month='2') or (year='1988' and month='7')


    Thanks


    Mark Guest

  9. #9

    Default stored procedures

    Hi,

    is there any generic way to anumerate all stored procedures in the database
    (ms sql or oracle or any other supporting stored procedure) ?

    thanks for help

    x



    Guest

  10. #10

    Default Re: stored procedures

    check out this VB+ADO snippet:

    With New Connection
    .Open CONN_STR
    With .OpenSchema(adSchemaProcedures)
    Do While Not .EOF
    Debug.Print !PROCEDURE_NAME, !DATE_CREATED
    .MoveNext
    Loop
    End With
    End With

    this must be DBSCHEMA_PROCEDURES for rguidSchema param of
    IDbSchemaRowset::GetRowset in OLEDB

    HTH,
    </wqw>

    <pl> wrote in message news:phx.gbl... 
    database 


    Vlad Guest

Similar Threads

  1. Stored Procedures and MySQL 5
    By Jon in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 6th, 01:14 AM
  2. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  3. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  4. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  5. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 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