sql Stored Procedure > table name as an input parameter.

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi,

I have a stored procedure as below:

SQL

USE [MashtiHasanShoppingDB]
GO
/****** Object:  StoredProcedure [dbo].[Del_Users]    Script Date: 9/12/2015 1:57:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Del_Users]
@UserID int
AS
BEGIN
	DELETE FROM Users where UserID = @UserID
END

Now, I want to have this stored procedure working for other table's. I shouldn't write the same code over and over for one database.
I want it to take the table name as an parameter, something like:

SQL

ALTER PROCEDURE [dbo].[Del_Users]
@UserID int,
@Table_name nvarcher(50)
AS
BEGIN
	DELETE FROM @Table_name where UserID = @UserID
END

But i don't know how to do it.

I'd be grateful of your help,
Thanks.

Posted 11-Sep-15 23:06pm

m.r.m.40

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

GuyThiebaut · Answer 1 · 2015-09-11T23:27:00

By creating a dynamic sql statement you will be able to build and run the query at runtime.

This should do the trick. First run the stored procedure and check the output from the print command. When you are confident that all is okay, comment out the print command and uncomment the exec command:

ALTER PROCEDURE [dbo].[Del_Users]
@UserID int,
@Table_name nvarcher(50)
AS
BEGIN
	
	declare @cmd nvarchar(max) = ''
	set @cmd += N' DELETE FROM ' + @Table_name + ' where UserID = ' @UserID
    print @cmd
	--exec sys.sp_executesql @cmd

	
END

OriginalGriff · Answer 2 · 2015-09-11T23:30:00

Solution 2

You can do it - but it's a pain:

SQL

DECLARE @Command NVARCHAR(MAX)
SET @Command = 'DELETE FROM ' + @Table_name + ' WHERE UserId = ' + @UserID
EXEC(@Command)

It's also risky: it leave you open to SQL Injection attacks if @UserID is changed to a string.

Personally? I wouldn't do it.

Posted 11-Sep-15 23:30pm

OriginalGriff