How to get return value from EXEC sp_executesql

Question

How do I get the return value from EXEC sp_executesql @OpenQry so I can check if the value exists in IF EXISTS?

DECLARE @TableName VARCHAR(25)
DECLARE @TD_QUERY NVARCHAR(MAX)

DECLARE CUR_QRY CURSOR FOR
SELECT TABLENAME FROM dbo.tbl_table

OPEN CUR_QRY
FETCH NEXT FROM CUR_QRY INTO @TableName

WHILE @@FETCH_STATUS = 0 
BEGIN

SET  @OpenQry = 'SELECT * FROM OPENQUERY(linkedserver,''SELECT TABLENAME FROM DBC.TABLES WHERE TABLEKIND=''''T'''' AND DATABASENAME=''''dbname'''' AND TABLENAME=''''' + @TableName + ''''''')'

EXEC sp_executesql @OpenQry

IF EXISTS (SELECT @OpenQry)
AND EXISTS (SELECT TableName FROM dbo.table WHERE TableName=@TableName)

FETCH NEXT FROM CUR_QRY INTO @TableName
END
CLOSE CUR_QRY
DEALLOCATE CUR_QRY

Answer 1

you just need put the variable that you want receive de return value before sp_executesql

try tis code and let me know if works

DECLARE @TableName VARCHAR(MAX)
DECLARE @TableNameToDrop VARCHAR(MAX) --NEW
DECLARE @TD_QUERY NVARCHAR(MAX)
DECLARE @OpenQry NVARCHAR(MAX) -- Dont forget to declare this variable
declare @sqldrop nvarchar(max)


DECLARE CUR_QRY CURSOR FOR
SELECT TABLENAME FROM dbo.tbl_table

OPEN CUR_QRY
FETCH NEXT FROM CUR_QRY INTO @TableName

WHILE @@FETCH_STATUS = 0 
BEGIN

SET  @OpenQry = 'SELECT * FROM OPENQUERY(linkedserver,''SELECT TABLENAME FROM DBC.TABLES WHERE TABLEKIND=''''T'''' AND DATABASENAME=''''dbname'''' AND TABLENAME=''''' + @TableName + ''''''')'

EXEC TableNameToDrop  = sp_executesql @OpenQry

/*
IF EXISTS (SELECT @OpenQry)
AND EXISTS (SELECT TableName FROM dbo.table WHERE TableName=@TableName )
*/
--maybe you dont need check if the table name existis, jut try this

IF EXISTS (SELECT TableName FROM dbo.table WHERE TableName=@TableNameToDrop )
BEGIN
    set @sqldrop = 'drop table '+  @TableNameToDrop 
    EXEC sp_executesql @sqldrop
END

FETCH NEXT FROM CUR_QRY INTO @TableName
END
CLOSE CUR_QRY
DEALLOCATE CUR_QRY

Regards