Stored Procedure Return Value

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

I am Having Table Name : Country
I need Stored Procedure for insertion and Updation,check already country name exist.

SQL

create PROCEDURE Country
	(	
		@Id1 int,		
		@name varchar(50),		
		@checkCondType varchar(10),   ---Country
		@tbCond varchar(15)
	)
	AS
	BEGIN
if @checkCondType='Country'
        BEGIN
            if @tbCond='Insert'
            BEGIN
                    if not exists(select * from Country where CoName=@name)
                        BEGIN
                            if exists(select * from Country)
                                BEGIN
                                    select @AutoId=MAX(CoId)+1 from Country
                                END
                            else
                                BEGIN
                                    set @AutoId=1
                                END
                    END
                    ELSE
                        BEGIN
                            insert into Country
                            (
                                CoId,CoName
                                
                            )
                            VALUES
                            (
                                @AutoId,@name
                                
                            )
                         END
            END
            ELSE IF @tbCond='Update'
            BEGIN
                    update Country
                    set
                     CoName=@name where CoId=@Id1
            END
        END

From this Stored Procedure , I need to Return Values to C# page.If Already Country Name Exist return some value,if insertion happen return some value,if updation happen return some value

Posted 3-Dec-12 19:23pm

itsureshuk

Add a Solution

2 solutions

Solution 1

You can define one of your variables as an OUTPUT like so:

@retvalue bit OUTPUT

Set the value like you would any other variable and it'll get returned when the SPROC completes.

I'm not guaranteeing the syntax of this (I copied your pasted code and am adding my suggestion as you requested in a comment):

SQL

create PROCEDURE Country
	(	
		@Id1 int,		
		@name varchar(50),		
		@checkCondType varchar(10),   ---Country
		@tbCond varchar(15),
                @retval varchar(50) OUTPUT
	)
	AS
	BEGIN
if @checkCondType='Country'
        BEGIN
            if @tbCond='Insert'
            BEGIN
                    if not exists(select * from Country where CoName=@name)
                        BEGIN
                            if exists(select * from Country)
                                BEGIN
                                    select @AutoId=MAX(CoId)+1 from Country
                                END
                            else
                                BEGIN
                                    set @AutoId=1
                                END
                    END
                    ELSE
                        BEGIN
                            insert into Country
                            (
                                CoId,CoName
                                
                            )
                            VALUES
                            (
                                @AutoId,@name
                                
                            )
                         SET @retval = 'insert'
                         END
            END
            ELSE IF @tbCond='Update'
            BEGIN
                    update Country
                    set
                     CoName=@name where CoId=@Id1
            SET @retval = 'update'
            END
        END

Something like that...

Posted 3-Dec-12 19:28pm

Expert Coming

Updated 3-Dec-12 20:11pm

v3

Comments

sunandandutt 4-Dec-12 1:52am

Good One. My 5!

itsureshuk 4-Dec-12 1:58am

can u help me where i to write this variable ,where i can return value on above stored procedure code

Patel Shailendra 4-Dec-12 3:17am

@Id1 int,@name varchar(50),@checkCondType varchar(10), @tbCond varchar(15), @retval varchar(50) OUTPUT

exec Country 5,'ABC','XYZ','jjj',?

At ? what to pass in sql server

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)

__TR__ · Accepted Answer · 2012-12-03T22:48:00

Here is an approach. I have modified your stored procedure with the comments indicating what i have done.

create PROCEDURE Country
	(	
		@Id1 int,		
		@name varchar(50),		
		@checkCondType varchar(10),   ---Country
		@tbCond varchar(15)
	)
	AS
	BEGIN
--Declare a variable to identify if its insert or update operation
DECLARE @IsInsert BIT
if @checkCondType='Country'
        BEGIN
            if @tbCond='Insert'
            BEGIN
                    if not exists(select * from Country where CoName=@name)
                        BEGIN
                            if exists(select * from Country)
                                BEGIN
                                    select @AutoId=MAX(CoId)+1 from Country
                                END
                            else
                                BEGIN
                                    set @AutoId=1
                                END
                    END
                    ELSE
                        BEGIN
                            insert into Country
                            (
                                CoId,CoName
                                
                            )
                            VALUES
                            (
                                @AutoId,@name
                                
                            )
                         END
            --Set @IsInsert to true as its an insert
              SET @IsInsert = 1
            END
            ELSE IF @tbCond='Update'
            BEGIN
                    update Country
                    set
                     CoName=@name where CoId=@Id1
            --Set @IsInsert to false as its an Update
              SET @IsInsert = 0
            END

           --RETURN THE @IsInsert value 
           SELECT @IsInsert AS OperationType

        END

You can then use SqlCommand.ExecuteScalar Method[^] to retrieve the value from the stored procedure in your application.
I also noticed that you are manually incrementing the Id value and then inserting the new value into your CoId column. You can use Identity[^] property in SQL server that will automatically generate consecutive numbers as you insert data into your rows. Using Identity is always a better option.
Also looking at your stored procedure it looks like you are incrementing the CoId in the if block and inserting the data into your table in the else block. I think the data may not get inserted. I might be wrong, but just thought will let you know.