Jamiex304 Jamiex304 - 9 months ago 41
SQL Question

Using values from Select Statement in a String for a Stored Procedure - MS SQL Server

I am writing a drop Procedure in SQL Server, but have hit a problem, in order to drop an index you must pass the table name in. I have wrote a piece of code to find the table (which works)

set @sqlndex = N'select name from sys.objects where object_id = (select object_id from sys.indexes where name = ''' + @objectname + ''')';


But I cant seem to take the returned value and use it, the code below is the full Procedure part I am working on:

What it should do:

It should print the table name (Testing reason / debugging)

It should then pass the name of the table to the drop string (+@result+)

if @objecttype='index' begin
IF EXISTS (SELECT * FROM sysindexes WHERE name = @objectname)
set @sqlndex = N'select name from sys.objects where object_id = (select object_id from sys.indexes where name = ''' + @objectname + ''')';

execute sp_executesql @sqlndex, @result = @result OUTPUT;
SELECT @result;
print @result;

set @SQL = 'DROP ' + @objecttype + ' [dbo].['+@result+'].['+@objectname+']';
select @SQL;
EXEC (@SQL);
end


Variables I am declaring

DECLARE @sql VARCHAR(MAX);
DECLARE @sqlndex NVARCHAR(MAX);
DECLARE @resultOUT NVARCHAR(MAX);
DECLARE @result NVARCHAR(MAX);
DECLARE @objecttype VARCHAR(MAX);
DECLARE @objectname VARCHAR(MAX);


Any help would be great

Answer Source

Try this:

Instead of

set @sqlndex = N'select name from sys.objects
    where object_id = (select object_id from sys.indexes 
                      where name = ''' + @objectname + ''')';

Write:

declare @result nvarchar(100)
declare @paramdef nvarchar(100)

set @sqlndex = N'select @resultout = name from sys.objects
    where object_id = (select object_id from sys.indexes
                      where name = ''' + @objectname + ''')';
set @paramdef = N'@resultout nvarchar(100) output';
execute sp_executesql @sqlndex, @paramdef, @resultout = @result OUTPUT;

So you'll be able to manage @result value