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 |
|
hethy
Starting Member
8 Posts |
 Posted - 2002-08-16 : 09:45:40
|
Hello, In a stored procedure "SP_masterproc" I'm trying to set a local variable to the result of a stored procedure "SP_nestedproc", and am coming up with this error:Invalid column name 'SP_nestedproc'.Here are a couple lines from SP_masterproc, including one that tries to assign the local variable to the result of SP_nestedproc:DECLARE @local_variable varchar(500)SET @local_variable=(SP_nestedproc)When I highlight just the part SP_nestedproc in Query Analyzer and execute it, it returns one row (which has one column), of varchar type.Any help would be greatly appreciated -- is this just not possible? I looked it up in SQL Server help but all it says is that you can set a local variable to an expression or cursor. I don't need a cursor, since I'm only returning one row/col. About the expression, in SQL Server help it defines an expression as "any valid Microsoft® SQL Server™ expression."  Meanwhile, in my master proc I have no problems when it's a select statement instead of a procedure name:e.g. DECLARE @local_variable varchar(500)SET @local_variable=(SELECT my_varchar_field FROM mytable where my_varchar_field_ID#=1) |
|
|
MuffinMan
Posting Yak Master
107 Posts |
|
|
hethy
Starting Member
8 Posts |
Posted - 2002-08-16 : 10:52:27
|
| Thanks so much for the response! I think I'm still doing something wrong though... My nested proc *has* my_varchar_field as an OUTPUT parameter. Here's what the nested proc looks like:CREATE PROC SP_nestedproc (@my_varchar_field AS varchar(500) OUTPUT)AS...blah blah blah...SET @my_varchar_field='Content for this output variable'SELECT @my_varchar_field my_varchar_field FROM mytable where my_varchar_field_ID#=1The part I highlighted in red is basically a garbage query that I'm running just so I can set the value of @my_varchar_field to a "column" called "my_varchar_field" that I can call from ASP. BTW, I *can* call rs("my_varchar_field") fine, from ASP. I just can't seem to SET @local_variable from my master proc "SP_masterproc" to the value of my_varchar_field. When I try to compile SP_masterproc it is still sayingInvalid column name 'SP_masterproc'.TIA |
 |
|
|
MuffinMan
Posting Yak Master
107 Posts |
|
|
hethy
Starting Member
8 Posts |
Posted - 2002-08-16 : 13:06:58
|
Thank you so much!!! I did get it to do what I wanted. |
|
|
|
|
|
|
Can you set a local variable to result of SP?