Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    29

    Unanswered: '@NumericVal IS NOT NULL' seems not to return a boolean.

    I'm trying to ensure that only of the parameters is passed to my stored procedure.

    BOL says that the IS [NOT] NULL operator (language construct?) will return a boolean. An IF statement takes an expression which results in a boolean so I was surprised to find that the below code doesn't parse.

    Code:
    CREATE PROC sp_OneParm(
    	@NumericVal float = null,
    	@StringVal nvarchar(200) = null,
    	@DateVal datetime = null,
    	@BitVal bit = null)
    AS
    	DECLARE @ValCount tinyint
    	SET @ValCount = 0
    	
    	-- Ensure we've only got one update value specified
    	IF @NumericVal IS NOT NULL @ValCount = @ValCount + 1
    	IF @StringVal IS NOT NULL @ValCount = @ValCount + 1
    	IF @DateVal IS NOT NULL @ValCount = @ValCount + 1
    	IF @BitVal IS NOT NULL @ValCount = @ValCount + 1
    	IF @ValCount > 1 RAISERROR ('Only one @*Val paramater may be specified when calling sp_OneParm()', 16, 1)
    
    	-- Other Stuff
    GO
    Am I missing something simple or do I need to restructure my code to achieve the logic I want?

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Sure

    Code:
    IF @NumericVal IS NOT NULL SET @ValCount = @ValCount + 1
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jan 2005
    Posts
    29
    Bugger.
    Thank-you roac.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •