-
Nov 12th, 2013, 12:31 AM
#1
Thread Starter
Lively Member
[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
-
Nov 12th, 2013, 01:14 AM
#2
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.
-
Nov 12th, 2013, 06:32 AM
#3
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?
-
Nov 12th, 2013, 06:55 AM
#4
Thread Starter
Lively Member
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
-
Nov 12th, 2013, 07:24 AM
#5
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
-
Nov 13th, 2013, 05:28 AM
#6
Thread Starter
Lively Member
Re: How Can I truncate only some tables in a database using procedure?
-
Nov 13th, 2013, 02:55 PM
#7
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
-
Nov 13th, 2013, 02:58 PM
#8
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.
-
Nov 19th, 2013, 02:42 AM
#9
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|