Site icon Pets Blog

Stored procedure not setting variable

Stored procedure not setting variable

I have tried but do not understand what is wrong with my code. I am trying to set a variable within a stored procedure calling a second stored procedure but as I know the result I know that the variable is not set correctly. Can anyone say where is my mistake.

CREATE PROCEDURE [dbo].[ColumnHasData]
    @scheme VARCHAR(10),
    @tablename VARCHAR(30),
    @column VARCHAR(50)
AS
BEGIN

    DECLARE @FullQuery NVARCHAR (max)=N'
        IF EXISTS
        (
            SELECT *
            FROM ' + @scheme + '.' + @tablename + '
            WHERE [' + @column + '] IS NOT NULL
        )
        SELECT CAST(1 AS BIT)
        ELSE 
        SELECT CAST(0 AS BIT)
    ';

    EXECUTE sp_executesql @FullQuery;
END

CREATE PROCEDURE [dbo].[me_delete_column]
AS
BEGIN

    Declare @noDataPresent bit
    
    exec @noDataPresent = [different_db].[dbo].[ColumnHasData] 'int' ,'table1' ,'column1'

    print @noDataPresent

    RETURN
    IF(@noDataPresent=0)
    begin
        --drop column
    end
    
END

ColumnHasData is on one database

me_delete_column is on a second database

I am trying to set variable @noDataPresent executing ColumnHasData

Even though I know the result should be 1 the print @noDataPresent returns 0 so something is wrong.

What is wrong and why my variable is not setting? The SQL Server is version 15.0.4102.2

Exit mobile version