how to get the data from multiple rows

• aswanidutt.dasara

  SSC-Addicted

  Points: 403

  More actions

  November 3, 2008 at 4:34 am

  #383284

  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..

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  November 3, 2008 at 5:10 am

  #892933

  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

• aswanidutt.dasara

  SSC-Addicted

  Points: 403

  More actions

  November 3, 2008 at 5:45 am

  #892943

  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..

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  November 3, 2008 at 5:49 am

  #892944

  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

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  November 3, 2008 at 5:51 am

  #892945

  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

• aswanidutt.dasara

  SSC-Addicted

  Points: 403

  More actions

  November 3, 2008 at 6:03 am

  #892957

  hi Madhivanan

  i am getting a null value.

  regards

  aswin

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  November 3, 2008 at 6:15 am

  #892960

  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

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  November 3, 2008 at 8:03 am

  #893022

  You probably have a NULL in the bname

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

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005

• aswanidutt.dasara

  SSC-Addicted

  Points: 403

  More actions

  November 3, 2008 at 9:44 pm

  #893408

  hi jerry

  thanks for the help, it works

  thanks

  aswin..

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  November 3, 2008 at 10:05 pm

  #893416

  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)