Tuesday, May 30, 2006


Accepting Contributions

Please send any contributions to me, and I will post them here on your behalf. Check my profile for contact information.

There are limited options for Sybase professionals looking for information, and I'd be pleased to help in any way I can. Sadly I have changed jobs recently and since I don't work with Sybase as much anymore, I will be contributing very little myself.

In the mean time, visit the links on this page, enjoy some of the excellent articles in the archives, and check back for new contributions from fellow Sybase professionals.

I am expecting this kind of article every month...

Please release....
Unfortunately I have not received any contributions.

If you have already gone through my archives, and reviewed all the articles contained in my links, then you probably know so much that you should write an article yourself for me to post. :)
My contribution is a question:

I have a stored proc that queries a couple big tables. I would like to take advantage of a sort order of the tables and use a sort merge.

In the proc I create a temp table, then call another proc to do the final query. (As sybase says, so table stats etc are available). When I run the stored proc, the optimizer always chooses NLJ. When I run the exact same code from an isql session, the optimizer always chooses MJ and the query rips.

What is different? Why will the optimizer refuse to do a sort_merge in the stored proc? I have "set sort_merge on" and also have the server-wide setting.
This is Yugandhar. I started using Sybase recently after working in oracle for 8 years. I need help in the below problem.

I am using T-SQL to develop a stored procedure.

1. I am creating a cursor using Exec statement by passing a tablename generated using a random number.
2. When I exec the cursor and tried to open it says it doesnt exist. the cursor name is emp_cur.

I need to loop through the temp table created using this cursor and create an output string.

Any help is greatly appreciated. I am sending you the stored procedure I am using.

thanks in advance for your time and help.


/*This operation is being done in a transaction.
Please ensure that in case of error the transaction is rolled back.*/
drop procedure dbo.getSupProc
create procedure getSupProc @empid int in, @tier int in, @empList Varchar(500) out
declare @SomeRandomString varchar(30)
declare @createSql varchar(1000)
declare @dropSql varchar(1000)
declare @selectSql varchar(1000)
declare @insertSql varchar(1000)
declare @updateSql varchar(1000)
declare @updateRoleSql varchar(1000)
declare @cursorSql1 varchar(1000)
declare @cursorSql2 varchar(1000)
declare @employeeid int
declare @superId int
declare @userId varchar(30)
declare @roleid int
declare @cursorSql varchar(1000)
EXEC RandomStringProc 10, @Result = @SomeRandomString OUTPUT
select @SomeRandomString=@SomeRandomString+convert(varchar(10),@empId)
--select @SomeRandomString+convert(varchar(10),1)
print @SomeRandomString
select @createSql = 'create table tempdb..'+@SomeRandomString+'(employeeId int, supervisorid int, userid varchar(30) null, roleid int null, tier int null)'
select @dropSql ='drop table tempdb..'+@SomeRandomString
select @selectSql ='select distinct employeeId, supervisorId, userId, roleId, tier from tempdb..'+@SomeRandomString + ' order by 1,2'
select @cursorSql1 ='declare emp_cur cursor for select distinct employeeId, supervisorId, userId,'
select @cursorSql2 = 'roleId from tempdb..'+@SomeRandomString
select @cursorSql ='select distinct employeeId, supervisorId, userId,roleId from tempdb..'+@SomeRandomString
declare emp_cur1 cursor using @cursorSql
EXEC (@createSql)
-- invoke the procedure
exec getSupervisorProc1 @empid,@tier,@SomeRandomString
select @updateSql = 'update tempdb..'+@SomeRandomString + ' set userid = isnull(b.userid, ''No UserId'') from secdata..vwemployeeinfo b where supervisorid=b.employeeid'
exec (@updateSql)
select @updateRoleSql = 'update tempdb..'+@SomeRandomString + ' set roleid = (Select MAX(b.roleid) from asscApplicationUserRole b where userid = b.userid)'
exec (@updateRoleSql)
--concatenate the values as a string and return in the out parameter
print 'before open cursor'
exec immediate (@cursorSql1+@cursorSql2)
print @cursorSql1
print @cursorSql2
print @SomeRandomString
--select @cursorSql
print 'before open'
open emp_cur
print 'before fetch'
fetch emp_cur into @employeeId, @superId, @userId, @roleid
print 'after fetch'
while (@@sqlstatus = 0)
select @empList = @empList + "||" + convert(varchar(30), @employeeId) + ":" + convert(varchar(30), @superId) +":" + @userId + ":" + convert(varchar(30),@roleid)
print 'before last fetch'
fetch emp_cur into @employeeId, @superId, @userId,@roleid
print 'after last fetch'
-- print 'after emp_cur1'

close emp_cur
select @empList
--drop the random table
exec (@dropSql)
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?