Stored Procedure with varchar(max) as parameter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Stored Procedure with varchar(max) as...

Stored Procedure with varchar(max) as parameter

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Tuesday, May 25, 2010 8:11 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Post #927496

Aashini Shah

Posted Tuesday, May 25, 2010 8:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:22 AM
Points: 36, Visits: 207

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

Post #927544

river1

Posted Tuesday, May 25, 2010 9:03 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

thank you.

Post #927555

« Prev Topic | Next Topic »

Permissions