Professional Web Applications Themes

Dynamic Parameters in SP - Microsoft SQL / MS SQL Server

Hi all, I was wondering if there is a way to pass a set of parameters into a SP where the number of parameters is not always fixed? For example, if I am updating a user, I could either pass in all of the parameters (username, password, address, phone) or just update 1 or 2 (address). Is there a way to do this without having to write a SP for every combination? Thanks, Shane...

  1. #1

    Default Dynamic Parameters in SP

    Hi all,

    I was wondering if there is a way to pass a set of parameters into a SP
    where the number of parameters is not always fixed? For example, if I am
    updating a user, I could either pass in all of the parameters (username,
    password, address, phone) or just update 1 or 2 (address). Is there a way
    to do this without having to write a SP for every combination?

    Thanks,
    Shane


    Shane Guest

  2. #2

    Default Re: Dynamic Parameters in SP

    You can set a default value to the parameters in the stored procedure.

    Ionel
    "Shane" <com (remove underscores)> wrote in
    message news:phx.gbl... 


    ionel Guest

  3. #3

    Default Re: Dynamic Parameters in SP

    Sure, you can say

    CREATE PROCEDURE dbo.updateUser
    username VARCHAR(32),
    password VARCHAR(16) = NULL,
    address VARCHAR(37) = NULL,
    phone VARCHAR(12) = NULL
    AS
    BEGIN
    UPDATE Users SET
    password = COALESCE(password, password),
    address = COALESCE(address, address),
    phone = COALESCE(phone, phone)
    WHERE username = username
    END
    GO

    EXEC dbo.updateUser username = 'foo', password = 'newpassword'

    EXEC dbo.updateUser username = 'foo', phone = '976.900.9000'

    http://www.aspfaq.com/2348




    "Shane" <com (remove underscores)> wrote in
    message news:phx.gbl... 


    Aaron Guest

Similar Threads

  1. Adding Dynamic User Control WITH PARAMETERS
    By Jason Chu in forum ASP.NET Building Controls
    Replies: 0
    Last Post: July 14th, 06:45 PM
  2. LoadVars with dynamic parameters problem
    By dmalis webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 3
    Last Post: January 18th, 10:10 PM
  3. create parameters without creating parameters
    By dan marks in forum ASP Components
    Replies: 0
    Last Post: October 30th, 01:51 AM
  4. Dynamic Proc Parameters
    By Aaron Bertrand - MVP in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 07:53 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