Stored Proc within an if Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc within an if Statement

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-03-30 : 17:39:37

Hi can somebody help me performing the following operation in some way that it works? Seems sps canot be built inside conditionals.

declare @s as int
set @s=1
------
if(@s=1)
begin
create proc
as
select * from hTerms
go
end
----------

thank you

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-30 : 18:38:00

What are u going to do ?

SPs can be built inside "If", but I cannot see any reason for doing so.
Generally SPs are created only once, may be changed a few times when need to correct some feature or enhance the performance, but rarely creating SPs dynamically.

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 20:37:39

Create Procedure must be the first statement in a query batch.

From BOL

quote:
The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

Why do you want to create procedure inside your query ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-30 : 22:15:01

There are ways to work around this, but they're all bad. Can you explain what you want in a bit more detail?

-PatP

heze
Posting Yak Master

192 Posts

Posted - 2006-03-31 : 05:04:49

Ok Thanks,
I have a large script in which I literally delete and build a database every time I run it. THe problem is that I want to be able to conditiohnally define which objects I want to create according to the needs and was hoping to do it the mentioned way. For example, according to the case, I want to choose between set A constrainint stored procedures and set B also contianing stored procedures of different nature. For my purposes is pointless and confussing to have setA and SetB materialized when we only need one.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-31 : 05:41:55

The create has to be processeded by a go. The go will terminate the batch and so cannot be compiled with the if statement.

There are a few ways round this.
Put the SP creates into a file and run it via osql - the osql execute command can be put inside an if block.
Create all the SPs using dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-31 : 08:32:08

heze,

if u r so interested in that, u can do something as follows


Create   PROCEDURE sp_Creator (@sCondition varchar(100)) AS

declare @sSQL varchar (8000)

	if @sCondition = 'Condition For SP1'
	  Begin	
		-- Write code to drop object before creating it, if needed
		Set @sSQL = 'CREATE PROCEDURE sp_Cond1  AS' + char(13)
		Set @sSQL =  @sSQL +  'begin' + char(13)
		Set @sSQL =  @sSQL +  'Insert into T1 values(''aa'',''bb'')' + char(13)
		Set @sSQL =  @sSQL +  'end' + char(13)
		
		Exec (@sSQL)
	  End

	Else if @sCondition = 'Condition For SP2'
	  Begin	
		-- Write code to drop object before creating it, if needed
		Set @sSQL = 'CREATE PROCEDURE sp_Cond1  AS' + char(13)
		Set @sSQL =  @sSQL +  'begin' + char(13)
		Set @sSQL =  @sSQL +  'Insert into T2 values(''pp'',''qq'')' + char(13)
		Set @sSQL =  @sSQL +  'end' + char(13)
		
		Exec (@sSQL)
	  End
--	........................

Srinika

heze
Posting Yak Master

192 Posts

Posted - 2006-04-01 : 00:27:15

thanks nr and srini,
I think I will evaluate nr's first option since I have too many stored procedures that will very likely be modified and managing them as dynamic sqlin future could be hell.Regarding the osql, I hadnt thought about it but seems a candidate to extend it to other objects as well. Nr,Srini, in general when is it useful to use the osql alternative?
Additionally let me see if I understood the osql thing, by asking the following: Is it like opening the query analizer and executing the sql scripts, only that this time is in the command line?
If the latter is the case, is there a kind of "check syntax" option? i did not see it in the books online, furthermore up to what extent could we talk about saving client side sql licences by replacing the enterprisemanager, query analizer, etc.. with the osql utilities? is the functionality equivalent and practical?

thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-01 : 11:58:12

>> Is it like opening the query analizer and executing the sql scripts, only that this time is in the command line?
Yep a bit like that

>> is there a kind of "check syntax" option?
Don't think so
You can use -b to exit on error, check it in a calling batch which hods a transaction and rolls back on error.
I create the scripts separately and when testing concatenate them into a single file and run using osql. I'll put the concatenate and execute in a batch file so updating becomes saving the changed script then executing the batch file (from query analyser).
see
http://www.nigelrivett.net/SQLServerReleaseControl.htm

>> replacing the enterprisemanager, query analizer, etc.. with the osql utilities?
That's possible - and quite common when using msde.
You can redirect the output to a file and read that file. You could also write a simple application that takes sql executes it and displays the result.
if you are doing a lot of development you really need query analyser.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-01 : 15:11:42

quote:
Originally posted by heze

Ok Thanks,
I have a large script in which I literally delete and build a database every time I run it. THe problem is that I want to be able to conditiohnally define which objects I want to create according to the needs and was hoping to do it the mentioned way. For example, according to the case, I want to choose between set A constrainint stored procedures and set B also contianing stored procedures of different nature. For my purposes is pointless and confussing to have setA and SetB materialized when we only need one.

Thanks

It might be simpler to just create all the procs in one script, and then use a script to drop the ones you don't need.


if @db_version = 1
	begin
	drop procedure MyProc1
	drop procedure MyProc2
	drop procedure MyProc4
	drop procedure MyProc9
	end

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources