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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored Procedure in Select

Author  Topic 

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2008-02-01 : 06:41:40
Hi,

It is not possible to call Store Procedure in Select. Can anybody tell me the reason behind it?

Thanks in advance,

Mahesh

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-01 : 06:48:24
have a look in BOL for detailed descriptions etc, but to quote quickly...

quote:

...Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression....



what are you trying to do? is it actually a function that you need?

Em
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2008-02-01 : 06:53:02
quote:
Originally posted by elancaster

have a look in BOL for detailed descriptions etc, but to quote quickly...

quote:

...Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression....



what are you trying to do? is it actually a function that you need?

Em




No ... I faced it at an inteview. 1st he asked me, whether it is possible to call procedure in a Select and if either Y / N then why? I could just answer that it is not possible to call SProc in select instead use Function.

So I just wanted to know the reason behind it. Its not possible to do so...agree. But I wish to get the reason.

Thanks in advance,

Mahesh
Go to Top of Page

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-01 : 06:58:36
well, i'm sure someone will come up with a more 'technical' explanation, but i always think of it in terms of SP's being 'actions' rather than 'data'. they don't necessary return results, they're just stored code. so if you can't be sure there is a result to return, you can't 'select' it or select from it.

anyone else...?

Em
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-01 : 10:32:30
You can just select columns from the resultset
Refer point 2
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -