Can I pass in a table name as a parameter to a SP?

Question

Can I pass in a table name as a parameter to a SP?

Rod at work

SSC-Dedicated

Points: 34008
More actions
April 11, 2007 at 4:21 pm

#175849

I want to be able to pass in a table name, as a parameter, to a stored procedure. I've got a situation in which I want to retrieve selected records from about 25 different tables. They all have the same 3 primary keys (some of the tables have additional columns in their primary keys, but they all have 3 columns that are in their primary keys) and so bringing back that data for whatever matches the key values passed would be best. I would be ideal is I could also pass in the table's name into the SP as well, rather than write 25 different SP's that will be the same, with the exception of the table's name. I tried doing there here:
CREATE PROCEDURE spASITableData
@ClientNumber int,
@CaseNumber tinyint = NULL,
@TableName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM @TableName
WHERE ClientNumber = @ClientNumber
AND ((CaseNumber = @CaseNumber) OR (CaseNumber is NULL))
ORDER BY CaseNumber DESC
END
But when I do that, I get an error of "Must declare the table variable "@TableName".

So, how do I do what I want to do?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

I would probably create a string with the SQL I want to execute in it - IE: SET @sqlStatement = 'SELECT * FROM '.... then use sp_executesql or EXEC..

- James

--
James Moore
Red Gate Software Ltd

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 2

James,

That thought did occur to me (constructing the SELECT execution string dynamically in the SP), but it seemed to me to not be any better than doing the same thing in VB.NET or C# code. Is there a performance gain to be achieved if I do the dynamic string generation in SQL Server, rather than in some DAL written in VB.NET or C#?

Kindest Regards, Rod Connect with me on LinkedIn.

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 3

Hi Rod,

I'm not sure there is particularly other than the advantage of using a stored procedure from your code rather than dynamic sql (which is some what of a religous war) - Personally in your situation I would stick with the sp, but I dont really like dynamic SQL from C#/VB.NET when it can be avoided.

- James

--
James Moore
Red Gate Software Ltd

Okbir Newbie Points: 3 More actions · Answer 4

Pass Table Name as parameters

DECLARE @sql nvarchar(4000), @objName sysname, @Count int

SET @objName = 'CUSTOMER_DIM'

SET @sql = 'SELECT * FROM ' + @objName

EXEC @Count = sp_executesql @sql