4 Comments

1. SHAHNAWAZ says:

   March 6, 2012 at 6:41 am

   ALTER PROCEDURE Proc_Filter_Mobile
   – Add the parameters for the stored procedure here
   @MOBSER_ID SMALLINT
   AS
   BEGIN
   DECLARE @INT_POS SMALLINT,
   @ANI NVARCHAR(20)=NULL,
   @CUR_ANI NVARCHAR(1)=NULL,
   @FLAG TINYINT,
   @ANI_ID INT,
   @ANI_SUB NVARCHAR(6)=NULL,
   @COUNT TINYINT=0;

   DECLARE @CUR_FILTER CURSOR;
   SET NOCOUNT ON;
   DECLARE @CHK_ANI TINYINT;

   CREATE TABLE #Bulk_Load_Mobile_Series
   (
   Load_ID BIGINT IDENTITY(1,1),
   MobileSeries_SeriesID int,
   MobileSeries_ANI nvarchar(20)
   )

   – ## SETTING CURSOR ##
   SET @CUR_FILTER=CURSOR FOR
   SELECT DataTemp_ID,DataTemp_ANI FROM Bulk_DataTemp;

   – ## OPENING CURSOR
   OPEN @CUR_FILTER

   –## FETCHING CURSOR VALUE
   FETCH @CUR_FILTER INTO @ANI_ID,@ANI
   WHILE @@FETCH_STATUS=0
   BEGIN
   –## CUTTING LAST FIVE CHARACTER FROM ANI
   SET @ANI_SUB=SUBSTRING(@ANI,9,5);

   –## CHECKING GOLDEN,SILVER AND PLATINUM NUMBER AND FILTERED
   SET @INT_POS=0;
   WHILE @INT_POS<=9
   BEGIN
   SET @CHK_ANI=1;
   SET @FLAG=0;
   WHILE @CHK_ANI=3)
   BEGIN
   SET @COUNT=1;
   END;

   –PRINT ‘CURRENT ANI -’ + @CUR_ANI;
   –PRINT CAST(@INT_POS AS VARCHAR);
   – PRINT ‘FLAG VALUE’ + CAST(@FLAG AS VARCHAR);

   END;
   ELSE
   BEGIN
   SET @FLAG=0;
   END
   SET @CHK_ANI=@CHK_ANI+1;
   END
   SET @INT_POS=@INT_POS+1;
   END

   IF(@COUNT1)
   BEGIN
   –### DELETE THAT RECROD FROM THE TABLE###
   – PRINT @ANI_ID;
   INSERT INTO #Bulk_Load_Mobile_Series(MobileSeries_SeriesID,MobileSeries_ANI)
   VALUES(@MOBSER_ID,@ANI);
   –DELETE FROM [DBO].Bulk_DataTemp
   –WHERE DataTemp_ID=@ANI_ID;
   SET @FLAG=0;
   SET @COUNT=0;
   END
   ELSE
   BEGIN
   SET @COUNT=0;
   SET @FLAG=0;
   END;
   FETCH @CUR_FILTER INTO @ANI_ID,@ANI
   END
   CLOSE @CUR_FILTER;
   DEALLOCATE @CUR_FILTER;

   SELECT * FROM #Bulk_Load_Mobile_Series

   END
   GO

   Reply to this comment
2. syscosys says:

   September 26, 2011 at 6:03 am

   this is helpful. thanks!

   Reply to this comment
3. ron stephens says:

   August 26, 2011 at 12:11 pm

   I need to sum a field defined as varchar. In Oracle I’d do a to_num. We have an older version of SQLserver. what is the syntax of this? All the example don’t use a field name.

   Reply to this comment
   • Derek Dieter says:

     September 16, 2011 at 1:36 pm

     Hi Ron,

     To do this, you just need to cast the value as an int. Using:

     SELECT CAST(mycolumn AS int)
     FROM table 1

     Reply to this comment