how to get the data from multiple rows

  • hi

    i am new to sqlserver. i want to know, how to get the values(data) from the column into a declared variable in stored procedures. here is the example i have a column name as books_data. i want all the data of that column. i have tried by i am getting the null valuecan any help me on this. i have tried to use the concat option but here in sqlserver concat is not working.

    ex:

    create proc sp_books_outlet

    @bookname varchar(100) output

    as

    select @bookname =ename from

    declare @bookname varchar(100)

    exec historical.sp_books_outlet@bookname output

    select @bookname

    thanks in advance

    aswin..

  • aswanidutt.dasara (11/3/2008)


    hi

    i am new to sqlserver. i want to know, how to get the values(data) from the column into a declared variable in stored procedures. here is the example i have a column name as books_data. i want all the data of that column. i have tried by i am getting the null valuecan any help me on this. i have tried to use the concat option but here in sqlserver concat is not working.

    ex:

    create proc sp_books_outlet

    @bookname varchar(100) output

    as

    select @bookname =ename from

    declare @bookname varchar(100)

    exec historical.sp_books_outlet@bookname output

    select @bookname

    thanks in advance

    aswin..

    Please provide the complete query

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • hi

    sorry i forgot it , this is the updated one

    ex:

    create proc sp_books_outlet

    @booknames varchar(100) output

    as

    select @booknames =b_name from historical.books

    declare @booknames varchar(100)

    exec historical.sp_books_outlet @booknames output

    select @booknames

    i have just 100 rows of data(i.e 100 book's names). i want to store all in a variable.

    thanks for the reply

    aswin..

  • aswanidutt.dasara (11/3/2008)


    hi

    sorry i forgot it , this is the updated one

    ex:

    create proc sp_books_outlet

    @booknames varchar(100) output

    as

    select @booknames =b_name from historical.books

    declare @booknames varchar(100)

    exec historical.sp_books_outlet @booknames output

    select @booknames

    thanks for the reply

    aswin..

    Your stored procedure is correct, but if you want to get the value from some user interface then use the same code as @booknames is of OUTPUT type. and if you want to take the result via stored procedure, its better you save the value in the temp table or create a function or use return statement

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • aswanidutt.dasara (11/3/2008)


    hi

    sorry i forgot it , this is the updated one

    ex:

    create proc sp_books_outlet

    @booknames varchar(100) output

    as

    select @booknames =b_name from historical.books

    declare @booknames varchar(100)

    exec historical.sp_books_outlet @booknames output

    select @booknames

    i have just 100 rows of data(i.e 100 book's names). i want to store all in a variable.

    thanks for the reply

    aswin..

    If you use front end application, do concatenation there

    or

    create proc sp_books_outlet

    @booknames varchar(8000) output

    as

    select @booknames =coalesce(@booknames+',','')+b_name from historical.books


    Madhivanan

    Failing to plan is Planning to fail

  • hi Madhivanan

    i am getting a null value.

    regards

    aswin

  • aswanidutt.dasara (11/3/2008)


    hi Madhivanan

    i am getting a null value.

    regards

    aswin

    What do you get this for?

    select b_name from historical.books


    Madhivanan

    Failing to plan is Planning to fail

  • You probably have a NULL in the bname

    select @booknames =coalesce(@booknames+',','')+COALESCE(b_name,'') from historical.books

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • hi jerry

    thanks for the help, it works

    thanks

    aswin..

  • Heh... you have books with no names? :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply