storing values

I want to store the whole values returned by a while loop. How to do this
my store procedure is like this

create proc start_proc @country varchar(20), @c_code int, @p_id int, @out_put varchar(500) output
as
if exists(select start_date from new_start where country=@country and country_code=@c_code and program_id=@p_id)
begin
set nocount on
declare @@msg1 datetime

Declare out_put cursor
local Scroll Keyset Optimistic
For
select start_date from new_start where country=@country and country_code=@c_code and program_id=@p_id
open out_put
fetch absolute 1 from out_put into @@msg1

while @@fetch_Status <> -1
begin
print @@msg1
fetch next from out_put into @@msg1
end
close out_put
Deallocate out_put
end
else
begin
set nocount on
declare @msg varchar(100)
set @msg = 'No matches are there, please try again'
print @msg
end

I want to store the values in the output parameter and later on for the programming usage.
but now I am only getting one value out. Any way out of this.

regards
shine

Answer this question

storing values

zhu.zheng
Why can't you make the SP as simple as

create proc start_proc
@country varchar(20),
@c_code int,
@p_id int
as
select start_date
from new_start
where country=@country
  and country_code=@c_code
  and program_id=@p_id

Please provide a sample of what you expect to be returned from SP.

P.S. When looping with cursor you should check
   while @@fetch_status = 0
instead of
   while @@fetch_status <> -1
Ido Tandy
Yea exactly, it's, Comma-separated list of start_date values. Therefore, I can omit recordset. These all dates are used to populate a drop down based on some country selections. So the value returned by the stored procedure is directly passed to a hidden field, with out any overheads. Does u think it is a bad thinking On the other hand, would it take any performance cost
Prof
Still don't understand what do you mean by "whole output". Comma-separated list of start_date values Still don't understand why can't you use a just a select statement instead of out parameter.
Harreld Kuiper
The main purpose is to store the whole output to a parameter, so straightaway from the front end I can extract the value from the parameter and use that for the furthur purpose.

shine
yogaboy
Please send the result directly to the client and then perform the conversion to comma-separated list there. It is much more efficient. This way you can bind recordset directly to controls or get array of values easily and so on. So you can choose a method that fits your needs on the client side.
TLevin10
1. Generating comma-separated list may cause overflow errors (when the total length of the start_date values + commas will exceed specified 500 characters).

2. It is safer and faster to make a method on a client side to walk though incoming resultset using SqlDataReader and build a list/StringBuilder/whatever you need.

3. It is not a good practise to use cursors when they are not really needed. Actually in your case you can prepare a comma-separated list using unsupported query (I still suggest you to use standard select statement and process resultset on a client):

create proc start_proc
   @country varchar(20),
   @c_code int,
   @p_id int,
   @out_put varchar(500) output
as
   set @out_put = ''

   select @out_put = @out_put + convert(varchar(20), start_date) + ','
   from new_start
   where
      country=@country and
      country_code=@c_code and
      program_id=@p_id

   if len(@out_put)>0
      set @out_put = left(@out_put, len(@out_put) - 1)
storing values

Answer this question

storing values

Hot Topic

SQL Server

storing values

storing values

zhu.zheng

Ido Tandy

Prof

Harreld Kuiper

yogaboy

TLevin10