Dynamic Query

Question

Post reply

Dynamic Query

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
January 8, 2015 at 8:04 am

#314339

I am trying to do this
I have researched many site stackoverflow, here, social.technet.microsoft.com
SELECT * FROM @tablename

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23009 More actions · Answer 1

In VERY simple terms:

declare @tablename varchar(50) = 'YourTableName'

declare @sql Nvarchar(4000)

set @sql =

N'select * from '+@tablename

exec(@SQL)

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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)[

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

cbrammer1219 (1/8/2015)
I am trying to do this
I 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Neil Burton SSC-Insane Points: 23009 More actions · Answer 4

I should have been clearer about the potential risks, although I'll admit to forgetting completely about QUOTENAME.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 5

If it is on another server how would I reference it, server.db.dbo.tablename

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 6

No there is a few columns and few rows but the tablle name is like this..table1412,table1211,table1210

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

aaron.reese SSChampion Points: 13498 More actions · Answer 8

Careful with that, remember little Bobby Tables. http://bobby-tables.com/
[

The second xkcd cross link of the week!

ScottPletcher SSC Guru Points: 101121 More actions · Answer 9

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 10

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...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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