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
Even though I know the result should be
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