none
stored procedure

    Question

  • Hi
    I am new to stored procedures.I have a table with one text field .I need to create a stored procedure for addingand deleting keywords in that text filed.Also I need show all the records of keywords in that text field
    Thursday, April 23, 2009 8:06 PM

Answers

  • Thursday, April 23, 2009 9:15 PM
  • ---------------------------------------------------------------
    --	This procedure adds the given text if not exist, 
    --	If already exists it will be deleted
    create procedure Keywords1
    @keyword varchar(20)
    as
    	if exists(	select	keyword
    				from	MyKeywords
    				where keyword=@keyword
    			 )
    		Begin
    			print 'Keyword already present so deleting'
    			
    			delete from MyKeywords 
    			where keyword=@keyword
    		End
    	else
    		Begin
    			print 'Keyword not present so Adding'
    			
    			insert into MyKeywords values (@keyword)
    		End
    ---------------------------------------------------------
    Keywords1 'aaaa'
    ---------------------------------------------------------
    --	This procedure adds the given text if operation='a'  
    --	and deletes if operation='d'
    create procedure Keywords2
    @operation varchar(2),@keyword varchar(20)
    as
    	if(@operation='a')
    		begin
    			if exists(	select	keyword
    						from	MyKeywords
    						where keyword=@keyword
    					)
    				Begin
    					print 'Keyword already present'
    				end
    			else
    				Begin			
    					insert into MyKeywords values (@keyword)
    				End
    		 End	
    	else if(@operation='d')
    		Begin
    			if exists(	select	keyword
    						from	MyKeywords
    						where keyword=@keyword
    					)
    				Begin
    					delete from MyKeywords 
    					where keyword=@keyword
    					
    				end
    			else
    				Begin			
    					print 'Keyword not present'
    				End	
    		End
    ---------------------------------------------------------
    Keywords2 a,'aaaa'
    ---------------------------------------------------------


    Friday, April 24, 2009 3:51 AM

All replies

  • Thursday, April 23, 2009 9:15 PM
  • ---------------------------------------------------------------
    --	This procedure adds the given text if not exist, 
    --	If already exists it will be deleted
    create procedure Keywords1
    @keyword varchar(20)
    as
    	if exists(	select	keyword
    				from	MyKeywords
    				where keyword=@keyword
    			 )
    		Begin
    			print 'Keyword already present so deleting'
    			
    			delete from MyKeywords 
    			where keyword=@keyword
    		End
    	else
    		Begin
    			print 'Keyword not present so Adding'
    			
    			insert into MyKeywords values (@keyword)
    		End
    ---------------------------------------------------------
    Keywords1 'aaaa'
    ---------------------------------------------------------
    --	This procedure adds the given text if operation='a'  
    --	and deletes if operation='d'
    create procedure Keywords2
    @operation varchar(2),@keyword varchar(20)
    as
    	if(@operation='a')
    		begin
    			if exists(	select	keyword
    						from	MyKeywords
    						where keyword=@keyword
    					)
    				Begin
    					print 'Keyword already present'
    				end
    			else
    				Begin			
    					insert into MyKeywords values (@keyword)
    				End
    		 End	
    	else if(@operation='d')
    		Begin
    			if exists(	select	keyword
    						from	MyKeywords
    						where keyword=@keyword
    					)
    				Begin
    					delete from MyKeywords 
    					where keyword=@keyword
    					
    				end
    			else
    				Begin			
    					print 'Keyword not present'
    				End	
    		End
    ---------------------------------------------------------
    Keywords2 a,'aaaa'
    ---------------------------------------------------------


    Friday, April 24, 2009 3:51 AM
  • Friday, April 24, 2009 8:50 AM
  • I used my code as below
    Create table myProduct (i int, j int, t text)
    go
    create unique index ui on myProduct
    (i, j)
    go
    create proc nr_Adtxt
    @i int ,
    @j int ,
    @t varchar(8000) ,
    @Action varchar(1) -- 'I insert
    as
    declare @ptr binary(16)

    if @Action = 'I'
    begin
    insert myProduct
    (
    i ,
    j ,
    t
    )
    select @i ,
    @j ,
    @t
    end

    if @Action = 'A'
    begin
    select @ptr = textptr(t)
    from mySecond
    where i = @i
    and j = @j

    updatetext myProduct.t @ptr null 0 @t
    end

    go
    exec nr_Adtxt 1, 1, 'abc,bdes,123', 'I'--insert
    exec nr_Adtxt 1, 1, '678,es,123', 'A'--update
    select i,j, substring(t, 1, 8000) from myProduct


    --This code will replace all occurrances of a string in a text column in all rows

    delete myProduct
    exec nr_Adtxt 1, 1, '6,bdes,123', 'I'
    exec nr_Adtxt 1, 1, '678,bdes,123', 'A'
    declare @t varchar(8000)
    select @t = space(6000) + 'sadf'
    exec nr_Adtxt 1, 1, @t, 'A'
    select @t = 'sadf'
    exec nr_Adtxt 1, 1, @t, 'A'
    select @t = space(6000) + 'sadf'
    exec nr_Adtxt 1, 1, @t, 'A'
    select @t = 'sadf'
    exec nr_Adtxt 1, 1, @t, 'A'
    but I dont know how can i use stored procedure for replace .No I am able to insert keyword and delete the keywords.But not able to do update function If you have any idea please let me know
    Thanks in advance
    Friday, April 24, 2009 9:19 PM
  • Still an issue?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, June 03, 2013 3:43 PM
    Owner