SQL Server Performance

Check if passes value is integer

Discussion in 'General Developer Questions' started by rasha, Sep 14, 2004.

  1. rasha New Member

    Hello,<br /><br />I have a stored procedure that has a prameter of varchar datatype called @MemberMaskId.<br />The possible values passed to the proc through this prameter are either of a string data type or integer datatype.<br />I will do some processing inside the sp and at the end I need to put the passed value in an integer output parameter called @OutMemberId<br /><br />I need to check whether the passed value is integer or not. How can I do this inside the sp.<br /><br /><pre><br />Alter Procedure dbo.test<br /><br />@MemberMaskId nvarchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,<br />@OutMemberId int output<br /><br />As<br /><br /> SET XACT_ABORT ON<br /> SET NOCOUNT ON<br /> BEGIN<br /> Declare @Exists Int -- Return Value<br /> ,@myERROR int -- Local @@myErrorOR<br /> ,@myRowCount int -- Local @@myRowCount<br /> ,@MemId Int<br /> ,@IdLen Int<br /> ,@CharPos Int<br /><br /> Set @CharPos = CHARINDEX('-', @MemberMaskId)<br /><br /> If @CharPos &gt; 0 -- If the entered Member id is the mask one ,<br /> Begin<br /> -- Make sure that Mask Code (Branch + Id ) exists<br /> -- This is because a member with mask code 1-2 may exits<br /> -- and if you type 3-2, still a member with id 3 exists but his full <br /> -- code is written wrong because his branch is 1 not 2 <br /> If Exists (SELECT * from members<br /> where MaskMemberCode = @MemberMaskId<br /> )<br /> -- Thus Get member integer id out of Member Mask Code<br /> Set @IdLen = Len(@MemberMaskId)<br /> Set @MemId = Substring (@MemberMaskId , (@CharPos + 1),(@IdLen - @CharPos) )<br /> Set @MemId = Cast(@MemId as int)<br /> Select @OutMemberId = @MemId<br /> End<br /><br /> Else<br /><br /> Begin<br /> -------------------------------------------<br /> ---The error occurs at the following line if the passed value of<br /> ---@MemberMaskId is of atring data type<br /> ---I need to put a check whether it is integer or not<br /> ---If it is integer I will select it in the output param<br /> ===If it is string then I will set it to Null<br /> ---The @@error did not succeed to solve the problem<br /> -------------------------------------------<br /> Set @MemId = Cast(@MemberMaskId as int)<br /> Select @myError = @@Error <br /> If @myError &gt; 0 <br /> Select @OutMemberId = Null<br /><br /> Else<br /> Select @OutMemberId = @MemId<br /><br /> End<br /><br /> Select @OutMemberId 'out'<br /><br />End</pre><br /><br />Any Help<br />Thanks a lot<br /><br /><br />Rasha zaki <br />Web Developer<br />Cairo, Egypt
  2. Adriaan New Member

    Check ISNUMERIC in BOL.
  3. rasha New Member

    quote:Originally posted by Adriaan

    Check ISNUMERIC in BOL.
    Thanks Adriaan, sometimes brain stop thinking when it is tired.

    Rasha zaki
    Web Developer
    Cairo, Egypt

Share This Page