SQL2008 and EXEC/CURSOR

[Crazy]

I need to parse a SP, line by line, and changing the output. What I
have now is this SP which I execute (it will search in a view):

declare @Search1 nvarchar(20)
declare @Search2 nvarchar(20)
set @Search1 = 'Ford'
set @Search2 = '3|4'

EXEC SP_MySearch @Model=@Search1, @Motor=@Search2

The above will result in this output:
Model, Motor
Focus, 1.3
Focus, 1.4
Focus, 2.3
Mondeo, 2.3
Mondeo, 3.0
Mondeo, 4.0

What I want to do is _NOT_ to output the above but instead output the
above PLUS additional fields (I have the other info in other
variables):

ID, Type, Model, Motor
1, Car, Focus, 1.3
3, Car, Focus, 2.3
5, Car, Focus, 2.4
6, Car, Mondeo, 2.3
8, Car, Mondeo, 3.0
9, Car, Mondeo, 4.0

I guess I need to do some CURSOR stuff but I can't figure out how to
do this? I have now spent all day trying to find a solution on this
but sadly no luck.

[Erland Sommarskog]

Post by Crazy
...
What I want to do is _NOT_ to output the above but instead output the
above PLUS additional fields (I have the other info in other
ID, Type, Model, Motor
1, Car, Focus, 1.3
3, Car, Focus, 2.3
5, Car, Focus, 2.4
6, Car, Mondeo, 2.3
8, Car, Mondeo, 3.0
9, Car, Mondeo, 4.0
I guess I need to do some CURSOR stuff but I can't figure out how to
do this? I have now spent all day trying to find a solution on this
but sadly no luck.

I'm afraid that I don't really follow. Maybe you could post the code
of the procedure as a start?

My the say, the prefix sp_ is reserved for system procedures, and you
should not use it for your own procedures.

[Crazy]

Post by Erland Sommarskog
I'm afraid that I don't really follow. Maybe you could post the code
of the procedure as a start?

This is all fictive code as I do not want to post my production code -
but it shows my problem pretty good I think.
Think of it in another way - how do I manipulate the data I get back
from an EXEC (the EXEC/SP will return thousands of rows of data)?

Post by Erland Sommarskog
My the say, the prefix sp_ is reserved for system procedures, and you
should not use it for your own procedures.

I know that but that is not the problem here as I am using names which
are not conflicting with any system procedures.

[Erland Sommarskog]

Post by Crazy
Think of it in another way - how do I manipulate the data I get back
from an EXEC (the EXEC/SP will return thousands of rows of data)?

I have an article on my web site that discusses this problem:
http://www.sommarskog.se/share_data.html

[Bob Barrows]

Post by Crazy

Post by Erland Sommarskog
My the say, the prefix sp_ is reserved for system procedures, and you
should not use it for your own procedures.

I know that but that is not the problem here as I am using names which
are not conflicting with any system procedures.

That's only part of the problem. But do what you want.

[Gene Wirchenko]

On Thu, 12 Jan 2012 23:39:48 -0800 (PST), Crazy

Post by Crazy

Post by Erland Sommarskog
I'm afraid that I don't really follow. Maybe you could post the code
of the procedure as a start?

This is all fictive code as I do not want to post my production code -
but it shows my problem pretty good I think.
Think of it in another way - how do I manipulate the data I get back
from an EXEC (the EXEC/SP will return thousands of rows of data)?

Post by Erland Sommarskog
My the say, the prefix sp_ is reserved for system procedures, and you
should not use it for your own procedures.

I know that but that is not the problem here as I am using names which
are not conflicting with any system procedures.

And when a later version of SQL Server does use that name? (You
are setting yourself up for trouble.)

Sincerely,

Gene Wirchenko