Solved

ms sql server - run a query from a variable

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

790 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