July 30, 2009 at 3:17 am
The only conclusion to all this discussion is that Strored Procedures should be used to call functions...
And in no way your desired result is far.....
Thanks,
Amit
July 30, 2009 at 5:52 am
Amit (7/30/2009)
The only conclusion to all this discussion is that Strored Procedures should be used to call functions...
Not if you read the thread properly, no.
A number of solutions, including dynamic SQL, cursor variables, CLR functions, and extended stored procedures have been put forward.
The lack of posted code samples is probably related to:
(a) The lack of clarity in the original question; and
(b) The fact that pretty much every 'solution' is not going to be pretty or robust; and
(c) There is almost certainly a better way to meet the need - if we knew more about it.
Amit (7/30/2009)
And in no way your desired result is far.....Thanks
That makes no sense at all, so no 'thanks' are necessary.
I find it mildly ironic that you posted no new ideas or code 😛
Paul
July 30, 2009 at 6:01 am
I find it mildly ironic that you posted no new ideas or code
Hello Paul,
When there is no idea about the solution to the given problem i consider it right to not to put any incorrect or misleading code..
Well I do appreciate your efforts in giving solution as well as correcting me..
Thanks AGAIN
Amit
August 3, 2009 at 2:57 pm
Hi ,
By the reading the replies for my question , i agree that My question is not at all clear or may be the incorrect terminology i am using or something else , excuse me for that ,let me try to explain the my problem once again , i hope this time i can....
So,
Suitation is like :
I have a store procedure (sp1: main store procedure) which ..
the coding of the store procedure is like
Create #temp table(a,b,c,d) :- 4 parameters
Insert into #temp table
Exec sp2(a,b,c,d) -----(sp2 is sub store procedure with contains same parameters as which i declare for #temp table )
Select * from #temp
So , here when i executes the sp1(main store procedure) , it work fine .like
it Creates the #temp table , Executes the (sp2 )sub store procedure and insert the Data into #temp table and display the #temp table without any errors .
Ok upto this poin everything works fine
But,If i change the sp2(sub store procedure)like, I added 2 more column to sp2(sub store procedure)
then the parametes for sp2 is (a,b,c,d,e)
/* here i am not changing the body of the store procedure*/
then , I can't run the main store procedure (sp1) successful , right because .. the number of #temp table Columns defined is not Equal to sub store procedure(sp2)
it gives me Error ,
Here , So what can i do to overcome this problem ,
now, i came to know that we will not able to Create a Function which calls the store procedure and insert the data into #temp table ,
Just i need a Code something like ,
it should Automaticlly detects the Coulmns which needed for #temp table from sp2(sub store procedure) and inserts to #temp table
In the above case , It does n't matter how many colums are in sub store procedure(sp2), just the #temp table require 4 Columns ,
it should insert the 4 columns only.
I hope this time ,, Somewhat better
Thanks
John
August 3, 2009 at 2:57 pm
Hi ,
By the reading the replies for my question , i agree that My question is not at all clear or may be the incorrect terminology i am using or something else , excuse me for that ,let me try to explain the my problem once again , i hope this time i can....
So,
Suitation is like :
I have a store procedure (sp1: main store procedure) which ..
the coding of the store procedure is like
Create #temp table(a,b,c,d) :- 4 parameters
Insert into #temp table
Exec sp2(a,b,c,d) -----(sp2 is sub store procedure with contains same parameters as which i declare for #temp table )
Select * from #temp
So , here when i executes the sp1(main store procedure) , it work fine .like
it Creates the #temp table , Executes the (sp2 )sub store procedure and insert the Data into #temp table and display the #temp table without any errors .
Ok upto this poin everything works fine
But,If i change the sp2(sub store procedure)like, I added 2 more column to sp2(sub store procedure)
then the parametes for sp2 is (a,b,c,d,e)
/* here i am not changing the body of the store procedure*/
then , I can't run the main store procedure (sp1) successful , right because .. the number of #temp table Columns defined is not Equal to sub store procedure(sp2)
it gives me Error ,
Here , So what can i do to overcome this problem ,
now, i came to know that we will not able to Create a Function which calls the store procedure and insert the data into #temp table ,
Just i need a Code something like ,
it should Automaticlly detects the Coulmns which needed for #temp table from sp2(sub store procedure) and inserts to #temp table
In the above case , It does n't matter how many colums are in sub store procedure(sp2), just the #temp table require 4 Columns ,
it should insert the 4 columns only.
August 3, 2009 at 2:57 pm
Hi ,
By the reading the replies for my question , i agree that My question is not at all clear or may be the incorrect terminology i am using or something else , excuse me for that ,let me try to explain the my problem once again , i hope this time i can....
So,
Suitation is like :
I have a store procedure (sp1: main store procedure) which ..
the coding of the store procedure is like
Create #temp table(a,b,c,d) :- 4 parameters
Insert into #temp table
Exec sp2(a,b,c,d) -----(sp2 is sub store procedure with contains same parameters as which i declare for #temp table )
Select * from #temp
So , here when i executes the sp1(main store procedure) , it work fine .like
it Creates the #temp table , Executes the (sp2 )sub store procedure and insert the Data into #temp table and display the #temp table without any errors .
Ok upto this poin everything works fine
But,If i change the sp2(sub store procedure)like, I added 2 more column to sp2(sub store procedure)
then the parametes for sp2 is (a,b,c,d,e)
/* here i am not changing the body of the store procedure*/
then , I can't run the main store procedure (sp1) successful , right because .. the number of #temp table Columns defined is not Equal to sub store procedure(sp2)
it gives me Error ,
Here , So what can i do to overcome this problem ,
now, i came to know that we will not able to Create a Function which calls the store procedure and insert the data into #temp table ,
Just i need a Code something like ,
it should Automaticlly detects the Coulmns which needed for #temp table from sp2(sub store procedure) and inserts to #temp table
In the above case , It does n't matter how many colums are in sub store procedure(sp2), just the #temp table require 4 Columns ,
it should insert the 4 columns only.
August 3, 2009 at 2:58 pm
Hi ,
By the reading the replies for my question , i agree that My question is not at all clear or may be the incorrect terminology i am using or something else , excuse me for that ,let me try to explain the my problem once again , i hope this time i can....
So,
Suitation is like :
I have a store procedure (sp1: main store procedure) which ..
the coding of the store procedure is like
Create #temp table(a,b,c,d) :- 4 parameters
Insert into #temp table
Exec sp2(a,b,c,d) -----(sp2 is sub store procedure with contains same parameters as which i declare for #temp table )
Select * from #temp
So , here when i executes the sp1(main store procedure) , it work fine .like
it Creates the #temp table , Executes the (sp2 )sub store procedure and insert the Data into #temp table and display the #temp table without any errors .
Ok upto this poin everything works fine
But,If i change the sp2(sub store procedure)like, I added 2 more column to sp2(sub store procedure)
then the parametes for sp2 is (a,b,c,d,e)
/* here i am not changing the body of the store procedure*/
then , I can't run the main store procedure (sp1) successful , right because .. the number of #temp table Columns defined is not Equal to sub store procedure(sp2)
it gives me Error ,
Here , So what can i do to overcome this problem ,
now, i came to know that we will not able to Create a Function which calls the store procedure and insert the data into #temp table ,
Just i need a Code something like ,
it should Automaticlly detects the Coulmns which needed for #temp table from sp2(sub store procedure) and inserts to #temp table
In the above case , It does n't matter how many colums are in sub store procedure(sp2), just the #temp table require 4 Columns ,
it should insert the 4 columns only.
August 3, 2009 at 2:58 pm
Hi ,
By the reading the replies for my question , i agree that My question is not at all clear or may be the incorrect terminology i am using or something else , excuse me for that ,let me try to explain the my problem once again , i hope this time i can....
So,
Suitation is like :
I have a store procedure (sp1: main store procedure) which ..
the coding of the store procedure is like
Create #temp table(a,b,c,d) :- 4 parameters
Insert into #temp table
Exec sp2(a,b,c,d) -----(sp2 is sub store procedure with contains same parameters as which i declare for #temp table )
Select * from #temp
So , here when i executes the sp1(main store procedure) , it work fine .like
it Creates the #temp table , Executes the (sp2 )sub store procedure and insert the Data into #temp table and display the #temp table without any errors .
Ok upto this poin everything works fine
But,If i change the sp2(sub store procedure)like, I added 2 more column to sp2(sub store procedure)
then the parametes for sp2 is (a,b,c,d,e)
/* here i am not changing the body of the store procedure*/
then , I can't run the main store procedure (sp1) successful , right because .. the number of #temp table Columns defined is not Equal to sub store procedure(sp2)
it gives me Error ,
Here , So what can i do to overcome this problem ,
now, i came to know that we will not able to Create a Function which calls the store procedure and insert the data into #temp table ,
Just i need a Code something like ,
it should Automaticlly detects the Coulmns which needed for #temp table from sp2(sub store procedure) and inserts to #temp table
In the above case , It does n't matter how many colums are in sub store procedure(sp2), just the #temp table require 4 Columns ,
it should insert the 4 columns only.
August 3, 2009 at 3:11 pm
If there are more than 4 columns being returned by the second stored procedure to the first one, how is the first one supposed to know which four it needs?
August 3, 2009 at 3:21 pm
When the sub Store procedures (sp2) executes , it gives ouput of 6 columns ,
but it need to insert 4 Columns insert into #temp table , which depeds on the #temp table Declaration ,
nothing to do with the sp1(main store procedure).. the output result of sp2(sub store procedure ) needed to be inserted in #temp table
and here the condition is like .. the data insertion should be done depend on #temptable and the columns declared which #temp table need .. not all the Columns which i get from sp2(sub store procedure )
August 3, 2009 at 3:23 pm
John Paul (8/3/2009)
When the sub Store procedures (sp2) executes , it gives ouput of 6 columns ,but it need to insert 4 Columns insert into #temp table , which depeds on the #temp table Declaration ,
But how do you know which four columns of the six you need?
August 3, 2009 at 3:25 pm
The #temp table columns 'Declaration' and the substore procedure(sp2) Columns will be the Same ..
August 3, 2009 at 3:37 pm
John Paul (8/3/2009)
The #temp table columns 'Declaration' and the substore procedure(sp2) Columns will be the Same ..
Show us with some actual code. I can't figure out what you are trying to accomplish. You talk about needing 4 columns in the temp table but the sub procedure returns 6 columns. Nothing you have shown so far explains how you know which four columns of the six you need.
August 3, 2009 at 4:12 pm
Here is Code like
/* Actual store procedure */(main store procedure (sp1))
CREATE PROCEDURE spDR_FINAL_INVESTMENTS
@ID char(2),
@Code char(2),
@Class char(4),
@Date datetime,
@ReportID char(2)
As
create #temp table (@ID char(2),@Code char(2),@Class char(4),@Date datetime)
INSERT INTO #Temp table values (@ID,@Code,@Class, @Date)
IF ID in ('04', '07', '01', '012', '035','013','02','98','63','99')
BEGIN
Execute [dbo].[spDR_Investments]@ID , @Code, @Class,@Date
Else
EXECUTE [dbo].[spSR_Investments]@ID,@Code, @Class,@Date
END
I have a requirement to add the 2 more extra columns for sub store procedure spDR_Investments, like @newcol1, @newcol2
But another substore procedure (spSR_Investments) remains same .
So , i am struggling to write a code like
When i executes this code .. so it should execute without any error , here the 2 new colums which comes from sub store procedures should n't be inserted into #temp table
like
INSERT INTO #Temp table values (@ID,@Code,@Class, @Date)
IF ID in ('04', '07', '01', '012', '035','013','02','98','63','99') BEGIN
Execute [dbo].[spDR_Investments]@ID , @Code, @Class,@Date , @newCol1 , @newCol2
Else
EXECUTE [dbo].[spSR_Investments]@ID,@Code, @Class,@Date
END
If i execute this ..error like difference in columns
August 3, 2009 at 4:18 pm
So, the addition of two columns in the stored procedure definition also resulted in the stored procedure returning two addtional columns as well, correct? Are these additional columns also added after the four required columns?
If so, define the temporary table with six columns with the last two being defined as accepting null values. You can then just ignore the last two columns while processing the four columns you do need.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply