0

I was wondering if it was possible to this:
example

if exists(Select * From table)
    select item from table
    set returnValue = 1
Else
    set returnValue = 0

Or if there any other way of doing this this

4
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by mail2saion
0

If this is stored proc, it is sure simple.

SET @returnValue = 0
    SELECT @returnValue = 1, item FROM TABLE
-- @returnValue = 1 means there were records in the table

if you want this as part of one query, I would like to know how you are using the query? For ex. in c#, or VB or ASP etc. In that case, you can look for record count returned (zero or more).
Hope this helps. If helps, close the thread. Thanks.

0
--Drop our test stuff
IF OBJECT_ID('dbo.sp_Test1', 'P') IS NOT NULL DROP PROCEDURE dbo.sp_Test1
IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
CREATE TABLE #Table
(
  Item varchar(10)
)
GO

--Create a procedure
CREATE PROCEDURE dbo.sp_Test1 
WITH EXECUTE AS CALLER AS
BEGIN
  Declare @retValue int
  
  IF (OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL) AND EXISTS (Select * From #Table)
  BEGIN
    Select item From #Table
    Set @retValue = 1
  END ELSE
  BEGIN
    Set @retValue = 0
  END
  
  RETURN @retValue
END

GO

--First run
Declare @res int
exec @res = dbo.sp_Test1 
Print 'First run (no records) returned: ' + Cast(@res as varchar)

GO

Insert Into #Table (Item) Values ('Item 1')

GO

--First run
Declare @res int
exec @res = dbo.sp_Test1 
Print 'Second run (1 record) returned: ' + Cast(@res as varchar)

Results in:

First run (no records) returned: 0

(1 row(s) affected)

(1 row(s) affected)
Second run (1 record) returned: 1
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.