Check and Return rows if SQL Server table exists

Question

I am working on an application that involves SQL Server tables. I am writing a query in which I need to check for a table and if the table exists I need to select records from that table and if the table does not exist in that database that I need to select from a different table.

I tried with writing the same with UDF but I am not successful. I am unable to use a stored procedure as I need to get the output to be joined to another table.

Is there any way to do this?

CREATE FUNCTION dbo.udf_school_information 
    (@univ_id INT)
RETURNS @returntable TABLE
(
    univ_id INT,
    school_number INT,
    school_name NVARCHAR(255),
    school_address NVARCHAR(255),
    state NVARCHAR(150),
    district NVARCHAR(100),
    start_date datetime
)
AS
BEGIN
    DECLARE @tbl_exists BIT;

    SET @tbl_exists = ISNULL((SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
                              WHERE Table_Name LIKE '%School'),0)

    IF @tbl_exists = 1      
    BEGIN
        SELECT 
            university_id, school_number, school_name, 
            school_address, school_state, district, school_started_date 
        FROM 
            [dbo].[tbl_school_info] 
        WHERE 
            id = @univ_id
    END
    ELSE
    BEGIN
        ---- My condition if school_ingo table does not exists 
        ---- will be querying another table.
    END

    RETURN;
END;
GO

The above is throwing the error

Msg 444, Level 16, State 2, Procedure udf_site_information, Line 24 [Batch Start Line 15] Select statements included within a function cannot return data to a client.

yeah you need to INSERT INTO @returntable SELECT university_id, ... — linden.phoenix
– linden.phoenix, Commented Aug 28, 2018 at 7:35

Andrey Stukalin · Accepted Answer · 2018-08-28 07:54:09Z

1

You don't receive your result, apparently. That's because you don't insert your dataset in the result table:

insert into @returntable
SELECT university_id,...

Add this and it should work. And change this existence check as well because everyone thinks that the problem is there:)

You should be able to simply use this form

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Feeds]') AND type in (N'U'))
  --abc
ELSE
  -- xyz

The same with the information_schema:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'feeds')
BEGIN
 print 'exists'
END

edited Aug 28, 2018 at 7:54

answered Aug 28, 2018 at 6:55

Andrey Stukalin

6,0042 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zhorov · Accepted Answer · 2018-08-28 07:37:54Z

1

Use OBJECT_ID() with N'U' as object type:

CREATE FUNCTION dbo.udf_school_information (@univ_id INT)
RETURNS @returntable TABLE
(
    univ_id INT,
    school_number INT,
    school_name NVARCHAR(255),
    school_address NVARCHAR(255),
    state NVARCHAR(150),
    district NVARCHAR(100),
    start_date datetime
)
AS
BEGIN
    DECLARE @tbl_exists BIT;

    IF OBJECT_ID(N'[dbo].[tbl_school_info]', N'U') IS NOT NULL SET @tbl_exists = 1
    ELSE SET @tbl_exists = 0;

    IF @tbl_exists = 1      
    BEGIN
       -- Statements
    ELSE
    BEGIN
        -- My condition if school_ingo table does not exists 
        -- will be querying another table.
    END
END;
GO

edited Aug 28, 2018 at 7:37

answered Aug 28, 2018 at 7:05

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Comments

gulshan arora · Accepted Answer · 2018-08-28 07:52:18Z

1

This will work! You need to return data in query as variable. You need to define all variables whichever u want as output . i have changed your example with my dummy tables example. You can use as per your requirements Let me give you one example

ALTER FUNCTION dbo.udf_school_information (@univ_id INT)
RETURNS @returntable TABLE
(
    univ_id INT

)
AS
BEGIN

    declare @tbl_exists bit
    Declare @Outputdata varchar(100)

    IF OBJECT_ID(N'[dbo].[tblprojectresource]', N'U') IS NOT NULL SET @tbl_exists = 1
    ELSE SET @tbl_exists = 0;

    IF @tbl_exists = 1      
    BEGIN
        select @Outputdata =ProjectResourceId from tblProjectResource Where ProjectResourceId =@univ_id
       -- Statements
    END
    ELSE
    BEGIN
        select @Outputdata =ProjectId from tblprojectmaster Where projectid =@univ_id
    END
END;
GO

answered Aug 28, 2018 at 7:52

gulshan arora

4732 silver badges8 bronze badges

2 Comments

Andrey Stukalin Over a year ago

Your code won't probably work as you select into the @outputdata and still not fill @returntable

gulshan arora Over a year ago

Just fill your @returntable at the end. this will work. Itried in local and it is working

Collectives™ on Stack Overflow

Check and Return rows if SQL Server table exists

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related