• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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
0
Sam OZ
Asked:
Sam OZ
2 Solutions
 
HuaMinChenBusiness AnalystCommented:
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
 
HuaMinChenBusiness AnalystCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
HuaMinChenBusiness AnalystCommented:
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
 
gmartinncCommented:
--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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now