sql server 2000 stored procedur: how do i stop a process 2

[duchovnick]

Hi,
I'd like my stored procedure to stop processing when a certain condition is not fulfilled. For example, if a table doesn't exist, i want it to show some message and cease processing. Code should be something like:

/*EXEC mysp table1, table2, 9*/
/*DROP PROC mysp*/

CREATE PROCEDURE mysp @table1 VARCHAR(15),@table2 VARCHAR(15),@myNumber VARCHAR(5)

AS
BEGIN TRANSACTION

DECLARE @myDynamicSQL VARCHAR(900)

SET @myDynamicSQL=
'
IF EXISTS (SELECT * FROM sysobjects WHERE name='''+@table1+''')
  SELECT * FROM '+@table1+'
[COLOR=#ff0000]ELSE
'Cannot proceed, table doesnt exist !'
stop running
[/color]

'
EXEC (@myDynamicSQL)

COMMIT
GO

Thanks a lot !

 

[SQLWilts]

Why use dynamic SQL? you dont need to in this instance and it can become very resource hungry.

Anyway,

Use 'Return'

IF EXISTS (SELECT * FROM sysobjects WHERE name='''+@table1+''')
  SELECT * FROM '+@table1+'
ELSE
'Cannot proceed, table doesnt exist !'
Return

 

[SQLWilts]

Oh, BTW, you need to say print, sorry, I missed that

IF EXISTS (SELECT * FROM sysobjects WHERE name='''+@table1+''')
  SELECT * FROM '+@table1+'
ELSE
Print 'Cannot proceed, table doesnt exist !'
Return

 

[duchovnick]

Hi SQLWilts
And thanks.
I dont know any other way to deal with tables' names as parameters thats why i use dynamic SQL. Is there another way to do it?
Your help is usefull and i'm gratefull to you.

 

[SQLWilts]

Sorry, brain abhorration there (and I can't spell )

What I was thinking was that you should use dynamic SQL as little as possible - so for example you could do this (from your previous post)

Create PROCEDURE deleteTables @table1 VARCHAR(10)
AS
BEGIN TRANSACTION


Declare @SQL nVarChar(256)
  Begin
    IF EXISTS (SELECT * FROM sysobjects WHERE name=@table1)
      Begin    
        Set @SQL = 'DROP TABLE ' + @Table1
		Exec sp_executesql @SQL
      End
  End

Commit

That way, you only "drop" to dynamic SQL when you need to - all the rest of the processing is done in the query itself.

Just my pontifications - ignore me if you will

 

[duchovnick]

Hi !
I think this could be a good idea which i'll adopt. Any time i need dynamic SQL i put it between:

Declare @myDynamicSQL1 nVarChar(256)
Begin    
        Set @myDynamicSQL1 = '???????'
        Exec @myDynamicSQL1
End
.
.
Declare @myDynamicSQL2 nVarChar(256)
Begin    
        Set @myDynamicSQL2 = '???????'
        Exec @myDynamicSQL2
End
.
.

Is that correct ?
Thanks !

 

[SQLWilts]

Your exec statement might need

Exec sp_executesql @SQL

 

[duchovnick]

Hi,
and thanks again.

sp_executesql

is a SQL keyword i reckon.

 

[SQLWilts]

stored proc

 

[duchovnick]

Thanks !

 

[SQLDenis]

read this:

http://www.sommarskog.se/dynamic_sql.html

it deal with dynamic SQl (EXEC and sp_executesql ) and why you should be careful because of SQL injections

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLWilts]

Denis,
After duchovnick's comment about him being the only person alowed in the DB here

http://www.tek-tips.com/viewthread.cfm?qid=1333624&page=1

I took it that injection would not be an issue - but its a very good point - assumptions are the mother of all **** ups!

 

[duchovnick]

Hi SQLDenis
Seems every notification of yours is a lesson to me: I dont understand what "SQL injections" means.
And SQLWilts, i also mentioned that i'm a beginner. In fact this is the first time i see how to activate one procedure from another (besides other new things i learned from you and SQLDenis and many more from which i get support).
Cordially thanking you all.

 

[gmmastros]

A quick google search will let you know what a sql injection attack is.

[google]SQL Injection Attack[/google]

Read the first couple articles. Sorry if this causes you nightmares.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[duchovnick]

Hi gmmastros
And thanks !
My nightmares will much worsen the moment you guys stop your support..