?
Solved

making a text from a select statement to execute

Posted on 2011-11-01
6
Medium Priority
?
228 Views
Last Modified: 2012-05-12
how can you execute a text returned from a select statement?
0
Comment
Question by:25112
  • 3
  • 2
6 Comments
 
LVL 5

Author Comment

by:25112
ID: 37063384
just to test, i tried

exec 'select '''select count(*) from sys.tables''''

but that won't work
0
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 37063434
can you provide some input and output required??
0
 
LVL 13

Expert Comment

by:dwkor
ID: 37063649
Something like that (in general) will show you how to execute sql dynamically.
declare
	@TxtSql nvarchar(max)
	
select @TxtSql= query_text_column from dbo.MyTable where CommandId = 1000

sp_executesql @TxtSql

Open in new window


Never need to say that this is dangerous thing from the multiple standpoints. First, it's security risk - think about sql injection and all other interesting issues. Second, dynamic sql introduces recompilations, blows up plan cache, etc.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Author Comment

by:25112
ID: 37063985
thanks for the warning.. what if it is used only once in a while, and not public.. in the context of the below..

right now, it gives select statements which are copied and executed separately.. the users want it all done at once..
use RASP2
go
create function dbo.getColumnNames(@tablecatalog sysname, @tableschema sysname, @tablename sysname)
returns varchar(8000) -- or max for 2005+
as
begin
declare @vc varchar(8000) -- or max for 2005+
select @vc = coalesce(@vc + ',', '') + column_name
from information_schema.columns
where table_name = @tablename
	and table_schema=@tableschema
	and table_catalog=@tablecatalog
order by ordinal_position
return @vc
end
go
select 'insert into '+ table_catalog+'.'+table_schema+'.'+table_name +
' select * from RASP3.'+table_schema+'.'+table_name
 from information_schema.tables where table_catalog+'.'+table_schema+'.'+table_name
not in (SELECT table_catalog+'.'+table_schema+'.'+table_name TableName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMNPROPERTY(object_id(quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1)

(SELECT 'set identity_insert '+table_catalog+'.'+table_schema+'.'+table_name + ' on '+
 'insert into '+ table_catalog+'.'+table_schema+'.'+
table_name +'( ' + dbo.getColumnNames(table_catalog, table_schema, table_name) + 
') select * from RASP3.'+table_schema+'.'+table_name+
' set identity_insert '+table_catalog+'.'+table_schema+'.'+table_name + ' off'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
COLUMNPROPERTY(object_id(quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1)


drop function dbo.getColumnNames

Open in new window

0
 
LVL 13

Accepted Solution

by:
dwkor earned 2000 total points
ID: 37064212
Your code would not work on SQL 2000 because of Information_Schema view. I've modified it a bit with SQL05/08 constructs.


alter function dbo.getColumnNames(@tablecatalog sysname, @tableschema sysname, @tablename sysname)
returns table
as
return
(
	select 
		STUFF(
			convert(nvarchar(max),
				(	
					select 
						',[' as [text()]
						,column_name as [text()]
						,']' as [text()]
					from information_schema.columns
					where table_name = @tablename
						and table_schema=@tableschema
						and table_catalog=@tablecatalog
					order by ordinal_position
					for xml path('')
				)
			),1,1,'') as Sql
)		
go
	
declare
	@Sql nvarchar(max)
	
;with SQLText(SqlText)
as
(
	SELECT 
		N'set identity_insert ' + i.TABLE_CATALOG +N'.'+i.TABLE_SCHEMA+N'.'+i.TABLE_NAME + N' on ' +  
		'insert into '+ i.TABLE_CATALOG+N'.'+i.TABLE_SCHEMA+N'.'+
		i.TABLE_NAME +N'( ' + cn.Sql + N') select ' + cn.Sql + N' from RASP3.'+I.TABLE_SCHEMA+'.'+i.TABLE_NAME+
		N' set identity_insert '+i.TABLE_CATALOG+N'.'+I.table_schema+N'.'+I.table_name + N' off; ' 
	FROM 
		INFORMATION_SCHEMA.COLUMNS i
		cross apply 
			dbo.getColumnNames(table_catalog, table_schema, table_name) cn
	WHERE 
	COLUMNPROPERTY(object_id(quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
),
AllInOne(SqlText)
as
(
	select 
			convert(nvarchar(max),
				(	
					select 
						SqlText as [text()]
					from SQLText
					for xml path(''), type
				)
			)
)
select @Sql = N'begin tran ' + SqlText + N' commit' from AllInOne

print @Sql
-- uncomment it
--exec sp_executesql @Sql

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 37064639
dwkor, you said "Your code would not work on SQL 2000 because of Information_Schema view"
ist that part of sql 2000 also? http://msdn.microsoft.com/en-us/library/aa933204%28v=sql.80%29.aspx

in the new code do you do the" set identity_insert" for both tables with and without identity  columns? is that no problem?

also, is it possible to put the individual statements on separate lines?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question