Solved

ms sql server - run a query from a variable

Posted on 2014-01-01
4
719 Views
Last Modified: 2014-01-03
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
Comment
Question by:GlobaLevel
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
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
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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
 
LVL 10

Author Comment

by:GlobaLevel
Comment Utility
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
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now