Re: I need to stop the execution of a query sometimes

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Lance Wynn (lance_wynn_at_N.O.S.P.A.M.hotmail.com)
Date: 01/24/05 

Date: Sun, 23 Jan 2005 20:47:53 -0700

I think the "return" statement may be what you are looking for.
Here's the example from the SQL Server Help:
Examples
A. Return from a procedure
This example shows if no username is given as a parameter when findjobs is
executed, RETURN causes the procedure to exit after a message has been sent
to the user's screen. If a username is given, the names of all objects
created by this user in the current database are retrieved from the
appropriate system tables.

CREATE PROCEDURE findjobs @nm sysname = NULL
AS
IF @nm IS NULL
   BEGIN
      PRINT 'You must give a username'
      RETURN
   END
ELSE
   BEGIN
      SELECT o.name, o.id, o.uid
      FROM sysobjects o INNER JOIN master..syslogins l
         ON o.uid = l.sid
      WHERE l.name = @nm
   END

"Justin Bezanson" <justinbezanson@hotmail.com> wrote in message
news:Oz%233Ag%23$EHA.464@tk2msftngp13.phx.gbl...
Hi,

I have a stored procedure that takes the values of 2 fields in the same
record and puts them into 2 variables
(groupId and userId)

My procedure checks each variable to make sure it is not NULL.(RULE: 1
of the 2 can be NULL, but not both of them)

If both are NULL I need to stop the query execution. I am the Admin and
will have users filling out records and need this check to make sure the
data stays consistant.

Here is the code for my procedure:

CREATE PROCEDURE [dbo].[sp_checkPerm] AS

--//DECLARE VARIABLES
--/////////////////////////////
  DECLARE @userId varchar(10)
  DECLARE @groupId varchar(10)

--//ASSIGN VALUES TO VARIABLES
--/////////////////////////////////////////
  SET @groupId =
  (
    SELECT Sec_Grp_ID FROM webengine.dbo.Permissions
    WHERE Perm_ID = 1 AND Site_ID = 'cbs'
  )

  SET @userId =
  (
    SELECT Sec_User_ID FROM webengine.dbo.Permissions
    WHERE Perm_ID = 1 AND Site_ID = 'cbs'
  )

--//CHECK VALUES OF VARIABLES
--/////////////////////////////////////////
  IF (@groupId = Null) OR (@groupId = '')
    GOTO GRPOUTPUT
  IF (@userId = Null) OR (@userId = '')
    GOTO USEROUTPUT

--//GOTO STATMENTS
--///////////////////////////

GRPOUTPUT:
  print 'Group ID is Null'
USEROUTPUT:
  print 'User ID is Null'
GO

Right now the procedure prints the error to a file but I don't want the
query to execute. Any ideas?

Thanks,

Justin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!