March 11, 2009 at 1:48 pm
--this is perfect and it works
declare @object sysname,@sql varchar(100)
set @object = 'p_adv_person'
set @sql = 'select substring('''+@object+''',3,1)'
Exec(@sql)
print @sql
--this is a problem
print('DECLARE @object sysname,@sql varchar(100)
SET @object = ''P_adv_person''
set @sql = ''select substring('''''+@object+''''',3,1)''
Exec(@sql)') --why it is not correct?
--Error-------------------------------------------
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@object".
--------------------------------------------
whtz the correct then?
March 11, 2009 at 1:57 pm
Hi
You have had too less quotes after the SUBSTRING. Now it should work:
execute ('
DECLARE @object sysname,@sql varchar(100)
SET @object = ''P_adv_person''
set @sql = ''select substring(''''''+@object+'''''',3,1)''
Exec(@sql)')
Greets
Flo
March 11, 2009 at 2:45 pm
thanks for ur reply i always gets stuck in dynamic quotes
March 11, 2009 at 2:51 pm
[font="Verdana"]Actually, why do you need the quotes around the @object at all?
set @sql = ''select substring(@object,3,1)''
... should work.
[/font]
March 11, 2009 at 2:58 pm
nope--not working u can try
exec('
DECLARE @object sysname,@sql varchar(100)
SET @object = ''P_adv_person''
set @sql = ''select substring(@object,3,1)''
Exec(@sql)')
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@object".
March 11, 2009 at 3:05 pm
[font="Verdana"]Doh! I need to read questions better. No, that won't work because you are doing a subsequent exec(). You could get it to work by using sp_executesql and passing @object as a variable.
exec('
DECLARE @object sysname,@sql nvarchar(100);
SET @object = ''P_adv_person'';
set @sql = ''select substring(@object,3,1)'';
exec sp_executesql @stmt=@sql, @params=N''@object sysname'', @object=@object;'
);
[/font]
March 11, 2009 at 3:12 pm
yeah that makes sense thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply