May 11, 2012 at 6:13 pm
Hello All,
Is there a better (or more elegant ) way of executing the output of the output of the Stored Proc?
In other words I am using a lot of Dynamic SQL of different levels in my process, so I have a one Stored Proc which generates an output.
Than I am taking that (first) ouput and executes it.
Once executed, I am taking the 2nd output and executes it.
And that will give me desired result.
So far I am using the following which works fine for me, since it affect only few rows, but was wondering if there is a better way to implement:
sqlcmd -SServer -E -vDBNAME=Mydatabase -q"EXECUTE MystoredProc" -oC:\Output.txt
sqlcmd -SServer -E -vDBNAME=Mydatabase -iC:\Output.txt -oC:\Output1.txt
sqlcmd -SServer -E -vDBNAME=Mydatabase -iC:\Output1.txt -oC:\Output2.txt
C:\Output2.txt just showing me how many records were affected total and I am not using it anywhere, so basically I don't need it to generate it.
Thank you,
May 14, 2012 at 5:41 am
...
Is there a better (or more elegant ) way of executing the output of the output of the Stored Proc?
...
I can't think of more elegant way of executing the output of the output of the Stored Proc than not doing it at all.
However if it's not the option, whatever you did looks fine...
May 14, 2012 at 12:45 pm
What exactly are you trying to accomplish by having a procedure generate code so you can execute it on the same server? In your example all your servers are the same so what is the point of writing the procedure's results to a file? You could just as easily execute the procedure in a wrapper proc, capture the results to a temp table and then executes them without doing another round trip.
That said, the entire design smells bad :sick:
Would you like some help redesigning your process?
What exactly is this process doing and why do you think it necessary to have dynamic SQL generate more code that is returned as a resultset?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 1:16 pm
I cannot say for sure if it would be better for you, or by your criteria, but here[/url] is stored procedure that I wrote several years ago, that does this in spades. In fact, one of the examples shows how it can call itself, consuming it's own output.
It basically uses SELECT..INTO.. to make temp tables with the output of prior queries, REPLACing strings with parameters as it goes. Then it reassemble the temp table's text into a new string and re-executes that as dynamic sql.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 1:29 pm
RBarryYoung (5/14/2012)
I cannot say for sure if it would be better for you, or by your criteria, but here[/url] is stored procedure that I wrote several years ago, that does this in spades. In fact, one of the examples shows how it can call itself, consuming it's own output.It basically uses SELECT..INTO.. to make temp tables with the output of prior queries, REPLACing strings with parameters as it goes. Then it reassemble the temp table's text into a new string and re-executes that as dynamic sql.
A wedge-case. For database administration where we have to iterate over metadata I am all for using techniques like you show in your article. However I contend that the technique should be used sparingly in application code, if at all.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 1:36 pm
opc.three (5/14/2012)
RBarryYoung (5/14/2012)
I cannot say for sure if it would be better for you, or by your criteria, but here[/url] is stored procedure that I wrote several years ago, that does this in spades. In fact, one of the examples shows how it can call itself, consuming it's own output.It basically uses SELECT..INTO.. to make temp tables with the output of prior queries, REPLACing strings with parameters as it goes. Then it reassemble the temp table's text into a new string and re-executes that as dynamic sql.
A wedge-case. For database administration where we have to iterate over metadata I am all for using techniques like you show in your article. However I contend that the technique should be used sparingly in application code, if at all.
Oh I agree, Administrative/operational use is what I intended it for. Isn't that what this question is about? I haven't seen many legit uses for SQLCMD in OLTP.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 1:43 pm
RBarryYoung (5/14/2012)
opc.three (5/14/2012)
RBarryYoung (5/14/2012)
I cannot say for sure if it would be better for you, or by your criteria, but here[/url] is stored procedure that I wrote several years ago, that does this in spades. In fact, one of the examples shows how it can call itself, consuming it's own output.It basically uses SELECT..INTO.. to make temp tables with the output of prior queries, REPLACing strings with parameters as it goes. Then it reassemble the temp table's text into a new string and re-executes that as dynamic sql.
A wedge-case. For database administration where we have to iterate over metadata I am all for using techniques like you show in your article. However I contend that the technique should be used sparingly in application code, if at all.
Oh I agree, Administrative/operational use is what I intended it for. Isn't that what this question is about? I haven't seen many legit uses for SQLCMD in OLTP.
Good point. The OP did not specify but I was thinking in terms of whether the OP was trying to use this in batch processing, ETL or the like. Questions about how to do backflips with dynamic SQL many times lead to broken data models, which is what made me curious.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 4:39 pm
Thank you guys,
I am using that purely for the Admin support, when 'unknown' number of jobs of a certain type are running during maint window.
Those jobs were created dynamically by using some logic. It can be one job or 20 jobs.
And based on job(s) outcome: running, failed, completed, I will need to perform another action(s).
That pretty much it.
May 15, 2012 at 2:27 pm
I am not picking up on the need to use dynamic sql.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply