Stored procedure not setting variable-CodePudding

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

CodePudding user response：

Use an output parameter to pass a scalar value from a stored procedure. The only mildly-tricky part is setting the parameter in dynamic SQL.

CREATE PROCEDURE [dbo].[ColumnHasData]
    @schema NVARCHAR(128),
    @tablename NVARCHAR(128),
    @column NVARCHAR(128),
    @hasData bit output
AS
BEGIN

    DECLARE @FullQuery NVARCHAR (max)=N'
        IF EXISTS
        (
            SELECT *
            FROM '   quotename(@schema)   '.'   quotename(@tablename)   '
            WHERE '   quotename(@column)   ' IS NOT NULL
        )
        set @hasData = CAST(1 AS BIT)
        ELSE 
        set @hasData = CAST(0 AS BIT)
    ';

    EXECUTE sp_executesql @FullQuery, N'@hasData bit output', @hasData = @hasData output;
END

Sample data

use tempdb 
drop table if exists t 
create table t(id int, a int null)
insert into t(id,a) values (1,null)

Usage:

declare @hasData bit
exec ColumnHasData 'dbo','t','id', @hasData output

select @hasdata 

declare @hasData bit
exec ColumnHasData 'dbo','t','a', @hasData output

select @hasdata