Solved

ms sql server - run a query from a variable

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

895 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

15 Experts available now in Live!

Get 1:1 Help Now