?
Solved

Can some one write this with out cursor

Posted on 2011-05-09
5
Medium Priority
?
311 Views
Last Modified: 2012-05-11
Can some one write this sql with out cursor

declare @state_id int , @num varchar(100), @all varchar(2000)

                  declare cur_state cursor for
                  select state_id
                  from state

                    open cur_state
                  while (1=1)
                  begin
                        fetch cur_inst into @inst_id
                        if @@fetch_status<>0 break


                        declare cur_acct cursor for
                        select ltrim(rtrim(data))
                        from view_values v
                        where v.type='P'
                        and v.status='A'
                        and v.name='Acc'
                        and v.state_id=@state_id

                        select @all =''
                        open cur_acct
                        while (1=1)
                        begin
                              fetch cur_acct into @num
                              if @@fetch_status <> 0 break
               
                              select @all = @all + ' / '  + @num
                        end
                        close cur_acct
                        deallocate cur_acct

                        if @all <> ''
                        begin
                              
                              select @all = substring(@all, 4,len(@all) - 3)

                              update country
                              set acct_no = @all
                              where state_id=@state_id
                        end
                   
                  end
                  close cur_state
                  deallocate cur_state
            end

This should be compatible ith sql 2000 also

Thanks in advance


0
Comment
Question by:vijay11
5 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1332 total points
ID: 35726121
Hi.

Is this a duplicate of: http://www.experts-exchange.com/Q_27027991.html
Or is this one just for T-SQL.  Just trying to figure out if this question is pretty much the same and needs to work for Sybase also.  Also, if it is the same, you should delete this one since it has no participation yet.

If this is strictly from the MS SQL side of things, then the solution is as shown in the other question:
UPDATE [country]
SET [acct_no] = SUBSTRING((SELECT '/' + LTrim(RTrim([data]))
        FROM view_values v
        WHERE v.type='P'
        AND v.status='A'
        AND v.name='Acc'
        AND v.[state_id] = [country].[state_id]
        FOR XML PATH('')), 2, 8000)
;

Open in new window

0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 35727728
Create a function using attached code, after which you can update using the command "update country set acct_no = joindata(state_id)"

 
create function joindata (@stateid int)
returns varchar(2000) 
as BEGIN
	declare @all varchar(2000)
	set @all = '/'
	while exists(select top 1 ltrim(rtrim(data)) from view_values v where v.type='P' and v.status='A' and v.name='Acc' and v.state_id=@state_id and @all not like '%/'+ltrim(rtrim(data))+'/%' )
	BEGIN
		set @all = @all + (select top 1 ltrim(rtrim(data))+'/' from view_values v where v.type='P' and v.status='A' and v.name='Acc' and v.state_id=@state_id and @all not like '%/'+ltrim(rtrim(data))+'/%' )
	END
	if len(@all)>1 set @all = left(@all, len(@all)-1)		-- to drop ending /
	if len(@all)>1 set @all = right(@all, len(@all)-1)	    -- to drop starting /
	return (@all)
END

Open in new window

0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 668 total points
ID: 35727887
select Top 1 @state_id = state_id
from state

-- Do thing

Test:
select Top 1 @state_id = state_id
from state where state_id > @state_id

If @state_Id IS not null
Begin
      --Dothing
End else begin
Go to Test:
End
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1332 total points
ID: 35734753
Hi.

In case your other question is going down a similar, but different path I will post my cursor rewrite here and await your confirmation in the other thread on what it is you are trying to do.
declare @state_id int, @all varchar(2000);

-- grab the smallest state id value first.
select @state_id = min(state_id)
from state;

-- while you have a valid state id, keep iterating.
while(@state_id is not null)
begin
   -- reset @all for each run.
   set @all = null;

   -- build delimited string per current state id.
   -- coalesce and concatenation should work in Sybase.
   select @all = coalesce(@all+'/', '') + ltrim(rtrim(data))
   from view_values v
   where v.type='P'
   and v.status='A'
   and v.name='Acc'
   and v.state_id=@state_id;

   -- update country table using delimited string and state id.
   update country
   set acct_no = @all
   where state_id=@state_id
   -- if you don't want to update if @all is null
   -- and @all is not null
   ;

   -- grab next state id.
   select @state_id = min(state_id)
   from state
   where state_id > @state_id;
end

Open in new window


It gets rid of the multiple cursors which should decrease cost.  Especially if you have proper indexing in place on state_id and other criteria used in retrieving data.  If you want to reduce this further, you can check the EXECUTION PLAN and see where the highest cost is within this newer script.

Hope that helps!

Kevin
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35737375
Kevin,

>>In case your other question is going down a similar<<
I think you mean questions, here is a third duplicate question:
http://www.experts-exchange.com/Microsoft/Development/Q_27028021.html

Anthony
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question