April 21, 2009 at 5:45 am
Hi,
Please run this query in a new window. This will give 2,3 as result. Now i need to update the procedure output (results : 1) to the table as mentioned as (******) in the below query. Is it possible. Is there any way that we can do that?
P.N : NO FUNCTIONS (as the tables might go heavy)
create proc usp_temp111
as
begin
set nocount on
select 1 as Number
end
go
create table #temp(uid int)
insert #temp select 2
insert #temp select 3
select * from #temp
--update #temp set uid = exec usp_temp111 -----*********
drop table #temp
go
drop proc usp_temp111
Thanks in advance
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
April 21, 2009 at 1:45 pm
It really is not clear what you are trying to do. Please review the article I link to in my signature and post some samples that we can work with.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2009 at 3:16 pm
Hi
I confirm Jeffrey. As your sample data are shown the usual answer MUST be a scalar function. Could you please explain what you want to do?
Greets
Flo
April 21, 2009 at 10:25 pm
hi,
try using output variable in usp_temp111
then use a variable in the below query(entered by you), store that output in that variable and update.
🙂
April 22, 2009 at 1:30 am
jchandramouli (4/21/2009)
Hi,Please run this query in a new window. This will give 2,3 as result. Now i need to update the procedure output (results : 1) to the table as mentioned as (******) in the below query. Is it possible. Is there any way that we can do that?
P.N : NO FUNCTIONS (as the tables might go heavy)
Thanks in advance
There are two common means of capturing the output from a stored procedure; into an existing table (INSERT INTO table EXEC proc), and by using output variables as suggested in the post above. Can you explain a little more about what you're trying to do?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 25, 2009 at 10:58 am
Interesting problem. I tried the following options
DECLARE @Z INT
SET @Z = (EXEC USP_TEMP111) ---> this does not work. Sql server throws an error saying incorrect syntax near the keyword EXEC. To me it seems clear that the scalar output of the exec sp should be assignable to a variable.
update #temp
set uid = @Z
I am unable to get the output of the stored procedure in a variable. Can somebody help me understand how to assign output of an sp into a variable. Thanks
Insert into #temp
exec usp_temp111
works perfectly alright though.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 25, 2009 at 1:36 pm
saurabh.dwivedy (4/25/2009)
Interesting problem. I tried the following optionsDECLARE @Z INT
SET @Z = (EXEC USP_TEMP111) ---> this does not work. Sql server throws an error saying incorrect syntax near the keyword EXEC. To me it seems clear that the scalar output of the exec sp should be assignable to a variable.
The return value of a procedure can be assigned to a variable, like this
CREATE PROCEDURE TestReturn
AS
RETURN 42
GO
DECLARE @rc int
EXEC @rc = TestReturn
SELECT @rc -- 42
However I suspect that's not what you want as the return value must be an int and is usually used to signal success or failure. You might be able to use an output parameter
CREATE PROCEDURE TestOutput @OutputValue VARCHAR(10) OUTPUT
AS
SET @OutputValue = 'Hi'
GO
DECLARE @o varchar(10)
EXEC TestOutput @o OUTPUT
SELECT @o -- Hi
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2009 at 9:33 pm
Thank you Gail: As evidenced in countless other posts on this forum, your explanations are crisp and insightful.
I'd like to summarize the learnings and propose further questions, if I may:
1) The output of a procedure cannot be assigned to a user variable using the SET command - ex
SET @Z = [exec procedure] name does NOT work;
I would like to still understand this in a bit more detail - Why does it not work?
Is it because I specifically need to code either a RETURN statement or specify an OUTPUT variable in the procedure?
Because ideally I am invoking the same principle that is generally applied in SQL anywhere...viz "the property of closure", something which forms the basis of nested statements as well (selects within select and so on). May be I am not aware of the exact term (is it the "closure property" or something else) but I hope you get what i am trying to say.
To elaborate a bit more: This is how I read the statement (in accordance with the "closure property")
if Set @z = 10 a straight numerical assignment can work, why can't
set @z = exec procname work?
2) What is the fundamental difference between
exec @x = procname
and
set @x = exec procname. Is there a difference really or should I just take it as syntactical peculiarity that I just need to follow - no questions asked.
I hope you can shed some light and clear my doubts.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 26, 2009 at 2:50 am
saurabh.dwivedy (4/25/2009)
I would like to still understand this in a bit more detail - Why does it not work?
Because it's not valid syntax. Nothing fancier than that.
Is it because I specifically need to code either a RETURN statement or specify an OUTPUT variable in the procedure?
No. I showed you how return and output parameters work. Regardless of what you specify in the procedure, SET variable = Exec Proc does not work. It is not valid SQL Server syntax
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2009 at 2:55 am
Ok. Thanks!
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 26, 2009 at 7:33 pm
saurabh.dwivedy (4/25/2009)
Because ideally I am invoking the same principle that is generally applied in SQL anywhere...viz "the property of closure", something which forms the basis of nested statements as well (selects within select and so on). May be I am not aware of the exact term (is it the "closure property" or something else) but I hope you get what i am trying to say.
To elaborate a bit more: This is how I read the statement (in accordance with the "closure property")
if Set @z = 10 a straight numerical assignment can work, why can't
set @z = exec procname work?
2) What is the fundamental difference between
exec @x = procname
and
set @x = exec procname. Is there a difference really or should I just take it as syntactical peculiarity that I just need to follow - no questions asked.
It doesn't work because it's invalid syntax. Could there possibly be more to it than that? Is sql a mile wide and an inch deep? 🙂 Your asking a question, a good question, about the fundamental nature of sql. And as Joe Celko is fond of saying sql really stands for Scarcely Qualifies as a Language :-). The short answer as to why set @x = exec procname can't work is because at runtime sql doesn't recognize the procedure as anything more than..well syntax. It doesn't recognize the proc as a 'type' like an integer or string. In other words it doesn't realize the result of a procedure as a type at runtime. This is one of the fundamental differences between sql and a relational database. A relational db recognizes the procedure as a variable with a type of what the procedure is supposed to return. A relational system in this case would realize (at runtime) a variable with name procname and (if the procedure returned an integer) and a value with scalar type integer. This is why you can't select from a procedure in sql. You can't select from syntax 🙂 You have to select from something the system recognizes as a type. In a relational system the proc is recognized as a variable with a type of table so you can select from it. These concepts are more fully explored here:
'What is a stored procedure?'
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-server-part-i.html
'The unit test as part of the database'
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-server-part-ii.html
'What does deferred name resolution really mean?'
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-server-part-iii.html
Note that sql functions are a way of faking the idea of a type at runtime. But like procs they are not really recognized by the system as a real type. They don't call it impedance mismatch for nothing 🙂
As for closure it's broadly speaking the idea of pipelining expressions. One expression serving as the input to another. It's easier to understand in a system that has an algebra as opposed to a calculus like sql. Besides sql procs break whatever closure sql has obviously. Closure is a basic idea in a relational system which supports an algebra and types all expressions (ie. tables).
Here is the problem of the op as it appears in a relational system.
create session table temp
{
uid:Integer,
key{uid}
};
//Variables support assignment and that includes tables
//as variables. The type is table{uid:Integer}.
temp:=table{row{2 uid},row{3}};
select temp;
/*
uid
---
2
3
*/
create session operator usp_temp111():Integer
begin
result:=1;
end;
select usp_temp111();//1
//What is the type of usp_temp111()?
select usp_temp111() is Integer; //True, it's a variable of type integer.
update temp set {uid:= usp_temp111()} where uid=2 ;
//Note we can only update 1 row to the value of usp_temp111() because
//otherwise it would cause a primary key violation.
select temp;
/*
uid
---
1
3
*/
best,
steve
April 27, 2009 at 12:59 am
steve dassin (4/26/2009)
saurabh.dwivedy (4/25/2009)
Because ideally I am invoking the same principle that is generally applied in SQL anywhere...viz "the property of closure", something which forms the basis of nested statements as well (selects within select and so on). May be I am not aware of the exact term (is it the "closure property" or something else) but I hope you get what i am trying to say.
To elaborate a bit more: This is how I read the statement (in accordance with the "closure property")
if Set @z = 10 a straight numerical assignment can work, why can't
set @z = exec procname work?
2) What is the fundamental difference between
exec @x = procname
and
set @x = exec procname. Is there a difference really or should I just take it as syntactical peculiarity that I just need to follow - no questions asked.
It doesn't work because it's invalid syntax. Could there possibly be more to it than that? Is sql a mile wide and an inch deep? 🙂 Your asking a question, a good question, about the fundamental nature of sql. And as Joe Celko is fond of saying sql really stands for Scarcely Qualifies as a Language :-). The short answer as to why set @x = exec procname can't work is because at runtime sql doesn't recognize the procedure as anything more than..well syntax. It doesn't recognize the proc as a 'type' like an integer or string. In other words it doesn't realize the result of a procedure as a type at runtime. This is one of the fundamental differences between sql and a relational database. A relational db recognizes the procedure as a variable with a type of what the procedure is supposed to return. A relational system in this case would realize (at runtime) a variable with name procname and (if the procedure returned an integer) and a value with scalar type integer. This is why you can't select from a procedure in sql. You can't select from syntax 🙂 You have to select from something the system recognizes as a type. In a relational system the proc is recognized as a variable with a type of table so you can select from it. These concepts are more fully explored here:
'What is a stored procedure?'
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-server-part-i.html
'The unit test as part of the database'
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-server-part-ii.html
'What does deferred name resolution really mean?'
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-server-part-iii.html
Note that sql functions are a way of faking the idea of a type at runtime. But like procs they are not really recognized by the system as a real type. They don't call it impedance mismatch for nothing 🙂
As for closure it's broadly speaking the idea of pipelining expressions. One expression serving as the input to another. It's easier to understand in a system that has an algebra as opposed to a calculus like sql. Besides sql procs break whatever closure sql has obviously. Closure is a basic idea in a relational system which supports an algebra and types all expressions (ie. tables).
Here is the problem of the op as it appears in a relational system.
create session table temp
{
uid:Integer,
key{uid}
};
//Variables support assignment and that includes tables
//as variables. The type is table{uid:Integer}.
temp:=table{row{2 uid},row{3}};
select temp;
/*
uid
---
2
3
*/
create session operator usp_temp111():Integer
begin
result:=1;
end;
select usp_temp111();//1
//What is the type of usp_temp111()?
select usp_temp111() is Integer; //True, it's a variable of type integer.
update temp set {uid:= usp_temp111()} where uid=2 ;
//Note we can only update 1 row to the value of usp_temp111() because
//otherwise it would cause a primary key violation.
select temp;
/*
uid
---
1
3
*/
best,
steve
Thank you Steve for your insightful explanation. I almost get a feeling that people here thought that I am really hell-bent on having an explanation afterall. In fact I had mentioned that I need an explanation only if one exists else i am content with taking it as a syntactical requirement.
Your explanation however explained the fundamental manner in which Relational Algebra and SQL calculus differ and how it impacts the applicability of the closure property in relation to SQL. Naively enough I used to think that the basis behind SQL was mainly the relational algebra itself and not so much the calculus which is why I asked the question.
Thanks again.
Regards
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply