Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to pass in the name of a table into a stored procedure?

For example, suppose you have several views of the same table. They all have the exact same structure.

You want a store procedure that can be run for any of the views.

Something like:

create procedure myprocedure
@tableName varchar(50) = ''
select blah from @tableName where blah = blah2 

When I try to do this, I get

Must declare the table variable @tablename

Any ideas how I can do this?

share|improve this question
4  
And it's easier to create a bunch of views where you had to write out the same date range queries? Isn't it easier to pass date parameters to a stored procedure, then you don't have to write out any dates in the SQL code at all. so confused –  Aaron Bertrand Jun 20 '12 at 14:52
5  
Ok. This seems very backwards to me - every time you want to query a new date range, you have to create a new view with that date range hard-coded into the view? That should be real fun to maintain over time... –  Aaron Bertrand Jun 20 '12 at 14:58
2  
@dublintech You've got to consider your "bus factor" in this. If someone inherited this code, it may create significant confusion, dissonance, and loss of functionality in the event of an emergency. The approach you're taking is neither portable nor scalable. If things change a few months in, you may be setting yourself up for a significant amount of rework. –  swasheck Jun 20 '12 at 15:07
3  
So when you find a set of interesting date ranges, put them in a table and give them an ID, then use them in a join like @CadeRoux suggested. The approach you're taking is madness. –  Aaron Bertrand Jun 20 '12 at 15:48
4  
@dublintech If the time periods are important, having them in data instead of in code lets you do important things with them more easily. –  Cade Roux Jun 20 '12 at 15:49

3 Answers 3

up vote 11 down vote accepted

Instead of creating views, why not make an inline table-valued function - effectively a parameterized view. You basically get all the benefits of views AND parameters.

Another thing you can do in your TVF is something like this:

SELECT yourtable.*
FROM yourtable
INNER JOIN ranges
    ON yourtable.date BETWEEN ranges.start AND ranges.end
WHERE ranges.name = @range_name

Now your ranges have names (in a table where they can be easily managed without changing the schema) - this also fairly seamlessly handles multiple ranges with the same name, although if ranges overlap you can have duplicates due to the join matching both.

Also, please consider avoiding BETWEEN: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

I strongly suggest that you reconsider your requirements and when asking questions here be sure to include more about your motivations, because when you bring in a question with a preconceived notion of what the solution is, you are likely to get a suboptimal solution. (like the dynamic SQL solutions given aren't going to easily work for your OR case, but that information about the problem space is buried in a comment from you)

From: http://sqlfiddle.com/#!6/a628a/1

CREATE TABLE LogData (
  Id INT IDENTITY NOT NULL
  ,Dt DATETIME NOT NULL
  ,Data VARCHAR(max) NOT NULL
);

CREATE TABLE Ranges (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
  ,DtStart DATETIME NOT NULL
  ,DtEnd DATETIME NOT NULL
);

CREATE TABLE Groups (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
);

CREATE TABLE RangeGroups (
  RangeId INT NOT NULL
  ,GroupId INT NOT NULL
);

INSERT INTO LogData (Dt, Data)
VALUES ('2011-11-23 11:00', 'before thanksgiving day')
       ,('2011-11-24 11:00', 'on thanksgiving day')
       ,('2011-12-24 11:00', 'on christmas eve')
       ,('2011-12-25 11:00', 'on christmas day')
       ,('2012-11-21 11:00', 'before thanksgiving day')
       ,('2012-11-22 11:00', 'on thanksgiving day')
       ,('2012-12-24 11:00', 'on christmas eve')
       ,('2012-12-25 11:00', 'on christmas day');

INSERT INTO Ranges (Name, DtStart, DtEnd)
VALUES ('THANKSGIVING2011', '2011-11-24', '2011-11-25')
       ,('CHRISTMASEVE2011', '2011-12-24', '2011-12-25')
       ,('CHRISTMASDAY2011', '2011-12-25', '2011-12-26')
       ,('BOXINGDAY2011', '2011-12-26', '2011-12-27')
       ,('NEWYEARSEVE2011', '2011-12-31', '2012-01-01')
       ,('NEWYEARSDAY2012', '2012-01-01', '2012-01-02')
       ,('THANKSGIVING2012', '2012-11-22', '2012-11-23')
       ,('CHRISTMASEVE2012', '2012-12-24', '2012-12-25')
       ,('CHRISTMASDAY2012', '2012-12-25', '2012-12-26')
       ,('BOXINGDAY2012', '2012-12-26', '2012-12-27')
       ,('NEWYEARSEVE2012', '2012-12-31', '2013-01-01')
       ,('NEWYEARSDAY2013', '2013-01-01', '2013-01-02');

INSERT INTO Groups (Name)
VALUES ('HOLIDAYS2011')
       ,('HOLIDAYS2012')
       ,('ANYCHRISTMAS');

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2011')
FROM Ranges WHERE Name LIKE '%2011';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2012')
FROM Ranges WHERE Name LIKE '%2012';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'ANYCHRISTMAS')
FROM Ranges WHERE Name LIKE 'CHRISTMAS%';


CREATE FUNCTION dbo.JustDoIt(@GroupName VARCHAR(50))
RETURNS TABLE
RETURN (
SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName
);

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2011');

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2012');

SELECT *
FROM dbo.JustDoIt('ANYCHRISTMAS');

This part:

SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName

Means that each log item will be joined to the ranges it fits in (by date), each range will join to the groups they are in, but only that group selected will be used.

share|improve this answer
2  
I think what he's trying to 'save' is having to type dates for parameters, instead he can just say ViewAugust. After all, why create a function either, when your where clause can just apply to the base table? If you teach him about inline TVFs he might just create a TVF for each date range. <shrug> –  Aaron Bertrand Jun 20 '12 at 15:03
1  
@dublintech sqlfiddle.com/#!6/425db/1 –  Cade Roux Jun 20 '12 at 17:09
1  
@dublintech that example not only gives names to the ranges but also groups them. I did not restrict range to be unique, so you could have multiple Ranges with the same name and same group. You can see the power you now have by having this in a table - you can now do all kinds of interesting stuff with ranges. –  Cade Roux Jun 20 '12 at 17:11
1  
+1 way above and beyond the call of duty. @dublintech I suspect you should have a consultant on staff to save you from bad designs. –  Aaron Bertrand Jun 20 '12 at 17:33
1  
@dublintech See revised one which has an ITVF and shows the usage is now super simple with all logic in the function and the table. –  Cade Roux Jun 20 '12 at 19:21

I think your best bet is to use dynamic SQL to accomplish this:

create procedure myprocedure
@tableName nvarchar(50) = ''
as

    declare @sql_cmd nvarchar(200) 
    set @sql_cmd = 'select blah from ' + QUOTENAME(@tableName) + ' where blah = blah2'

    exec (@sql_cmd)

go
share|improve this answer
    
@AaronBertrand Ha! No problem, thanks for the extra set of eyes. QUOTENAME() is good, and I did change to unicode. Thanks, Aaron! –  Thomas Stringer Jun 20 '12 at 14:47

Object names cannot be parameterized.

You need to use dynamic SQL:

CREATE PROCEDURE MyProc
(
    @schemaName sysname,
    @tableName sysname,
    @blah2 int
)
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @sql nvarchar(MAX)

    SET @sql = N'SELECT blah FROM '
        + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' WHERE blah = @blah2'

    EXEC sp_executesql @sql, N'@blah2 int', @blah2

END

Note that sysname is (currently) defined as nvarchar(128).

share|improve this answer
    
I was going to post a different answer also, but felt it better to improve the existing answer, since the differences are quite subtle. –  Aaron Bertrand Jun 20 '12 at 15:02
    
@Aaron: Yeah, I was editing it, too. ;) But then I realized I wanted to make too many changes, so I posted my own. –  Jon Seigel Jun 20 '12 at 15:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.