Sql query to insert & update

Question

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 28-Sep-21 0:16am

Member 11658469

Updated 12-Oct-21 6:39am

Add a Solution

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

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.

Member 11658469 28-Sep-21 9:44am

Error generating near the line Insert @siteID Exec(@mystring).KIndly help

Member 15329613 28-Sep-21 10:34am

How can I help? I don't know what the error is.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-10-12T06:39:00

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