Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
this my store procedure

i want to stored count values in columns

SQL
create procedure usp_GetPKPopulationDetails
(
	    @Id				    int,
	    @PK_Total_Population	    nvarchar(max)
           ,@PK_Total_Male		    nvarchar(max)
           ,@PK_Total_Female	       	    nvarchar(max)
           ,@PK_Total_Male_Married	    nvarchar(max)
           ,@PK_Total_Female_Married	    nvarchar(max)
           ,@PK_Total_Male_Single	    nvarchar(max)
           ,@PK_Total_Female_Single	    nvarchar(max)
           ,@PK_Total_Dead		    nvarchar(max)
           ,@PK_Total_Male_Dead		    nvarchar(max)
           ,@PK_Total_Female_Dead	    nvarchar(max)
           ,@Month			    date
)
	as
		begin
INSERT INTO [dbo].[tbl_PK_Population]
           ([PK_Total_Population]
           ,[PK_Total_Male]
           ,[PK_Total_Female]
           ,[PK_Total_Male_Married]
           ,[PK_Total_Female_Married]
           ,[PK_Total_Male_Single]
           ,[PK_Total_Female_Single]
           ,[PK_Total_Dead]
           ,[PK_Total_Male_Dead]
           ,[PK_Total_Female_Dead]
           ,[Month])
     VALUES
           (@PK_Total_Population select count(*) from tbl_Vill_Members where activeId=1
           ,@PK_Total_Male select count(*) from tbl_Vill_Members where genderid=1
           ,@PK_Total_Female
           ,@PK_Total_Male_Married 
           ,@PK_Total_Female_Married
           ,@PK_Total_Male_Single 
           ,@PK_Total_Female_Single
           ,@PK_Total_Dead 
           ,@PK_Total_Male_Dead 
           ,@PK_Total_Female_Dead 
           ,getdate())

end


What I have tried:

this my store procedure
create procedure usp_GetPKPopulationDetails
(
	    @Id				    int,
	    @PK_Total_Population	    nvarchar(max)
           ,@PK_Total_Male		    nvarchar(max)
           ,@PK_Total_Female	       	    nvarchar(max)
           ,@PK_Total_Male_Married	    nvarchar(max)
           ,@PK_Total_Female_Married	    nvarchar(max)
           ,@PK_Total_Male_Single	    nvarchar(max)
           ,@PK_Total_Female_Single	    nvarchar(max)
           ,@PK_Total_Dead		    nvarchar(max)
           ,@PK_Total_Male_Dead		    nvarchar(max)
           ,@PK_Total_Female_Dead	    nvarchar(max)
           ,@Month			    date
)
	as
		begin
INSERT INTO [dbo].[tbl_PK_Population]
           ([PK_Total_Population]
           ,[PK_Total_Male]
           ,[PK_Total_Female]
           ,[PK_Total_Male_Married]
           ,[PK_Total_Female_Married]
           ,[PK_Total_Male_Single]
           ,[PK_Total_Female_Single]
           ,[PK_Total_Dead]
           ,[PK_Total_Male_Dead]
           ,[PK_Total_Female_Dead]
           ,[Month])
     VALUES
           (@PK_Total_Population select count(*) from tbl_Vill_Members where activeId=1
           ,@PK_Total_Male select count(*) from tbl_Vill_Members where genderid=1
           ,@PK_Total_Female
           ,@PK_Total_Male_Married 
           ,@PK_Total_Female_Married
           ,@PK_Total_Male_Single 
           ,@PK_Total_Female_Single
           ,@PK_Total_Dead 
           ,@PK_Total_Male_Dead 
           ,@PK_Total_Female_Dead 
           ,getdate())

end
Posted
Updated 27-Jul-20 0:09am
v3
Comments
Garth J Lancaster 27-Jul-20 3:07am    
So what is the problem exactly ? please use Improve question to say what the actual issue is
OriginalGriff 27-Jul-20 3:09am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.
Richard MacCutchan 27-Jul-20 3:37am    
Why are you storing these values? The chances are they will be out of date almost as soon as they are stored.
Otekpo Emmanuel 27-Jul-20 3:41am    
Give full details of what you want and what you are unable to achieve.

1 solution

Quote:
i want to store[d]* count values in columns


* -removed by Maciej Los

You don't!

This is a very bad idea to store the count of records in (a/another) table. The count is changing every time after insertion/deletion of records. This will involve the continuous table updation (by using trigger/s/).

You need to use one of the aggregate functions[^]: COUNT[^] to get count recods meeting the criteria.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900