Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a stored proc with parameters

    OK, on to baby steps... how do I create a stored proc in SQL Server that I can pass a value to in ASP?

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a stored proc with parameters

    CREATE PROCEDURE your_procedure_name
    @YourVariable1 int,
    @YourVariable2 varchar(30)
    AS

    SELECT *
    FROM YourTable
    WHERE YourField1 = @YourVariable AND YourField2 = @YourVariable2


    Use ASP code similar to the example provided in <post#=469719>post 469719</post#>, except be sure to create a separate parameter object for each parameter you have in your Stored Procedure (making sure the name and data type match the stored proc).

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a stored proc with parameters

    thanks. confusing! i could have sworn I already had that but I kept getting an error when trying to execute the proc with a param value.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a stored proc with parameters

    There are a host of other factors that go into the process,. For instance:
    - The user account you're using to log into SQL Server (either with Windows or SQL authentication) must have permissions to execute the desired stored procedure
    - The Parameter must have the same name and data type in both SQL Server and ASP code

    What's the error you're getting?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a stored proc with parameters

    the error is resolved, but I recall, when opening the stored proc for Execute in Query Analyzer, being told that the proc cannot accept parameter values as it doesn't expect them. I MAY have put the DECLARE after the AS in the stored proc code...

    You probably already know this, but I thought it was cool: you can run multiple recordsets in one stored proc and output them in ASP via rs.NextRecordSet() for subsequest recordsets in the stored proc.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a stored proc with parameters

    Yeah - be sure not to declare any variables after "AS" that are already declared as input parameters (before "AS").

    I usually avoid returning multiple recordsets in a single stored procedure due to the inherent coupling of functionality. I try to have a stored procedure do as little as possible - it's more efficient and reusable that way...

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a stored proc with parameters

    that's probably where I went wrong. (putting vars after AS)

    as to the multiple recordsets, I don't know if this is a good idea in any case, but I just think it's kinda cool.

    FWIW, I now have a big thick book SQL Server 2000 Programming that I can now...uh... read.

Posting Permissions

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