conditionally executing sql statement

I need to write .sql file in a dynamic way.
inside the file i need to decide at run time (of the sql script) whether or
not to run a SQL statement that I don't know it's content while dynamically
creating the sql script, this sql statement might include a GO statement
what makes a problem putting it inside an if begin end block.
for example
in my script a runtime check
select @.runStatement = configVal from database at script runtime execution
if @.runStatement =1
begin
-- here comes an unknown sql statement at the time of creating the sql
script that might include a GO command which will break the syntax of the
entire block
-- go <- this here makes a TSQL error for the end command since it
breaks the begin / end block.
end
can someone recomend of an approach for how to solve this?
execute sql is not an option here since the internal SQL statement might be
larget then 4000 nvarchar characters and I can not declarae a @.ntext local
variable
TIA.>> inside the file i need to decide at run time (of the sql script) whether
Under normal circumstances, this is a poor way to write SQL code. The kludgy
workaround is to assign the SQL statement to a variable, replace the tokens
that are not needed and use EXEC or sp_ExecuteSQL to execute it.
The right way can be suggested only if you can explain the overall
situation. Why do you have to resort to such complex approach? Is there a
3rd party tool involved?
Anith|||martin (news.microsoft.com) writes:
> I need to write .sql file in a dynamic way.
> inside the file i need to decide at run time (of the sql script) whether
> or not to run a SQL statement that I don't know it's content while
> dynamically creating the sql script, this sql statement might include a
> GO statement what makes a problem putting it inside an if begin end
> block.
> for example
> in my script a runtime check
> select @.runStatement = configVal from database at script runtime execution
> if @.runStatement =1
> begin
> -- here comes an unknown sql statement at the time of creating the
> sql script that might include a GO command which will break the syntax
> of the entire block
> -- go <- this here makes a TSQL error for the end command since it
> breaks the begin / end block.
> end
>
> can someone recomend of an approach for how to solve this?
> execute sql is not an option here since the internal SQL statement might
> be larget then 4000 nvarchar characters and I can not declarae a @.ntext
> local variable
Are you on SQL 2000 or SQL 2005?
If you are on SQL 2000, I would srtongly recommend that you run the
control loop from a client. It could be very difficult to sort out
from SQL only. It could be a little easier on SQL 2005, since there
you can work with nvarchar(MAX) and you could do the batch splitting
in CLR code.
I echoes Aniths suggestion that you could be better served by telling
us the full story. This could give you better suggestions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||it should support database modifications / upgrades.
the commands are not known at the time of designing the tool that will
execute the statements.
i understand that running each script from a client tool like a VB.NET app
is a good option but is it not possible to run it from sql script file using
some goto label....?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eedlU0VaGHA.1020@.TK2MSFTNGP02.phx.gbl...
> Under normal circumstances, this is a poor way to write SQL code. The
> kludgy workaround is to assign the SQL statement to a variable, replace
> the tokens that are not needed and use EXEC or sp_ExecuteSQL to execute
> it.
>
> The right way can be suggested only if you can explain the overall
> situation. Why do you have to resort to such complex approach? Is there a
> 3rd party tool involved?
> --
> Anith
>|||martin (news.microsoft.com) writes:
> it should support database modifications / upgrades.
> the commands are not known at the time of designing the tool that will
> execute the statements.
> i understand that running each script from a client tool like a VB.NET
> app is a good option but is it not possible to run it from sql script
> file using some goto label....?
Possible and possible. With severe kludges maybe. And it depends on the SQL
Server version.
If the purpose of the tool is run scripts for database changes, I strongly
recommend using a control part in a client language.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql