Today I will explain very small but very useful feature which I have used in the past for the procedures. When I was new to SQL Server I was facing many problems and this was one of them.
We all know user defined functions has limitations that we can not use #temp table or table variables in UDF. Because of that we can not get the data from another function in the UDF.
So to overcome this problem, we can create the procedure in which we get data from another procedure for further manipulation. Question is how to get data from another procedure? You will get the answer of this question in this post.
e.g., suppose procedure ReturnStudentDetail returns the table with two fields “Name” and “City” so how can you get that table in another procedure for further manipulation.
– Create Table
Create table #StudentDetail
(Name varchar (50), City varchar (100))
– Below code will give you data in the procedure
Insert into #StudentDetail
Exec ReturnStudentDetail
This syntax works only if we use create table. You can also use dynamic sqlserver query output for inserting data in #temp table.
– Create Table
Create table #StudentDetail
(Name varchar (50), City varchar (100))
Declare @sql as varchar (500)
Set @sql = ‘Select Name, City from tbl_StudentDetail where ……..’
– Below code will give you data in table
Insert into #StudentDetail
Exec (@sql)
So, this is how you can get the data from another procedure or Dynamic sqlserver query output in Table. Basic thing is whatever the structure of data being returned, you have to create table on basis of that and use that table to get data from procedure or dynamic sqlserver query.
I hope this feature is useful for you in future. Let me know if you find any better way to achieve this.