CODEWITHSUNDEEP
sql.netsql-serverstored-procedures
Vibeshnan Kanagamani
Vibeshnan Kanagamani

Reputation: 7

Create Dynamic Array

I have a table name Tankdet which has two columns TCO and Tanks.

Here is the stored procedure code to return the count of leased, owned and principle tanks:

ALTER PROCEDURE [dbo].[sp_Dash_LeaseInformation]  
AS  
BEGIN  
 DECLARE @leased INT  
 DECLARE @owned INT
 DECLARE @principal INT
  
 SET NOCOUNT ON  
  
 SET @owned = (SELECT COUNT(*) FROM Tankdet WHERE ownleasetank='owned')  
 SET @leased = (SELECT COUNT(*) FROM Tankdet WHERE ownleasetank='leased')   
 SET @principal = (SELECT COUNT(*) FROM Tankdet WHERE ownleasetank='principle') 
 SELECT [Leased]=@leased,[Owned]=@owned,[Principal]=@principal 
  
    SET NOCOUNT OFF  
END

The table looks like this

and it returns the values like,

leased = 4,owned = 4,principle = 7

The values which is used to show the tank count in my frontend.

The problem is here I display the whole count of principle tanks and returns it to my frontend.

Now I have the problem of creating array in getting the Principle Tank count as separate for each TCO, and I have to return it as:

SELECT [Leased]=@leased, [Owned]=@owned, [vibe]=@vibe, [baru]=@baru,[sarath]=@sarath, [karthi]=@karthi, [nth...]=@nth.....

The nth is because the TCO values may get added in future for purpose, and the selected values have to return like:

leased=4,owned=4,vibe=2,baru=3,sarath=1,karthi=1,nth= n......,

Upvotes: 0

Views: 398

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Tables (and resultsets) have a fixed number of columns and a variable number of rows. So simply return

SELECT ownleasetank, COUNT(*) TankCount
FROM Tankdet 
GROUP BY ownleasetank

That will return one row per ownleasetank value along with the count.

Upvotes: 1

Related Questions