January 8, 2015 at 8:04 am
I am trying to do this
I have researched many site stackoverflow, here, social.technet.microsoft.com
SELECT * FROM @tablename
January 8, 2015 at 8:17 am
In VERY simple terms:
declare @tablename varchar(50) = 'YourTableName'
declare @SQl Nvarchar(4000)
set @SQL =
N'select * from '+@tablename
exec(@SQL)
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 8, 2015 at 8:29 am
BWFC (1/8/2015)
In VERY simple terms:
declare @tablename varchar(50) = 'YourTableName'declare @SQl Nvarchar(4000)
set @SQL =
N'select * from '+@tablename
exec(@SQL)
Careful with that, remember little Bobby Tables. http://bobby-tables.com/
A simple change can prevent that.;-)
declare @tablename varchar(50) = 'YourTableName'
declare @SQl Nvarchar(4000)
set @SQL =
N'select * from '+ QUOTENAME(@tablename)
exec(@SQL)[
January 8, 2015 at 8:29 am
cbrammer1219 (1/8/2015)
I am trying to do thisI have researched many site stackoverflow, here, social.technet.microsoft.com
SELECT * FROM @tablename
Hopefully @Tablename cannot possibly be a table which contains 10 million rows each with 150 columns!
I'd recommend sp_ExecuteSQL over Exec to avoid SQL Injection possibilities.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 8, 2015 at 8:38 am
I should have been clearer about the potential risks, although I'll admit to forgetting completely about QUOTENAME.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 8, 2015 at 8:38 am
If it is on another server how would I reference it, server.db.dbo.tablename
January 8, 2015 at 8:45 am
No there is a few columns and few rows but the tablle name is like this..table1412,table1211,table1210
January 8, 2015 at 8:45 am
As long as you have it defined in your linked servers, you can use the fully qualified name.
Server.DB.Schema.Table
If you're using QUOTENAME(), you'll need to use it for each part of the name.
January 8, 2015 at 3:29 pm
Careful with that, remember little Bobby Tables. http://bobby-tables.com/
[
The second xkcd cross link of the week!
January 8, 2015 at 4:48 pm
Might as well have max flexibility on the number of name levels provided:
DECLARE @tablename varchar(500)
DECLARE @sql varchar(8000)
SET @tablename = 'YourTableName'
--SET @tablename = 'server1.db1..YourTableName'
SET @sql = 'SELECT * FROM ' +
ISNULL('[' + PARSENAME(@tablename, 4) + '].', '') +
ISNULL('[' + PARSENAME(@tablename, 3) + '].', '') +
ISNULL('[' + PARSENAME(@tablename, 2) + '].', 'dbo.') +
'[' + PARSENAME(@tablename, 1) + ']'
--PRINT @sql
EXEC(@sql)
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 8, 2015 at 7:07 pm
declare @server varchar(25) = 'server'
declare @db varchar(50) = 'db'
declare @type varchar(25) = 'dbo'
declare @tablenameCurrent varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY, GETDATE()) as varchar(4)),3,4) + '0' + SUBSTRING(CAST(DATEPART(MM, GETDATE()) as varchar(2)),1,2)
declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)
declare @tablename90 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -2, getdate())) as varchar(2)),1,2)
declare @tablename120 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -3, getdate())) as varchar(2)),1,2)
declare @arnumber varchar(10) = '0000000012'
declare @SQl Nvarchar(4000)
set @SQL =
N'
;WITH ARNOTES
as (
select * from ' + QUOTENAME(@server) + '.' + QUOTENAME(@db) + '.' + QUOTENAME(@type) + '.' + QUOTENAME(@tablenameCurrent)
+ 'UNION ALL ' +
'select * from ' + QUOTENAME(@server) + '.' + QUOTENAME(@db) + '.' + QUOTENAME(@type) + '.' + QUOTENAME(@tablename60)
+ 'UNION ALL ' +
'select * from ' + QUOTENAME(@server) + '.' + QUOTENAME(@db) + '.' + QUOTENAME(@type) + '.' + QUOTENAME(@tablename90)
+ 'UNION ALL ' +
'select * from ' + QUOTENAME(@server) + '.' + QUOTENAME(@db) + '.' + QUOTENAME(@type) + '.' + QUOTENAME(@tablename120) +
')
Select ARNOTES.AR_NUMBER,HD_DATE_TIMEX,ARNOTES.TRANS_NO,ARNOTES.MESSAGE_COUNTER,ARNOTES.MESSAGE_2,ARNOTES.SQL_LAST_UPDATE from ARNOTES where isnumeric(ARNOTES.ar_number) = 1 AND ARNOTES.ar_number = ' + @arnumber
exec(@SQL)
PRINT @SQL
I get an error Conversion failed when converting the varchar value '00000A2869' to data type int. because some genius decided to add alpha characters to arnumber, but what I can't figure out is it is delared a varchar(10) just as it is in the table, any suggestion how to avoid this...
January 9, 2015 at 1:53 am
Comment out EXEC(@sql) and add an extra statement: PRINT @sql. Printing the statement and running the generated sql catches most errors.
Your final SELECT from the CTE:
Select ARNOTES.AR_NUMBER,HD_DATE_TIMEX,ARNOTES.TRANS_NO,ARNOTES.MESSAGE_COUNTER,ARNOTES.MESSAGE_2,ARNOTES.SQL_LAST_UPDATE from ARNOTES where isnumeric(ARNOTES.ar_number) = 1 AND ARNOTES.ar_number = ' + @arnumber
resolves to
Select ARNOTES.AR_NUMBER,HD_DATE_TIMEX,ARNOTES.TRANS_NO,ARNOTES.MESSAGE_COUNTER,ARNOTES.MESSAGE_2,ARNOTES.SQL_LAST_UPDATE from ARNOTES where isnumeric(ARNOTES.ar_number) = 1 AND ARNOTES.ar_number = 0000000012
SQL Server then compares the integer value 12 to ARNOTES.ar_number. You need single quotes around @arnumber for the value to be interpreted as a string.
Something like this:
Select ARNOTES.AR_NUMBER,HD_DATE_TIMEX,ARNOTES.TRANS_NO,ARNOTES.MESSAGE_COUNTER,ARNOTES.MESSAGE_2,ARNOTES.SQL_LAST_UPDATE from ARNOTES where isnumeric(ARNOTES.ar_number) = 1 AND ARNOTES.ar_number = ''' + @arnumber + '''
Use PRINT(@sql) to check the number of quotes.
If you're selecting from a CTE such as this, an alias is a bit unnecessary:
Select AR_NUMBER, HD_DATE_TIMEX, TRANS_NO, MESSAGE_COUNTER, MESSAGE_2, SQL_LAST_UPDATE from ARNOTES where isnumeric(ar_number) = 1 AND ar_number = ''' + @arnumber + '''
Note that SQL Server will perform calculations at a point where it's most efficient to do so: the ISNUMERIC check may well be performed AFTER the filter.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply