Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:
IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END
But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.
Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.
Stu
cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:
IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:
> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END
But procedures are generally permenent object and why are you
interested to create them on the fly?
Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.
If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.
For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.
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|||I am using a Code Generation program that creates a script for the
necessary stored procedures.
I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.
Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> 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|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.
Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?
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

Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:

IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END

But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.

Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.

Stu

cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:

IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:

> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?

You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END

But procedures are generally permenent object and why are you
interested to create them on the fly?

Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.

If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.

For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.

--
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|||I am using a Code Generation program that creates a script for the
necessary stored procedures.

I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.

Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> 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|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.

Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?

--
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

create only sp's during schema initialization

Hey, I've created the indexes and tables and stuff manually. I just need SQL
to create the 3 sp's on each table during Trans Repl. Does it do that or it's
all or nothing kind of situation?
Right now, I manually edited about 20 schema files from snapshot to not drop
and recreate the table. And, it takes time.
Tejas,
for a nosync initialization, if you run sp_scriptpublicationcustomprocs
'publicationname' at the publisher, the results (in text format) are 3
stored procedure creation scripts. These are then run on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||see, when you say, 'no, the subscriber already has the schema and data' even
the data doesn't get transferred over. How would I be able to do it through
replication process? or i have to sue bcp or dts or something EXTERNALLY?
Thank you.
|||Tejas,
now I'm confused I thought you were trying to achieve a nosync
initialization? If not, then the normal initialization process will take
care of the data transfer. Please can you clarify a bit more for me exactly
what you want to achieve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
What had happened is this.
I was asked to script the tables and indexes from the publisher and run them
on the sub. Now, I cannot use the 'yes,initialize the schema' option as it
would overwrite all that. But at the same time, I could not use 'no, the sub
already has the schema and data' option cuz that would not transfer over the
data. That's what I was asking you about.
|||Tejas,
what I don't understand is the point of putting just the schema on the
subscriber. It's standard practice to do a full initialization (schema and
data) or a nosync one (neither). So, if there is no reason for creating the
shema on the subscriber, then I'd do a standard initialization and let it
drop the articles on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I do it the way you asked me to, the replication finishes doing the
schema and most of the data and when it starts doing the indexes, the log
file grows like crazy an i dont have that much space to fulfill the logspace
need. Is there a work around this? And i think it evetually times out. I read
somewhere to increase the querytimeout for this. But still, how about the log
file space?
thank you.
by the way, I tried it the way you had said first
|||Tejas,
if you're struggling for space to host the log file, there's no simple fix
Options include creating a separate log file on another disk, trying
simple recovery mode etc
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)