TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Nepethya Rana
NA
335
151.6k
How to pass schema and table name in store proc
Sep 26 2016 4:00 AM
Is it possible to pass schema name and table name as parameter in storeprocedure?
I am trying to write a store procedure to delete huge(million of records) in chunk of small size. Since there are many such tables under different schema instead of writing different store procedure for each tables.
For example:
spDeleteRecords 'dbo.tblEmployee', 1000, '00:00:05';
MyCode:
CREATE PROCEDURE spDeleteRecord
@SchemaTableName varchar(100),
@DeleteBatchSize INT,
@DelayTime DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteRowCount INT
SET @DeleteRowCount = 1
WHILE (@DeleteRowCount > 0)
BEGIN
BEGIN TRANSACTION
DELETE TOP(@DeleteBatchSize)
@SchemaTableName;
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
COMMIT
WAITFOR DELAY @DelayTime
END
END
GO
Problem here is I must have to declare Table variable. How do i do it?
Reply
Answers (
2
)
Syntax to create store proc to delete huge receords
Distribution of temp db in multiple files