Click here to Skip to main content
15,885,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hii all..

i want to enter data in a temp table . using 2 varchar type query..

my stored procedure is:

the error is:
SQL
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.



SQL
USE [scjdata]
GO
/****** Object:  StoredProcedure [dbo].[alldistt_wise]    Script Date: 05/06/2014 15:54:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[alldistt_wise] 
	
    @Sex VARCHAR (250),
    @DisabiltyType VARCHAR (250),
    @SocialGroup  VARCHAR (250),
    @Minority VARCHAR (250),
    @Age_start VARCHAR (250),
    @Age_End VARCHAR (250)
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
	@FamilyTblName AS VARCHAR (125), 
    @vQuery AS VARCHAR (max), 
    @vQuery1 as varchar(max);
      CREATE TABLE #tblagereoprt
    ( 
       disttname nvarchar(20),
       totalpopulation   nvarchar(20)  
    );
    
    DECLARE @N varchar(3)
    SET @N = '1' 
    WHILE (@N <= 27)
    BEGIN
    SET @ChildTblName = '[' + RIGHT(('0'+@N),2) + 'Child_D' + ']'
    SET @FamilyTblName = '[' + RIGHT(('0'+@N),2) + 'Family_D' + ']'
    
     
      
    SET @vQuery1 = 'select distname as disttname from DIST where distt='+@N+''
    
    SET @vQuery = 'select count(Child.cname) as totalpopulation from ' + @ChildTblName + ' As Child  INNER JOIN ' + @FamilyTblName + ' 
      AS Family  ON Child.barcode=Family.barcode where Child.distt='+@N+''; 
     
    IF(@Sex <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Child.sex = ''' + @Sex + ''''
    END  
    IF(@DisabiltyType <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Child.disability = ''' + @DisabiltyType + ''''
    END 
    IF(@Age_start <> '' and @Age_End <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND (Child.age between ''' + @Age_start + ''' and ''' + @Age_End + ''')'
    END 
    
    IF(@SocialGroup <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Family.social_grp = ''' + @SocialGroup + ''''
    END 
    IF(@Minority <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Family.minority = ''' + @Minority + ''''
    END 
    
    insert into #tblagereoprt
    exec(@vQuery1)
    exec(@vQuery)
   
    SET @N = @N +1
    END
    select * from  #tblagereoprt 
    drop table #tblagereoprt;
  
END


thanks in advance
Posted
Updated 6-May-14 1:26am
v2

1 solution

your table
#tblagereoprt
is accepting two values, But while inserting you are using exec statement of query
@vQuery1
which will give you only one result.

try like this

first save your results in two different variables and insert both at same time.
 
Share this answer
 
Comments
bindash 6-May-14 8:18am    
query is not return single value.. it returns multiple rows..

what do u want to say.. please explain
Darshan.Pa 6-May-14 8:24am    
though it returns multiple values, your table has two parameters, that need to be inserted at the same time.
bindash 6-May-14 8:27am    
please give me syntax .. how it perform.. as example
Darshan.Pa 6-May-14 8:49am    
first thing is your approach used in Stored procedure is very bad. describe what is the expected result and available table structure or data. so can help to modify your SP.

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