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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.