CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jamiex304
Jamiex304

Reputation: 242

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

Upvotes: 1

Views: 537

Answers (1)

Joe Taras
Joe Taras

Reputation: 15399

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

Upvotes: 1

Related Questions