Variables in SQL

Hello Experts,
Can I use variable file name in sql statement? For example Select *From @Variable_File_Name?  Thank you very much in advance.

Thank you!
RadhaKrishnaKiJayaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff DarlingDeveloper AnalystCommented:
-- Create a table Variable
DECLARE @myTable TABLE (
	FirstName varchar(30)
	,LastName varchar(30)
	);
	
	
-- add some records to @myTable
Insert into @myTable (FirstName,Lastname) values  ('John','Smith') 
Insert into @myTable (FirstName,Lastname) values  ('Jane','Doe') 
Insert into @myTable (FirstName,Lastname) values  ('Homer','Simpson') 

-- Query Table Variable
Select * from @myTable

Open in new window

0
OriNetworksCommented:
I assume you mean variable table name, but I generally stay away from dynamic sql such as this. See information about sp_executesql https://msdn.microsoft.com/en-us/library/ms188001.aspx I generally stay aware from this because it can lead to sql injection or malicious activity if not properly used.

If you have a small number of tables to switch between, use a stored procedure with an if statement to see which table to query. For example,
IF @tblName='table1' 
BEGIN
SELECT col1,col2 FROM table1
END

IF @tblName='table2' 
BEGIN
SELECT col1,col2 FROM table2
END

Open in new window

0
RadhaKrishnaKiJayaAuthor Commented:
Hi Jeff Darling,  Thank you for your help.

I have some tables like XX_2013, XX_2014, XX_2015 etc.  I want to select the table based on the @YEAR parameter passed.  It is not a new table I need to create, but need to select an existing one.

Thank you again!
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott PletcherSenior DBACommented:
You really can't do that in SQL Server.  SS doesn't allow variable object names.
0
JestersGrindCommented:
To be honest, I believe your issue stems from bad design, especially if those tables are identical with data from different years.  They should really be one table with a year column.  Then querying it would be trivial.
0
Jeff DarlingDeveloper AnalystCommented:
I think that OriNetworks gave you the solution above for that.  

You would need to use a stored procedure and then have if statements for every year table that you want to refer to.

IF @YEAR ='2013' 
BEGIN
SELECT col1,col2 FROM xx_2013
END


IF @YEAR ='2014' 
BEGIN
SELECT col1,col2 FROM xx_2014
END

Open in new window

0
OriNetworksCommented:
If you were to use sp_executesql the syntax would be something like

DECLARE @sql2 as nvarchar(max)
declare @tblName as varchar(50)
set @tblName='tablename'
Select @sql2 ='select 1 from ' + @tblName

EXECUTE sp_executesql @sql2

Open in new window


Although again, I would avoid this if at all possible
0
Scott PletcherSenior DBACommented:
I think your best option is dynamic SQL.

You can use "template" SQL and just substitute in the year right before you run the code, something like this:

DECLARE @sql varchar(8000)
SET @sql = '
SELECT ...
FROM XX_$year$
WHERE
    ... = ...
ORDER BY ...
'

SET @sql = REPLACE(@sql, '$year$', CAST(@YEAR AS varchar(4))
EXEC(@sql)
0
RadhaKrishnaKiJayaAuthor Commented:
ScottPletcher, OriNetworks, Jeff Darling and JestersGrind,
Thank you very much for your help.  I learned a lot from your posts.  Here is what I am trying to use.  

The main data is in one table only.  But the data stored in column format.  That is the reason I used view xx_2013, xx_2014 etc for easy accessibility of data in row format.  The query is going to be big and complicated.  Here is what I am planning to use as below.

Please let me know if you think it would create any issue.  

Thank you again!

IF @YEAR ='2013'
BEGIN
SELECT  * INTO A FROM xx_2013
END

IF @YEAR ='2014'
BEGIN
SELECT  * INTO A FROM xx_2014
END

select *from A
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RadhaKrishnaKiJayaAuthor Commented:
OriNetworks,
Thank you for your help.  Please read my comments above and let me know if you think it would create any issue in future.

Thank you again!
0
RadhaKrishnaKiJayaAuthor Commented:
Already described.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.