Using variable in FROM Clause

[BradEdwards]

I created a stored procedure called spStaffingModelsRpt that passes the name of the month as @Month. I then create a #Temporary table that selects records from the @Month table but it will not allow me to use @Month in the FROM Clause. I saw another posting that said to create the SQL statment dynamically i.e.

Select @dynamicQuery = 'Select * from ' + @Month
Exec @dynamicQuery

But when I run 'exec spStaffingModels 'July' through an SQLPassThrough query in Access, it says it "could not find stored procedure 'S'. (#2182)". Following is a VERY simplified version of my stored procedure:

CREATE PROCEDURE spStaffingModelsRpt (@Month varchar)

AS

DECLARE @dynamicQuery varchar

SELECT @dynamicQuery = 'Select * from ' + @Month
EXEC @dynamicQuery

Am I doing something wrong? Please help!!!

 

[Guest_imported]

try this, in your called SP - add/prime a receiving variable called @month-in ....

DECLARE @dynamicQuery varchar,
@month-in varchar

select @month-in = @month
SELECT @dynamicQuery = 'Select * from ' + @Month-in

 

[rac2]

Hello Brad,

Try declaring the variables with explicit length. The default value for length is 1, which would explain why the system thinks you have a stored procedure named 'S'.

@monthReported VARCHAR(10)

DECLARE @dynamicQuery VARCHAR(50)

Also use EXEC() instead of EXEC. You don't need to put the string into a variable, just do this.

EXEC('SELECT * FROM ' + @monthReported)

Good luck,
Richard

 

[BradEdwards]

You were correct Richard. I declared the @month variable with a length of 10 and this fixed the problem. Thanks.

One more thing though. Before I passed the @Month variable I was just using a single table to get my data from called tblJournal and I was using a SELECT statement to pull the data from that table. However each month is going to have over 300,000 records in it so I decided to split the tables up into separate months, hence the reason why I'm passing the @month variable.

Here was my code before:

CREATE TABLE #Data
(
BRN smallint,
SEQ int,
StartTime datetime,
StopTime datetime,
TimeDiff datetime
)
INSERT INTO #Data (BRN, SEQ, StartTime, StopTime, TimeDiff)
(SELECT BRN, SEQ, StartTime, StopTime, (StopTime-StartTime) as TimeDiff
FROM tblJournal
WHERE (StartTime Between @StartDate and @EndDate))

And here is my code now:

CREATE TABLE #Data
(
BRN smallint,
SEQ int,
StartTime datetime,
StopTime datetime,
TimeDiff datetime
)
INSERT INTO #Data (BRN, SEQ, StartTime, StopTime, TimeDiff)
Exec ('SELECT BRN, SEQ, StartTime, StopTime, (StopTime-StartTime) as TimeDiff
FROM ' + @Month +
' WHERE (StartTime Between ''' + @StartDate + ''' and ''' + @EndDate + '''))

The first code took about 18 seconds to run, and the latter took about 52 seconds and the only thing I changed was passing the @Month variable and using the EXEC() command. Does using the EXEC() command take longer than just a regular SELECT statement. Just wondering??? Thanks again Richard.