Click here to Skip to main content
15,446,510 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Dear All,

<pre>USE [NSH_CAMPDB]
GO
/****** Object:  StoredProcedure [dbo].[nsh_BP_TagActivitywise_CRUD]    Script Date: 26-Sep-21 10:51:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<SREEJITH>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[nsh_BP_TagActivitywise_CRUD]
				(
					@BP_TagNo NVARCHAR(200),
					@BP_TagCategory NVARCHAR(100),
					@BP_TagItems NVARCHAR(100),
					@BP_TagSubActivities NVARCHAR(250),
					@BP_TagActivity_BOQ NVARCHAR(50),
					@BP_TagActivity_IFC NVARCHAR(50),
					@BP_TagActivity_DPRNo NVARCHAR(100),
					@BP_TagActivity_DPRQty NVARCHAR(20),
					@BP_TagActivity_DPRProgressDate NVARCHAR(20),
					@BP_TagActivity_DPRTableName NVARCHAR(100)
				)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @siteId TABLE (siteId NVARCHAR(MAX));
	DECLARE @Tag NVARCHAR(100);
	SET NOCOUNT ON;
	IF NOT EXISTS(SELECT * FROM tb_BP_TagActivityDetails WHERE BP_TagNo=@BP_TagNo AND BP_TagSubActivities=@BP_TagSubActivities)
	BEGIN
    INSERT INTO tb_BP_TagActivityDetails (BP_TagNo,BP_TagCategory,BP_TagItems,BP_TagSubActivities,BP_TagActivity_BOQ,BP_TagActivity_IFC)
	VALUES
	(
		@BP_TagNo,@BP_TagCategory,@BP_TagItems,@BP_TagSubActivities,@BP_TagActivity_BOQ,@BP_TagActivity_IFC
	)

	SET @SQL='INSERT INTO '+ @BP_TagActivity_DPRTableName +' (BP_TagNo,BP_Tag_SubActivities,BP_TagActivity_DPRNo,BP_TagActivity_DPRQty,BP_TagActivity_DPRProgressDate)
	VALUES
	(
		'''+ @BP_TagNo +''','''+ @BP_TagSubActivities +''','''+ @BP_TagActivity_DPRNo+''','''+ @BP_TagActivity_DPRQty +''', '''+ @BP_TagActivity_DPRProgressDate +'''
	)'
	EXEC(@SQL);
	END
	ELSE IF EXISTS(SELECT * FROM tb_BP_TagActivityDetails WHERE BP_TagNo=@BP_TagNo AND BP_TagSubActivities=@BP_TagSubActivities)
	BEGIN
	Declare @mystring varchar(max) ;
    SET @mystring = N'SELECT * from ' + @BP_TagActivity_DPRTableName +' 
	WHERE BP_TagNo='''+ @BP_TagNo +''' AND BP_Tag_SubActivities='''+ @BP_TagSubActivities +''' AND BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+'''';
	INSERT @siteId EXEC (@mystring)
	SET @Tag=(SELECT * FROM @siteId)
		IF EXISTS (SELECT * FROM @siteId)
		BEGIN
		SET @SQL='UPDATE '+ @BP_TagActivity_DPRTableName +'  SET BP_TagNo='''+ @BP_TagNo +''',BP_Tag_SubActivities='''+ @BP_TagSubActivities +''',
		BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+''',BP_TagActivity_DPRQty='''+ @BP_TagActivity_DPRQty +''',BP_TagActivity_DPRProgressDate='''+ @BP_TagActivity_DPRProgressDate +'''
			 WHERE BP_TagNo='''+ @BP_TagNo +''' AND BP_Tag_SubActivities='''+ @BP_TagSubActivities +''' AND BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+''''	
		EXEC(@SQL);
		END		
		ELSE 
		BEGIN
		SET @SQL='INSERT INTO '+ @BP_TagActivity_DPRTableName +' (BP_TagNo,BP_Tag_SubActivities,BP_TagActivity_DPRNo,BP_TagActivity_DPRQty,BP_TagActivity_DPRProgressDate)
		VALUES
		(
			'''+ @BP_TagNo +''','''+ @BP_TagSubActivities +''','''+ @BP_TagActivity_DPRNo+''','''+ @BP_TagActivity_DPRQty +''', '''+ @BP_TagActivity_DPRProgressDate +'''
		)'
		EXEC(@SQL);
		END
	END
END



Above mentioned sp for insert & update datas into the tables.Insert function working fine while updating datas into table not working.Kindly help.

What I have tried:

USE [NSH_CAMPDB]
GO
/****** Object:  StoredProcedure [dbo].[nsh_BP_TagActivitywise_CRUD]    Script Date: 26-Sep-21 10:51:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<SREEJITH>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[nsh_BP_TagActivitywise_CRUD]
				(
					@BP_TagNo NVARCHAR(200),
					@BP_TagCategory NVARCHAR(100),
					@BP_TagItems NVARCHAR(100),
					@BP_TagSubActivities NVARCHAR(250),
					@BP_TagActivity_BOQ NVARCHAR(50),
					@BP_TagActivity_IFC NVARCHAR(50),
					@BP_TagActivity_DPRNo NVARCHAR(100),
					@BP_TagActivity_DPRQty NVARCHAR(20),
					@BP_TagActivity_DPRProgressDate NVARCHAR(20),
					@BP_TagActivity_DPRTableName NVARCHAR(100)
				)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @SQL NVARCHAR(MAX);
	DECLARE @siteId TABLE (siteId NVARCHAR(MAX));
	DECLARE @Tag NVARCHAR(100);
	SET NOCOUNT ON;
	IF NOT EXISTS(SELECT * FROM tb_BP_TagActivityDetails WHERE BP_TagNo=@BP_TagNo AND BP_TagSubActivities=@BP_TagSubActivities)
	BEGIN
    INSERT INTO tb_BP_TagActivityDetails (BP_TagNo,BP_TagCategory,BP_TagItems,BP_TagSubActivities,BP_TagActivity_BOQ,BP_TagActivity_IFC)
	VALUES
	(
		@BP_TagNo,@BP_TagCategory,@BP_TagItems,@BP_TagSubActivities,@BP_TagActivity_BOQ,@BP_TagActivity_IFC
	)

	SET @SQL='INSERT INTO '+ @BP_TagActivity_DPRTableName +' (BP_TagNo,BP_Tag_SubActivities,BP_TagActivity_DPRNo,BP_TagActivity_DPRQty,BP_TagActivity_DPRProgressDate)
	VALUES
	(
		'''+ @BP_TagNo +''','''+ @BP_TagSubActivities +''','''+ @BP_TagActivity_DPRNo+''','''+ @BP_TagActivity_DPRQty +''', '''+ @BP_TagActivity_DPRProgressDate +'''
	)'
	EXEC(@SQL);
	END
	ELSE IF EXISTS(SELECT * FROM tb_BP_TagActivityDetails WHERE BP_TagNo=@BP_TagNo AND BP_TagSubActivities=@BP_TagSubActivities)
	BEGIN
	Declare @mystring varchar(max) ;
    SET @mystring = N'SELECT * from ' + @BP_TagActivity_DPRTableName +' 
	WHERE BP_TagNo='''+ @BP_TagNo +''' AND BP_Tag_SubActivities='''+ @BP_TagSubActivities +''' AND BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+'''';
	INSERT INTO @siteId EXEC (@mystring)
	SET @Tag=(SELECT * FROM @siteId)
		IF @Tag <> ''
		BEGIN
		SET @SQL='UPDATE '+ @BP_TagActivity_DPRTableName +'  SET BP_TagNo='''+ @BP_TagNo +''',BP_Tag_SubActivities='''+ @BP_TagSubActivities +''',
		BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+''',BP_TagActivity_DPRQty='''+ @BP_TagActivity_DPRQty +''',BP_TagActivity_DPRProgressDate='''+ @BP_TagActivity_DPRProgressDate +'''
			 WHERE BP_TagNo='''+ @BP_TagNo +''' AND BP_Tag_SubActivities='''+ @BP_TagSubActivities +''' AND BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+''''	
		EXEC(@SQL);
		END		
		ELSE 
		BEGIN
		SET @SQL='INSERT INTO '+ @BP_TagActivity_DPRTableName +' (BP_TagNo,BP_Tag_SubActivities,BP_TagActivity_DPRNo,BP_TagActivity_DPRQty,BP_TagActivity_DPRProgressDate)
		VALUES
		(
			'''+ @BP_TagNo +''','''+ @BP_TagSubActivities +''','''+ @BP_TagActivity_DPRNo+''','''+ @BP_TagActivity_DPRQty +''', '''+ @BP_TagActivity_DPRProgressDate +'''
		)'
		EXEC(@SQL);
		END
	END
END
Posted
Updated 12-Oct-21 6:39am
Comments
Member 15329613 28-Sep-21 7:42am    
How are we supposed to help? We can't run your code so how can we know what is wrong?
Member 11658469 28-Sep-21 7:59am    
Inserting datas are working while we tried to update the records its not updating.The If Exists check portion
Member 15329613 28-Sep-21 9:06am    
PRINT @SQL and see what is being generated.
Member 11658469 28-Sep-21 9:22am     CRLF
Exec @SQL working correct after that Insert into @siteID table variable while Updating we are getting the error Column name or number of supplied values does not match table definition. So we cant debug to the next loop.
Member 15329613 28-Sep-21 9:31am    
That is a very easy error to fix. Do you understand the error? PRINT @SQL so you can see what is being created and then you can fix it.

1 solution

Your problem is with
SQL
SET @mystring = N'SELECT * from ' + @BP_TagActivity_DPRTableName +'
WHERE BP_TagNo='''+ @BP_TagNo +''' AND BP_Tag_SubActivities='''+ @BP_TagSubActivities +''' AND BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+'''';
You have declared @siteID to be a table with a single column siteID
SQL
DECLARE @siteId TABLE (siteId NVARCHAR(MAX));
But you are trying to insert into it ALL of the columns from the table name in @BP_TagActivity_DPRTableName.

But from the rest of your code that table has at least 5 columns BP_TagNo,
BP_Tag_SubActivities,
BP_TagActivity_DPRNo,
BP_TagActivity_DPRQty
and BP_TagActivity_DPRProgressDate

So change that SELECT to only SELECT the column name that contains the siteID e.g.
SQL
SET @mystring = N'SELECT siteID from ' + @BP_TagActivity_DPRTableName +'
WHERE BP_TagNo='''+ @BP_TagNo +''' AND BP_Tag_SubActivities='''+ @BP_TagSubActivities +''' AND BP_TagActivity_DPRNo='''+ @BP_TagActivity_DPRNo+'''';
(Obviously I do not know what the real column name is)

This is a classic example of why you should avoid using SELECT * and instead list the columns you need in the order you need them
 
Share this answer
 
v2

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