Stored Procedure with varchar(max) as parameter

• river1

  SSC-Insane

  Points: 24309

  More actions

  May 25, 2010 at 8:11 am

  #147235

  Hi,

  I have this stored procedure:

  create proc ExecuteScripts (@SQLstat varchar(max))

  as

  begin

  exec SQLstat

  end

  When i try to create this procedure i receive the following warning from SQL Server:

  Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'SQLstat'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

  But SQLstat is a variable that i want to pass, not a table...

  Can someone explain?

  Thank you

• Aashini Shah

  Ten Centuries

  Points: 1129

  More actions

  May 25, 2010 at 8:58 am

  #1172137

  Assuming that you are passing the script to be executed in @SQLStat parameter, your procedure should be:

  create proc ExecuteScripts (@SQLstat varchar(max))

  as

  begin

  exec ( @SQLstat )

  end

  Also check sp_executesql if you need to use dynamic sql.

  Neepa

• river1

  SSC-Insane

  Points: 24309

  More actions

  May 25, 2010 at 9:03 am

  #1172147

  thank you.