• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 907
  • Last Modified:

ms sql server - run a query from a variable

I have this:

----------
---  WRITE A QUERY TO A VARIABLE
Declare @query nvarchar(max)

set @query = 'Select * from person'

---
- EXECUTE THE QUERY  AND WRITE THE RESULT OF THE QUERY TO A VARAIBLE
Declare @results nvarchar(max)
set @results = exec (@query)

-- SAVE THE RESULTS TO A  SEPARATE TABLE
insert into table_results (data) values (@results)


------------------------------

However this is not working out to well .... any ideas?
0
GlobaLevel
Asked:
GlobaLevel
  • 2
1 Solution
 
Koen Van WielinkIT ConsultantCommented:
You can't just set 1 variable as a result of another variable, unless that result is generated from a stored procedure in which a specific output variable has been declared. But in your case there is no need to complicate matters.

Just rewrite your query variable to insert directly into your results table:

Declare		@query nvarchar(max);

Set		@query = 'Insert into table_results
				Select	*
				from	person';
			
Exec	(@query);

Select	*
from	table_results;

Open in new window

0
 
Surendra NathCommented:
You can use the method given by Kv above or else you can use the below one as well..

Declare @query nvarchar(max)
set @query = 'Select * from person'

insert into table_results (data) 
exec (@query)

Open in new window

0
 
GlobaLevelAuthor Commented:
I just want to be clear that I need to get any results from the @Query being run and write that to the results table....that could be errors...columns and rows...column names...the @Query could be any SQL statement : " delete from..." "Update ...." Etc
0
 
Koen Van WielinkIT ConsultantCommented:
Your query is going to return a dataset, provided that the query is properly written and no errors are found during the execution. This result can be inserted into a table with the correct columns, just like a normal select statement would. Error messages are only displayed on the screen during execution and possibly written in the event log, depending on severity. They cannot be captured in variables as far as i know. Not sure what you'd expect to see in your result table for update or delete statements, other than perhaps an audit of which records were updated or deleten. But again, you'd have to specify the exact column names and datatypes when you create your results table and make sure your insert statement matches this. It is not possible to write a generic query that works under all circumstances.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now