How do I put 0 in table result and print message in a special column(SQL stored procedure)

Question

1.00/5 (1 vote)

See more:

DECLARE @check INT = CASE
	WHEN @@ROWCOUNT = 0
    THEN 
	select 0 as [result], 'Success' as [message]
	ELSE 
	select 1 as [result], 'Failed' as [message]
	END

Posted 18-Feb-19 18:01pm

CaptainChizni

Updated 21-Feb-19 2:00am

Add a Solution

Comments

Santosh kumar Pithani 19-Feb-19 0:10am

Variable returns only single values not table records.Find my solution
select [result],CASE WHEN [result]=@@ROWCOUNT THEN ' Success ' ELSE 'Failed' END AS [message]
from (values(0),(1)) AS tab([result])

CHill60 20-Feb-19 5:56am

What is the error?

2 solutions

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)

Jörgen Andersson · Answer 1 · 2019-02-21T02:00:00

There is a way to return the data the way you have structured in the question.
So if I adjust CHills procedure just a little bit:

XML

ALTER PROCEDURE [dbo].[Test]
	-- Add the parameters for the stored procedure here
AS
BEGIN
    -- Whatever it is you are doing to get a value in @@ROWCOUNT goes here
    IF @@ROWCOUNT = 0
    BEGIN
    SELECT 0 AS [result], 'Success' AS [message]
    RETURN 0
    END
	ELSE 
    BEGIN
	SELECT 1 AS [result], 'Failed' AS [message]
    RETURN 1
	END
END

CHill60 · Answer 2 · 2019-02-20T00:12:00

There are three ways of returning data from a stored procedure - see Return Data from a Stored Procedure - SQL Server | Microsoft Docs[^]
But your problem is that you are trying to assign more than one thing to a variable, you need to separate that out. E.g. like this

SQL

IF OBJECT_ID('CHtest', 'P') IS NOT NULL  
   DROP PROCEDURE CHtest;  
GO  

CREATE PROCEDURE CHtest(@p_Input char(1), @msg varchar(255) OUTPUT)
AS
BEGIN

	-- Whatever it is you are doing to get a value in @@ROWCOUNT goes here

	IF @@ROWCOUNT = 0 
	BEGIN
		SET @msg = 'Success'
		RETURN 0
	END
	ELSE
	BEGIN
		SET @msg = 'Failed'
		RETURN 1
	END 
END

How do I put 0 in table result and print message in a special column(SQL stored procedure)

2 solutions

Solution 3

Solution 2

Add your solution here

Preview 0