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