September 20, 2002 at 12:16 pm
Is it possible to use dynamic sql in the from clause
select col_1 from @v_table
???
September 20, 2002 at 12:52 pm
Some what. Here is an example:
use pubs
declare @v_table varchar(100)
set @v_table = 'publishers'
declare @cmd varchar (100)
set @cmd = 'select country from ' + @v_table
exec (@cmd)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 20, 2002 at 1:18 pm
That's right. To execute a dynamic sql statement you must put it into a variable and the execute it.
You can also execute the dynamic transaction with EXEC sp_executeSQL 'Dynamic transaction' for better performance.
September 23, 2002 at 10:44 am
?Why would that be better performance?
September 26, 2002 at 12:44 pm
Placing the dynamic sql in a variable is fine if the dynamic sql is short enough to fit. If you need to be able to execute longer Sql than Nvarchar or Varchar will allow, perform the concatenation in the exec clause as its not limited in that way.
such as :
use pubs
declare @v_table varchar(100)
set @v_table = 'publishers'
exec ('select country from ' + @v_table)
I've run into this several times. More often in dynamic statements which return values and use the sp_executeSQL which I define to use NVarchar, but it happens.
I have sql jobs which write sql statements to handle dynamic jobs which require 35,000 characters or more, but are no problem by executing the statements like I showed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply