Solved

ms sql server - run a query from a variable

Posted on 2014-01-01
4
758 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 13

Expert Comment

by:Koen Van Wielink
ID: 39750684
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
ID: 39750718
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
ID: 39751517
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 13

Accepted Solution

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

786 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