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

2 arraystring - cursor

Hello,
I have 2 strings that I pass into a stored procedure:
@DIdString varchar(100)  For example: (1,3,7,15)
@StartDateString varchar(500)  For example (22.3.2012, 29.7.2012,30.8.2012,5.9.2012)

DECLARE @DId int

DECLARE d_cursor cursor for
SELECT DId,DText FROM MyTable  ------ (Need to get StartDate from @StartDateString as well)
WHERE DId  IN (select Value from dbo.ParmsToList(@DIdString,','))

I also need to get the items from StartDateString into the cursor. How do I do that?
There are equal number of items in @DIdString and @StartDateString
0
johnkainn
Asked:
johnkainn
  • 4
  • 3
  • 2
3 Solutions
 
LIONKINGCommented:
If you're calling this procedure from within SQL (and not from an external application) you could use table-valued parameters (Link).

If not, you'll have to use a function to split the values and the dates as well.
Can we see what's being done in the cursor? Maybe there's no need for the cursor after all.
0
 
keyuCommented:
do you want values from @StartDateString  too while creating cursor is that so ?

DECLARE @DId int

DECLARE d_cursor cursor for
SELECT DId,DText FROM MyTable  where startdate in (select Value from dbo.ParmsToList(@StartDateString,',')) and DId  IN (select Value from dbo.ParmsToList(@DIdString,','))

if you want to store result in multiple paramater you can do this...

DECLARE @MergeDate Datetime
 DECLARE @MasterId Int
 DECLARE @DuplicateId Int
 
SELECT @MergeDate = GetDate()
 

DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0
 
OPEN merge_cursor
 
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId

WHILE @@FETCH_STATUS = 0
 BEGIN
  EXEC MergeDuplicateCustomers @MasterId, @DuplicateId
 
 UPDATE DuplicateCustomers
  SET
   IsMerged = 1,
   MergeDate = @MergeDate
  WHERE
   MasterCustomerId = @MasterId AND
   DuplicateCustomerId = @DuplicateId
 
 FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
 END
 
CLOSE merge_cursor
DEALLOCATE merge_cursor


if not refer below link you can achive this without cursor to.. as cursor is not suggested considering performance....

http://www.sommarskog.se/arrays-in-sql-2008.html
0
 
johnkainnAuthor Commented:
I actually pass in 3 arraystrings. One (@SIdString) is used in the cursor. I have to check if the item exists before I insert. Can I take StartDateString and SIdString and insert into temporary table, that I will then use to populate the cursor? How is best to do that?

      @DIdString varchar(500),
      @StartDateString varchar(1000),
      @SIdString varchar(500)


DECLARE @DId int

DECLARE D_cursor cursor for
SELECT DId,DText FROM MyTable
WHERE DId  IN (select Value from dbo.ParmsToList(@DIdString,','))

open D_cursor;

FETCH NEXT FROM D_cursor
INTO @DId

WHILE @@FETCH_STATUS=0
      BEGIN

      INSERT INTO STable(DId,SId,DateCreated)
      SELECT @DId,SId,GETDATE() FROM StTable s
      WHERE SId IN (select Value from dbo.ParmsToList(@SIdString,',')) AND SId not in (SELECT sd1.SId from STable sd1 where DId=@DId)

FETCH NEXT FROM D_cursor
INTO @DId

END
CLOSE D_cursor;
DEALLOCATE D_cursor
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LIONKINGCommented:
I think you could use temp tables with this (or even table variables).

Give this a try and let us know if it throws the correct results.

IF OBJECT_ID('tempdb.dbo.#tempSIdString') IS NOT NULL DROP TABLE #tempSIdString
IF OBJECT_ID('tempdb.dbo.#tempDIdString') IS NOT NULL DROP TABLE #tempDIdString
IF OBJECT_ID('tempdb.dbo.#ExistingSId') IS NOT NULL DROP TABLE #ExistingSId

SELECT Value
INTO #tempSIdString
FROM dbo.ParmsToList(@SIdString,',')

SELECT Value
INTO #tempDIdString
FROM dbo.ParmsToList(@DIdString,',')

SELECT DISTINCT StTable.SId
INTO #ExistingSId
FROM StTable INNER JOIN #tempDIdString t ON
StTable.DId=t.Value

INSERT INTO STable(DId,SId,DateCreated)
SELECT DIdString.Value, StTable.SId, GETDATE()
FROM StTable INNER JOIN #tempSIdString SIdString ON
StTable.SId= SIdString.Value CROSS JOIN #tempDIdString DIdString
WHERE StTable.SId NOT IN (SELECT SId FROM #ExistingSId)
0
 
johnkainnAuthor Commented:
For following to work then I have to check on SId and DId, i.e. use WHERE SId=@SId AND DId=@DId.

SELECT DISTINCT StTable.SId
INTO #ExistingSId
FROM StTable INNER JOIN #tempDIdString t ON
StTable.DId=t.Value

How can I create one table from #tempDIdSting and #tempsIdString?
I.e. if @DIdString is '1,2' and @StartDateString is '7,8'
Then I would like to get 2 rows:
'1, 7'
'2, 8'
0
 
LIONKINGCommented:
You can use union.

SELECT @DIdString AS myColName
INTO #tempTable
UNION ALL
SELECT @StartDateString
0
 
LIONKINGCommented:
For following to work then I have to check on SId and DId, i.e. use WHERE SId=@SId AND DId=@DId.

This code:
SELECT DISTINCT StTable.SId
INTO #ExistingSId
FROM StTable INNER JOIN #tempDIdString t ON
StTable.DId=t.Value

Open in new window


Is to replace this part of your original query:
AND SId not in (SELECT sd1.SId from STable sd1 where DId=@DId)

Open in new window


So what you're doing is inserting all the SId's that at least on of the DId's that are passed in the parameter.
For example, if @DIdString = '1,2' then you're doing

SELECT DISTINCT SId FROM  StTable WHERE DId IN ('1','2')

Open in new window

0
 
johnkainnAuthor Commented:
Thak you I will check this out.

About my earlier question. I am curious how can I get a table with with 2 columns, i.e.

Row1:  DId=1, StartDate='7'
Row2:  DId=2, StartDate='8'

from @DIdString and @StartDateString?
0
 
keyuCommented:
for that you can use below function....

CREATE FUNCTION dbo.Split()      
returns @temptable TABLE (items varchar(8000),items1 varchar(8000))      
as      
begin      
    declare @idx int  
    declare @idx1 int          
    declare @Delimiter varchar(5)
    declare @slice varchar(8000)
    declare @slice1 varchar(8000)        
    declare @String varchar(8000)  
    declare @String1 varchar(8000)

      set @String='Chennai','Bangalore','Mumbai'
      set @String='test1','test2','test3'
      set @Delimiter =','

    select @idx = 1  
    select @idx1 = 1    
        if len(@String)<1 or @String is null  return      
        if len(@String1)<1 or @String1 is null  return
    while (@idx!= 0 and @idx1!= 0)      
    begin      
        set @idx = charindex(@Delimiter,@String)
        set @idx1 = charindex(@Delimiter,@String1)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
           set @slice = @String      
         
        if @idx1!=0      
            set @slice1 = left(@String1,@idx1 - 1)      
        else      
           set @slice1 = @String1

        if(len(@slice)>0)  
            insert into @temptable(Items,items1) values(@slice,@slice1)      
 
        set @String = right(@String,len(@String) - @idx)    
        set @String1 = right(@String1,len(@String1) - @idx1)  
       if len(@String) = 0 break      
       if len(@String1) = 0 break      
    end  
return      
end  


to call that function...

select top 10 * from dbo.split()
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now