Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Dummy tables in sql server 2000

    hi,
    In a stored procedure i have created dummy table.I have used some conditions and i have displayed some results.EG:200 records are displayed.

    But i need to insert those 200 records in the dummy table which is created in the same stored procedure as in the above i have mentioned.

    i am posting the procedure i have created....
    please any one help me .........it's urgent.........

    CREATE PROCEDURE p_diamondsearch_stud_Earring_test
    (
    @LowPrice varchar(50)= '100',
    @HeighPrice varchar(50)= '1000000',
    @Cut1 varchar(20)='' ,
    @Cut2 varchar(20)='' ,
    @Shape varchar(50) = '0',
    @Weight1 varchar(6) = '0',
    @Weight2 varchar(6) = '0',
    @ColorLow varchar(50) ,
    @ColorHeigh varchar(50),
    @ClarityLow varchar(50),
    @ClarityHeigh varchar(50),
    @OrderBy varchar(50)='catalognr',
    @SortOrder varchar(10)='ASC'
    )
    AS
    BEGIN
    create table #Diamonds1
    (
    partno1 varchar(30),color1 varchar(10),weight1 varchar(10),cut1 varchar(10),clarity1 varchar(10),length1 varchar(10),depth1 varchar(10),width1 varchar(10),sell1 varchar(10),LWRatio1 varchar(20),polish1 varchar(10),symmtry1 varchar(10),lab varchar(10)
    )



    declare @StrSQL varchar(5000),
    @tablevar int
    SET @StrSQL = ''
    SET @StrSQL = @StrSQL + ' SELECT D.partno as partno1,D.color as color1,D.weight as weight1 ,D.cut as cut1 ,D.clarity as clarity1 ,D.length as lenght1,D.depth as depth1,D.width as width1 ,D.sell as sell1 ,LWRatio1 = (D.length/D.width), D.polish as polish1 ,D.symmetry as symmtry1,D.lab as lab'

    SET @StrSQL = @StrSQL + ' FROM product_diam D
    where (D.catalognr = ''1025'' or D.catalognr = ''1027'') AND D.New_Release=''N'' AND (D.qtyoh - D.qtycom) > 0 and D.cut > 0 and D.avail <> ''T'' and (D.below_standard = 0) --and D.partno <> D.partno '

    if len(@LowPrice) > 0 and len(@HeighPrice) > 0
    Begin
    Set @StrSQL = @StrSQL + ' AND (D.sell ) BETWEEN ' + @LowPrice + ' AND ' + @HeighPrice
    End

    if len(@Cut1) > 0 and len(@Cut2) > 0
    Begin
    Set @StrSQL = @StrSQL + ' AND D.cut BETWEEN '+ @Cut1 + 'AND ' + @Cut2
    -- Set @StrSQL = @StrSQL + ' AND P.cut BETWEEN '+ @Cut1 + 'AND ' + @Cut2
    End

    if len(@Shape)>0
    Begin
    Set @StrSQL = @StrSQL + ' AND D.shape = ' + @Shape
    --Set @StrSQL = @StrSQL + ' AND P.shape = ' + @Shape
    End

    if len(@Weight1) > 0 and len(@Weight2) > 0
    Begin
    --Set @StrSQL = @StrSQL + ' AND (D.weight) BETWEEN ' + @Weight1 + ' AND ' + @Weight2
    Set @StrSQL = @StrSQL + ' AND (P.weight) BETWEEN ' + @Weight1 + ' AND ' + @Weight2
    --Set @StrSQL = @StrSQL + ' AND (D.weight + P.weight) BETWEEN ' + @Weight1 + ' AND ' + @Weight2
    End

    if len(@ColorLow) > 0 and len(@ColorHeigh) > 0
    Begin
    Set @StrSQL = @StrSQL + ' AND D.color BETWEEN ''' + @ColorLow + ''' AND ''' + @ColorHeigh + ''''
    -- Set @StrSQL = @StrSQL + ' AND P.color BETWEEN ''' + @ColorLow + ''' AND ''' + @ColorHeigh + ''''
    End

    if len(@ClarityLow) > 0 and len(@ClarityHeigh) > 0

    Begin
    Set @StrSQL = @StrSQL + ' AND D.clarity BETWEEN ' + @ClarityLow + ' AND ' + @ClarityHeigh
    -- Set @StrSQL = @StrSQL + ' AND P.clarity BETWEEN ' + @ClarityLow + ' AND ' + @ClarityHeigh
    End

    if len(@OrderBy)>0
    Begin
    if (@OrderBy = 'sell')
    Begin
    Set @StrSQL = @StrSQL + ' Order By (D.sell) ' + @SortOrder
    End
    else
    Begin
    Set @StrSQL = @StrSQL + ' Order By D.' + @OrderBy --+ ',P.'+ @OrderBy + ' ' + @SortOrder
    end
    End
    *****************************


    declare @partno1 varchar(5000)
    declare @color1 varchar(20)

    declare @clarity1 varchar(20)
    declare @length1 varchar(20)
    declare @depth1 varchar(20)
    declare @width1 varchar(20)
    declare @sell1 varchar(20)
    declare @LWRatio1 varchar(20)
    declare @polish1 varchar(20)
    declare @symmtry1 varchar(20)
    declare @lab varchar(20)



    insert into #Diamonds1 (partno1,color1,weight1,cut1,clarity1,length1,dept h1,width1,sell1,LWRatio1,polish1,symmtry1,lab) values (@partno1,@color1,@weight1,@cut1,@clarity1,@length 1,@depth1,@width1,@sell1,@LWRatio1,@polish1,@symmt ry1,@lab)
    select * from #Diamonds1

    END

    drop table #Diamonds1

    Exec(@StrSQL)
    --print @StrSQL
    GO


    in the procedure i have marked the ***********symbol
    above procedure is correct it displays nearly 200 records.
    But i need to insert all records in the #Diamonds1 table.

    Regards,
    Prakash

  2. #2
    Join Date
    Jun 2009
    Location
    CA, USA
    Posts
    59
    You can try the following....

    Insert into #Diamonds1
    exec (@StrSQL)
    MohammedU
    SQL Server MVP

  3. #3
    Join Date
    Jun 2009
    Posts
    2

    Insertion of records in Dummy tables

    hi Mohammed,

    Thanks for your code.
    It's working fine......
    very simple idea but i didn't think about this.
    I have tried in several ways except this way....
    Now the records are inserted in the dummy table #Diamonds1

    Regards,
    Prakash

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •