Can one exec a stored procedure in a case statement in a stored procedure?

Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***No, although if you can convert the proc into a user-defined function, you
could use the UDF.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Colette Horter" <coletten@.gmail.com> wrote in message
news:e67fw6RlGHA.1340@.TK2MSFTNGP02.phx.gbl...
Hi there,
Trying to do a complex query and pulling my hair out!!!!
Can one execute a stored procedure and get a value back from it in a
case statement in a stored procedure? as in:
create procedure .........
..........
.........
...........
select field1,
field2,
field3 = (case @.count when 1 then 50 * 6
else
exec @.count = sprocname @.field6, @.field7),
field11
from tablename
where............
*** Sent via Developersdex http://www.examnotes.net ***|||>> Can one execute a stored procedure and get a value back from it in a CASE stat
ement [sic] in a stored procedure? <<
Learn the basics and life is so easy:
There is no CASE statement in SQL. There is a CASE expression. What
does an expression do? It returns a scalar value of a known data type.
Does a stored procedure return a scalar value of a known data type?
NO! But a scalar function call does!

>From you psuedo-code posting, it looks like you also need to learn is
that rows are not records; fields are not columns; tables are not
files. You might also want to learn Standard SQL's AS for alias
assignments, too. That will make your code portable and readable to
the next guy to maintain it.
I will ignore the remark about pulling out your hair :)|||>>> Does a stored procedure return a scalar value of a known data type?
YES !! The data type is INTEGER.
I mean - really, please read the manual for MICROSOFT SQL SERVER and be
aware of the group you are posting to which is for MICROSOFT SQL SERVER.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150904673.628439.120580@.g10g2000cwb.googlegroups.com...
> Learn the basics and life is so easy:
> There is no CASE statement in SQL. There is a CASE expression. What
> does an expression do? It returns a scalar value of a known data type.
> Does a stored procedure return a scalar value of a known data type?
> NO! But a scalar function call does!
>
> that rows are not records; fields are not columns; tables are not
> files. You might also want to learn Standard SQL's AS for alias
> assignments, too. That will make your code portable and readable to
> the next guy to maintain it.
> I will ignore the remark about pulling out your hair :)
>|||>> The data type is INTEGER [return from T-SQL stored procedure] <<
No, that is a completion flag and it is no more a result data type than
a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
-- because of internal representation, but they are VERY different
domains. Think abstract, not current dialect implementation.
You might not remember the C programming language, but all statements
returned such flags (it was part of the DEC PDP-11 hardware that the
language was based on). If you want a scalar result value, you use a
function.|||Thank you so much! I put it in a function and now my sproc actually
saves!!! Just need to finish it up now. c",)
*** Sent via Developersdex http://www.examnotes.net ***|||> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
WRONG! WILL YOU PLEASE READ THE MANUAL AND ACTUALLY USE THE PRODUCT!!!!!
You can use RETURN to pass back any INTEGER, e.g. RETURN( 1 ) etc...
AGAIN: CHECK THE MANUAL UNDER STORED PROCEDURE!!!!

> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
Yes, I remember - I also remember PL/1, C++ and PASCAL.....
But, have you actually used these languages outside of a book / class room?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150925049.724707.252820@.r2g2000cwb.googlegroups.com...
> No, that is a completion flag and it is no more a result data type than
> a DATETIME is a FLOAT. I can CAST() them -- implicitly or explicitly
> -- because of internal representation, but they are VERY different
> domains. Think abstract, not current dialect implementation.
> You might not remember the C programming language, but all statements
> returned such flags (it was part of the DEC PDP-11 hardware that the
> language was based on). If you want a scalar result value, you use a
> function.
>|||>> - I also remember PL/1, C++ and PASCAL.. But, have you actually used thes
e languages outside of a book / class room? <<
PL/I at Coca Cola World HQ in Atlanta, Pascal at Southern Califrnia
Edison and I nver learned C++.