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

You would have to put the results of your dynamic sql into a table (persistent, temp or variable). Is there a reason you can't just query against the linkserver? This cursor approach is going to suck the life out of your performance. — Sean Lange, Oct 07 '15 at 19:10
Possible duplicate of [How to get sp\_executesql result into a variable?](http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable) — DeanOC, Oct 07 '15 at 20:08

Andaramis · Accepted Answer · 2015-10-07T21:18:50.477

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

edited Oct 07 '15 at 21:18

answered Oct 07 '15 at 19:20

Andaramis

119
1
4

This works! So in the IF EXISTS, do I do IF EXISTS (SELECT @TableName)? – angelcake Oct 07 '15 at 19:57
also, after IF EXISTS, I need to execute to drop the table EXEC sp_executesql N'EXEC (''DROP TABLE Dbname.''' +@TableName+ ''') AT [linkedserver];' The syntax seems not right cause it doesn't drop the table. Do you have a solution? – angelcake Oct 07 '15 at 20:09
You need chek if the table that you want to drop can be droped. – Andaramis Oct 07 '15 at 21:01
hi angelcake try this new edit, i add some lines for "your magic" – Andaramis Oct 07 '15 at 21:14
Thank you very much Andaramis! – angelcake Oct 08 '15 at 18:22

How to get return value from EXEC sp_executesql

1 Answers1