Ms sqlserver store procedure problem in C#

Question

1.00/5 (2 votes)

See more:

SQL-Server-2016

I Wrote one store procedure in Ms SQL server. In that procedure I used some try catch blocks. If I run that store procedure from SQL server Management studio ,it is running fine.If first try block result error, it is going to other block. But That procedure I am calling from c#. In c# first block of that store procedure result error then it is not going next block of that store procedure. Simply It is showing error. How to solve that

What I have tried:

I googled for solution but unable to find right one

SQL

USE [inventoryDB]
GO
/****** Object:  StoredProcedure [dbo].[clearinv]    Script Date: 05-10-2018 18:42:11 ******/
GO
GO
ALTER PROCEDURE [dbo].[SPNULL]
(
    @errorstat AS INT = 0
)
AS
	WHILE @errorstat = 0
	BEGIN
		BEGIN TRY
			UPDATE suppDB 
			SET ldgrid = 0 
			WHERE ldgrid IS NULL

			ALTER TABLE suppDB 
			ALTER COLUMN [ldgrid] INTEGER NOT NULL

			ALTER TABLE suppDB 
			ADD CONSTRAINT sldgrid  
			DEFAULT 0 FOR ldgridif @@ERROR <> 0 OR @@ERROR = 0

			BEGIN 
				PRINT '0'
			END
		END TRY
		BEGIN CATCH
			PRINT '1'
		END CATCH
	END
	GO

	DECLARE @errorstat AS INT = 0
	WHILE @errorstat = 0
	BEGIN
		BEGIN TRY
			UPDATE suppDB1718 
			SET ldgrid = 0 
			WHERE ldgrid IS NULL
		
			ALTER TABLE suppDB1718 
			ALTER COLUMN [ldgrid] INTEGER NOT NULL
		
			ALTER TABLE suppDB1718 
			ADD CONSTRAINT sldgrid1718  
			DEFAULT 0 
			FOR ldgridif @@ERROR <> 0 OR @@ERROR = 0
		
			BEGIN 
				PRINT '0'
			END
		END TRY
		BEGIN CATCH
			PRINT '2'
		END CATCH
	END
	GO

	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	BEGIN try
		UPDATE suppDB1819 
		SET ldgrid = 0 
		WHERE ldgrid IS NULL

		ALTER TABLE suppDB1819 
		ALTER COLUMN [ldgrid] INTEGER NOT NULL
	
		ALTER TABLE suppDB1819 
		ADD CONSTRAINT sldgrid1819 DEFAULT 0 FOR ldgrid
	END try
	BEGIN CATCH
		PRINT '3'
	END CATCH
	GO
	
	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	IF @errorstat = 0
	BEGIN TRY
		UPDATE suppDB SET jvno = 0 WHERE jvno IS NULL

		ALTER TABLE suppDB 
		ALTER COLUMN [jvno] INTEGER NOT NULL

		ALTER TABLE suppDB 
		ADD CONSTRAINT sjvno DEFAULT 0 FOR jvno
		END try
	BEGIN CATCH
		PRINT '4'
	END CATCH
	GO

	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	IF @errorstat = 0
	BEGIN TRY
		UPDATE suppDB1718 SET jvno = 0 WHERE jvno IS NULL

		ALTER TABLE suppDB1718 
		ALTER COLUMN [jvno] INTEGER NOT NULL

		ALTER TABLE suppDB1718 
		ADD CONSTRAINT sjvno1718 DEFAULT 0 FOR jvno
	END TRY
	BEGIN CATCH
		PRINT '5'
	END CATCH
	GO

	IF @@ERROR <> 0 OR @@ERROR = 0 DECLARE @errorstat AS INT = 0
	IF @errorstat = 0
	BEGIN TRY
		UPDATE suppDB1819 SET jvno = 0 WHERE jvno IS NULL

		ALTER TABLE suppDB1819 
		ALTER COLUMN [jvno] INTEGER NOT NULL

		ALTER TABLE suppDB1819 
		ADD CONSTRAINT sjvno1819 DEFAULT 0 FOR jvno
	END TRY
	BEGIN CATCH
		PRINT '6'
	END CATCH
	GO

Posted 5-Oct-18 4:25am

vijay_bale

Updated 5-Oct-18 22:52pm

#realJSOP

v2

Add a Solution

Comments

Dave Kreskowiak 5-Oct-18 10:40am

Without the seeing the stored procedure code and the C# code calling it, it's impossible for anyone to tell you what's going on.

vijay_bale 5-Oct-18 10:49am

@Dave Kreskowiak

USE [inventoryDB]
GO
/****** Object:  StoredProcedure [dbo].[clearinv]    Script Date: 05-10-2018 18:42:11 ******/

GO

GO

ALTER procedure [dbo].[SPNULL]

(@errorstat as int = 0)

as

while @errorstat = 0

begin

Begin try

update suppDB set ldgrid = 0 where ldgrid is null
ALTER TABLE suppDB ALTER COLUMN [ldgrid] INTEGER NOT NULL
alter table suppDB ADD CONSTRAINT sldgrid  DEFAULT 0 FOR ldgrid

if @@ERROR <> 0 or @@ERROR = 0
begin 
print '0'
end

END try
begin catch
Print '1'
end catch
end
go


declare @errorstat as int = 0

while @errorstat = 0
begin
Begin try

update suppDB1718 set ldgrid = 0 where ldgrid is null
ALTER TABLE suppDB1718 ALTER COLUMN [ldgrid] INTEGER NOT NULL
alter table suppDB1718 ADD CONSTRAINT sldgrid1718  DEFAULT 0 FOR ldgrid

if @@ERROR <> 0 or @@ERROR = 0
begin 
print '0'
end

END try
begin catch
Print '2'
end catch
end
go



if @@ERROR <> 0 or @@ERROR = 0
declare @errorstat as int = 0

BEGIN try

update suppDB1819 set ldgrid = 0 where ldgrid is null
ALTER TABLE suppDB1819 ALTER COLUMN [ldgrid] INTEGER NOT NULL
alter table suppDB1819 ADD CONSTRAINT sldgrid1819  DEFAULT 0 FOR ldgrid

END try
begin catch
Print '3'
end catch
go
if @@ERROR <> 0 or @@ERROR = 0
declare @errorstat as int = 0

if @errorstat = 0
BEGIN try

update suppDB set jvno = 0 where jvno is null
ALTER TABLE suppDB ALTER COLUMN [jvno] INTEGER NOT NULL
alter table suppDB ADD CONSTRAINT sjvno  DEFAULT 0 FOR jvno

END try
begin catch
Print '4'
end catch
go
if @@ERROR <> 0 or @@ERROR = 0
declare @errorstat as int = 0

if @errorstat = 0
BEGIN try

update suppDB1718 set jvno = 0 where jvno is null
ALTER TABLE suppDB1718 ALTER COLUMN [jvno] INTEGER NOT NULL
alter table suppDB1718 ADD CONSTRAINT sjvno1718  DEFAULT 0 FOR jvno

END try
begin catch
Print '5'
end catch
go
if @@ERROR <> 0 or @@ERROR = 0
declare @errorstat as int = 0

if @errorstat = 0
BEGIN try

update suppDB1819 set jvno = 0 where jvno is null
ALTER TABLE suppDB1819 ALTER COLUMN [jvno] INTEGER NOT NULL
alter table suppDB1819 ADD CONSTRAINT sjvno1819  DEFAULT 0 FOR jvno

END try
begin catch
Print '6'
end catch
go

//This is SQL server storeprocedure code

vijay_bale 5-Oct-18 10:55am

@Dave Kreskowiak

C# code

using (SqlCommand cmd = new SqlCommand("SPNULL", con))
           {
               con.Close();
               con.Open();
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.ExecuteNonQuery();
           }

Herman<T>.Instance 5-Oct-18 10:46am

You only googled for a solution? I would debug code in VS and SP in SSMS

vijay_bale 5-Oct-18 10:51am

@digimanus
first I checked in ssms.no problem. in c# I debugged that code also

Herman<T>.Instance 5-Oct-18 10:58am

Why you need the loop? =>
while @errorstat = 0

Why You need to call with that parameter?

If you have no problems in this code you endlessly run this SP!
You keep ALTER TABLE you Column?

Your code makes no sense to me

vijay_bale 5-Oct-18 11:02am

I am creating some tables every time but that time some default values are missing(I Am creating filelds with not null and default values.but default values missing).While inserting some data I am getting error because not null but no default value. So I created this procedure

Eric Lynch 5-Oct-18 15:25pm

You might consider adding some indentation to help both yourself and us match the begin and end tags visually. I simply don't have the time to wade through your code without indentation.

At a quick glance, trying to match your begin/ends, it seems like you may have posted both your stored procedure and a bunch of extraneous scripted code outside your stored procedure. Near as I can tell, your stored procedure seems to end at the first "GO" statement.

Perhaps this is the cause of your problem?

If not, I'd recommend adding some indentation and using the "improve question" button to post only what you beleive to be your stored procedure.

Additionally, the logic in your stored procedure seems very suspect. In general, repeatedly altering tables is not a particularly good idea.

I'm guessing maybe you had some problems with your update and, instead of diagnosing those problems, tried slapping on this ill-advised band-aid?

I'd recommend maybe starting over and asking for help with the original problem...assuming you aren't able to resolve it yourself.

Eric Lynch 6-Oct-18 8:26am

Simply checking, you do understand that your stored procedure ends at the first GO, correct?

The rest is simply scripted SQL that is NOT part of your stored procedure. It might get executed in SSMS, but will never get executed when you call your SP.

To be clear, this is the entirety of your stored procedure:

ALTER PROCEDURE [dbo].[SPNULL]
(
@errorstat AS INT = 0
)
AS
WHILE @errorstat = 0
BEGIN
BEGIN TRY
UPDATE suppDB
SET ldgrid = 0
WHERE ldgrid IS NULL

ALTER TABLE suppDB
ALTER COLUMN [ldgrid] INTEGER NOT NULL

ALTER TABLE suppDB
ADD CONSTRAINT sldgrid
DEFAULT 0 FOR ldgridif @@ERROR <> 0 OR @@ERROR = 0

BEGIN
PRINT '0'
END
END TRY
BEGIN CATCH
PRINT '1'
END CATCH
END

If you want the rest of this to be part of your SP, you'll need to enclose the entirety of the SP (after AS) in a BEGIN/END block and re-factor to eliminate the "GO" statements (inside the SP).

Also, as others have mentioned, the approach you've selected doesn't make much sense.

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)

#realJSOP · Answer 1 · 2018-10-05T22:52:00

0) Allowing an exception to happen to implement some arbitrary form of error handling is just plain bad practice. Exceptions should generally be reserved for unexpected problems.

1) There's no point in repeatedly performing a process if it doesn't need to be done.

2) If there's already data in the database, altering a column's type will cause the data to be removed, so I took that code out.

Try this code:

SQL

--if the default value has not been set
IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB' AND COLUMN_NAME = 'ldgrid') IS NULL
   ALTER TABLE suppDB ADD CONSTRAINT DF_suppDB_ldgrid DEFAULT 0 FOR ldgrid;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1718' AND COLUMN_NAME = 'ldgrid') IS NULL
    ALTER TABLE suppDB1718 ADD CONSTRAINT DF_suppDB1718_ldgrid DEFAULT 0 FOR ldgrid;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1819' AND COLUMN_NAME = 'ldgrid') IS NULL
    ALTER TABLE suppDB1819 ADD CONSTRAINT DF_suppDB1819_ldgrid DEFAULT 0 FOR ldgrid;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB' AND COLUMN_NAME = 'jvno') IS NULL
    ALTER TABLE suppDB ADD CONSTRAINT DF_suppDB_jvno DEFAULT 0 FOR jvno;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1718' AND COLUMN_NAME = 'jvno') IS NULL
    ALTER TABLE suppDB1718 ADD CONSTRAINT DF_suppDB1718_jvno DEFAULT 0 FOR jvno;

IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'suppDB1819' AND COLUMN_NAME = 'jvno') IS NULL
    ALTER TABLE suppDB1819 ADD CONSTRAINT DF_suppDB1819_jvno DEFAULT 0 FOR jvno;

UPDATE suppDB     SET ldgrid = 0 WHERE ldgrid IS NULL;
UPDATE suppDB1718 SET ldgrid = 0 WHERE ldgrid IS NULL;
UPDATE suppDB1819 SET ldgrid = 0 WHERE ldgrid IS NULL;
UPDATE suppDB     SET jvno   = 0 WHERE jvno   IS NULL;
UPDATE suppDB1718 SET jvno   = 0 WHERE jvno   IS NULL;
UPDATE suppDB1819 SET jvno   = 0 WHERE jvno   IS NULL;