Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
 Posted - 2004-11-23 : 05:22:44
|
| I have a sp with the following format:--dynamic sql statement (which I can't change).Only outputs one record--create temp table--execute dynamic sql statement- insert into table--create cursor to grab recordIs there a way to streamline this process.I have read that it is inefficient to create temp tables/cursor.For example, how could I grab the one record using SET.?At the moment , I've got :CREATE TABLE #foo (results varchar(500))INSERT INTO #fooexec (@str)(Keep in mind , it's only 1 record)DYNAMIC CURSOR SCROLL CURSOR FOR SELECT RESULTS FROM #FOOETC |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-23 : 06:30:14
|
you don't need a cursor to do that.just do declare @results varchar(500)select @results = results from #foo -- OR whichever you like more  set @results = (select results from #foo) select @resultsand voila you got your result in @resultsGo with the flow & have fun! Else fight the flow  |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2004-11-23 : 08:11:40
|
| I've implemented and this works fine.ThanksIs it possible to have something like declare @results varchar(500)set @results = exec (@str)i've tried messing around with this and can't get it to work.Is it just not possible? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-23 : 08:24:26
|
| Why are you trying to put query results into a variable (which you can't do anyway)? Just return the results normally. If you're trying to return one single value, you can use an output variable from a stored procedure. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-23 : 08:42:47
|
| why are you using dynamic sql? Why do you feel you cannot change that?- Jeff |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2004-11-23 : 22:58:49
|
| the reasoning is: that it is a complicated table , which the number of columns is dependant on the number of records. Therefore any given request , does not know the amount of cols |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-23 : 23:10:48
|
quote:
which the number of columns is dependant on the number of records
That makes absolutely no sense at all. I can understand varying columns, but it would have nothing to do with the number of rows returned, unless you're trying to do some kind of cross tab.Can you describe what you are trying to do? WHAT, not how. Don't describe what you can't change. Just provide some more detail about the tables involved and what kind of results you want to get. A sample of the raw data and the desired results would help tremendously. |
|
|
|
|
|
|
dynamic sql/temp table/cursor