Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Unanswered: Stored Procedure with input and output parameter

    I have created a stored procdure with a in and out pararmeter. It
    takes the name of a designer as an in parameter and otput parameter
    of the categories of furnitures the designer has created. When i run
    the stored procedure I am getting an empty record.. although therea
    are supposed to be a number of categories of furnitures for the
    desinger name I send as an input parmeter. Have I missed something
    obvisous.

    The Stored Procdure code:

    CREATE PROCEDURE spDesignerKat
    @Designer nvarchar (20) ,
    @Kategori nvarchar (20) output
    AS
    SELECT
    dbo.VIEW1tbl.ProdID, dbo.VIEW1tbl.ProdNamn, dbo.VIEW1tbl.LevNamn,
    dbo.VIEW1tbl.DesingerNamn, dbo.VIEW1tbl.KatNamn,
    dbo.VIEW1tbl.SubKatNamn, dbo.VIEW1tbl.serieNamn,
    dbo.VIEW1tbl.Beskrivning, dbo.VIEW1tbl.Dimentioner,
    dbo.VIEW1tbl.PrisEuro, dbo.VIEW1tbl.PrisEuro * dbo.Kurs.SekKurs AS
    PrisSek, dbo.VIEW1tbl.BildLiten, dbo.VIEW1tbl.BildStor,
    dbo.VIEW1tbl.Width, dbo.VIEW1tbl.Height
    FROM dbo.VIEW1tbl CROSS JOIN dbo.Kurs
    WHERE DesingerNamn = @Designer
    and
    KatNamn = @Kategori
    ORDER BY dbo.VIEW1tbl.ProdNamn
    GO

    -----------------------------------------------------------

    The execution code:

    declare @DesignerKategori nvarchar (20)
    declare @KatVar nvarchar (20)
    exec @DesignerKategori = spDesignerKat @Designer = 'Walter Knoll',
    @Kategori = @KatVar OUTPUT

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Fantomen
    You have to assign a value to @Kategori. Unfortunately, this variable is only used in the where clause. The procedure would have to look something more like:

    use pubs
    go
    create procedure test (@input varchar(40), @output varchar(11) output)
    as
    select @output = au_id
    from authors
    where au_fname = @input
    go

    Probably what you need is to have two selects in the procedure. One to collect the category you want returned in the output parameter, and one to return the dataset. Hope this helps.
    ~Matt

  3. #3
    Join Date
    Feb 2003
    Posts
    7

    'Stored Procedure with input and output parameter'

    thanks.. but how would the exec code for this sp look like.. the store proc code is the following as your example:

    create procedure test (@input varchar(40), @output varchar(11) output)
    as
    select @output = KatNamn
    from VIEW1tbl
    where DesingerNamn = @input
    GO


    If I supply the input parameter with the designer name 'Jorge Pensi' and want to return the out parameter to supply all the category of furnitures he has designed. The out parameter is connected to the columm KatNamn

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Hmm. An output parameter can only hold a single value (like a return status). If you need to get a list of values, you should probably re-write the procedure as

    create procedure test (@input varchar(40))
    as
    select KatNamn
    from VIEW1tbl
    where DesingerNamn = @input
    GO


    If you need to use this data set in a further query, you could write something like

    create procedure test (@input varchar(40))
    as

    select (a bunch of fields)
    from (some set of tables)
    where KatNamn in (
    select KatNamn
    from VIEW1tbl
    where DesingerNamn = @input)
    GO


    Does this work better?
    ~Matt

  5. #5
    Join Date
    Aug 2011
    Posts
    1

Posting Permissions

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