Results 1 to 9 of 9

Thread: [RESOLVED] How Can I truncate only some tables in a database using procedure?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    Resolved [RESOLVED] How Can I truncate only some tables in a database using procedure?

    There will be a true or false condition specifying whether to include or exclude the passed table names from truncating. That is, if I pass true, then truncate the tables which were passed to the procedure. Otherwise truncate all tables but the passed ones. Thank You

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,301

    Re: How Can I truncate only some tables in a database using procedure?

    You would use an IF...ELSE statement, just like in any other code.

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: How Can I truncate only some tables in a database using procedure?

    I don't think you can pass a table name as a variable unless you use dynamic SQL. You can use the passed table name to invoke some If\Then logic to truncate that table as jmcilhinney mentioned but the tables would need to be listed in the stored procedure in advance. Are you referring to passing a table name and an indicator to decide whether to truncate it or not?

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    Re: How Can I truncate only some tables in a database using procedure?

    Yes I am passing a table name and an indicator to decide whether to truncate it or not

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: How Can I truncate only some tables in a database using procedure?

    #1 Create the stored procedure

    Code:
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER OFF
    GO
    
    CREATE PROCEDURE [dbo].[VBForums](
     @TableName varchar(50),
     @Truncate tinyint)
     
     AS
    
    
    DECLARE @Sql nVARCHAR(2000)
    
    If @Truncate = 1
    	begin
    		SET @SQL = 'Truncate table ' + @TableName
    		EXECUTE sp_executesql @Sql
    	end 
    
    GO
    #2.
    Create a table.
    insert some data
    display the data
    run the stored procedure
    verify the data is gone
    drop the table

    Code:
    create table VBForums_table(myValue int)
    
    insert into VBForums_Table(myvalue) values(10)
    
    select * from VBForums_table
    
    exec VBForums 'VBForums_table', 1
    
    select * from VBForums_table
    
    drop table vbforums_Table
    I didn't add any error checking. I'll leave that up to you
    Last edited by TysonLPrice; Nov 12th, 2013 at 01:50 PM. Reason: Delete unnedded code

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    Re: How Can I truncate only some tables in a database using procedure?

    T.y. Very much

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How Can I truncate only some tables in a database using procedure?

    Is it just ONE table name being passed in? Or could it be multiple table names? that complicates it a little more. And then using the passed in table name(s) as an EXCLUSION complicates it even more.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: How Can I truncate only some tables in a database using procedure?

    Or just call it multiple times as needed...Which makes me wonder what good that swith is.

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    Re: How Can I truncate only some tables in a database using procedure?

    There will be multiple tables. I will try to loop in the procedure then...

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width