Sql Server Cursor

Hi,
      I have a function in Oracle and I was looking the Sql Server Equivalent



/* ##
 pParentUID, pDefUID, pWFStepWF are input Parameters

 Variables inside  the function
  mRetVal    VARCHAR2(2000);
  mTemp      VARCHAR2(2000);
 
 SchemaGetRelObj is another function already created

*/

-- The Portion I need help in getting into Sql Server is  

 CURSOR c1 IS
    SELECT  obj2.ObjName, obj2.objuid
    FROM    scherel rel, scheobj obj2
    WHERE Rel.uid1 = pParentUID
            AND rel.uid2 = obj2.objuid
            AND rel.defuid = pDefUID

 FOR rec IN c1 LOOP
          If pWFStepWf Is Not Null Then
            mTemp := SchemaGetRelObj(rec.objuid, 'SPFWorkflowWorkflowStep', '21');
            If mTemp = pWFStepWf Then
              mTemp := rec.ObjName;
            Else
              mTemp := mTemp || '~' || rec.ObjName;
            END IF;
          Else
            mTemp := rec.ObjName;
          END IF;

          If mRetVal IS NULL THEN
            mRetVal := mTemp;
          ELSE
            mRetVal := mRetVal || '|' || mTemp;
          END IF;
      END LOOP;
   

I have got experts help on a cursor  in the following link
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28487514.html
Sam OZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Something like
DECLARE vendor_cursor CURSOR FOR 
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' + 
        @vendor_name

    PRINT @message

    -- Declare an inner cursor based   
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR 
    SELECT v.Name
    FROM Purchasing.ProductVendor pv, Production.Product v
    WHERE pv.ProductID = v.ProductID AND
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @product

    IF @@FETCH_STATUS <> 0 
        PRINT '         <<None>>'     

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product
        END

    CLOSE product_cursor
    DEALLOCATE product_cursor
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor 
    INTO @vendor_id, @vendor_name
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Open in new window

0
Sam OZAuthor Commented:
hi Thanks,

    The first thing I need to make sure is  whether there is a way of  avoiding the  Cursor someway ( Even though it looks difficult )
 But I am quite naive with this and running on a tight timeline  . Can you please give me the equivalent of my cursor

Thanks
 Sam
0
HuaMin ChenProblem resolverCommented:
You are calling other function in your loop.
Please try to define the cursor and do the loop in the way I showed above.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Alpesh PatelAssistant ConsultantCommented:
Hello,

You can use SSMA for Oracle (To migrate from ORacle to SQL Server). After migrating you have to fine tune that objects.
0
HuaMin ChenProblem resolverCommented:
Try

   open C1;
       fetch next from c1 into @mTempName , @mTempUID  ;
        
        while @@FETCH_STATUS = 0 
          begin
            if @pWFStepWF is not null  
            begin
                          set @mTemp = dbo.SchemaGetRelObj(@mTempUID,  'SPFWorkflowWorkflowStep', '21','','') ;
                          if @mTemp = @pWFStepWF 
                            set @mTemp = @mTempName ;
                          else
                            set @mTemp = @mTemp + '~' +  @mTempName;            
            end        
            else
                          set @mTemp = @mTempName  ;
            
            fetch next from c1 into @mTempName , @mTempUID  ;
          end
        
       close c1  
       deallocate c1

Open in new window

0
Scott PletcherSenior DBACommented:
I think this is at least very close.  I wasn't sure about the data types for the UID values: change below to match your data column data type.


CREATE FUNCTION function_name (
    @pParentUID uniqueidentifier, --change to int/bigint other matching data type as needed
    @pDefUID uniqueidentifier, --change to int/bigint other matching data type as needed
    @pWFStepWF nvarchar(2000) = NULL
)
RETURNS nvarchar(2000)
AS
BEGIN
DECLARE @mRetVal nvarchar(2000)
SELECT @mRetVal = STUFF((
    SELECT
        CASE WHEN @pWFStepWF IS NOT NULL
             THEN '~' + dbo.SchemaGetRelObj(rec.objuid, 'SPFWorkflowWorkflowStep', '21')
             ELSE '|' + rec.ObjName
        END
    FROM scherel rel, scheobj obj2
    WHERE
        Rel.uid1 = @pParentUID
          AND rel.uid2 = obj2.objuid
          AND rel.defuid = @pDefUID
    FOR XML PATH('')
    ), 1, 1, '')
RETURN @mRetVal
END --FUNCTION
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gmartinncDatabase ArchitectCommented:
--Not sure of your types, but for example...
DECLARE @pParentUID	UNIQUEIDENTIFIER;
DECLARE @pDefUID	UNIQUEIDENTIFIER;
DECLARE @pWFStepWf	VARCHAR(50);

DECLARE c1 CURSOR FOR 
	SELECT  
		obj2.ObjName
		, obj2.objuid
    FROM    
		scherel rel
		INNER JOIN scheobj obj2
			ON rel.uid2 = obj2.objuid
	WHERE
		Rel.uid1 = @pParentUID
        AND rel.defuid = @pDefUID;

DECLARE @objName	VARCHAR(50);
DECLARE @objUID		UNIQUEIDENTIFIER;
DECLARE @mTemp		VARCHAR(50);
DECLARE @mRetVal	VARCHAR(50);

--Init vars.
SELECT @mTemp = '';
SELECT @mRetVal = NULL;

FETCH NEXT FROM c1 INTO @objName, @objUID;
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @pWFStepWf IS NOT NULL
	BEGIN
		SELECT @mTemp = dbo.SchemaGetRelObj(@objUID, 'SPFWorkflowWorkflowStep', '21');	--you will have to write this FUNCTION
		IF @mTemp = @pWFStepWf
			SELECT @mTemp = @objName;
		ELSE
			SELECT @mTemp = @mTemp + '~' + @objName;
	END
	ELSE
	BEGIN
		SELECT @mTemp = @objName;
	END

    IF @mRetVal IS NULL 
		SELECT @mRetVal = @mTemp;
    ELSE
		SELECT @mRetVal = @mRetVal + '|' + @mTemp;

	FETCH NEXT FROM c1 INTO @objName, @objUID;
END

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.