• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1127
  • Last Modified:

Stored proc syntax -- returning a boolean -- to VB 6 code

Hello,

I need to see an example of a stored proc that receives a string (representing a logon_name) as an input parameter, and checks the user table to see if there is already a user with that logon_name, if so, returns a boolean of true.

Then, how can I call that stored proc from VB 6 code and "get"  the Boolean?

I have no idea how to do this, so thanks for your help, or suggestions for better ways of doing this.



0
edmund_mitchell
Asked:
edmund_mitchell
  • 2
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
I can help with SP.
Will need someone else to help with VB code.

There are two SP approaches you could take, either use the return code to indicate existence or use an output variable, for example:

CREATE PROCEDURE yourProcNameHere (
      @logon_name VARCHAR(50)
)
AS
RETURN CASE WHEN EXISTS(SELECT 1 FROM [user] WHERE logon_name = @logon_name) THEN 1 ELSE 0 END

<OR>

CREATE PROCEDURE yourProcNameHere (
      @logon_name VARCHAR(50),
      @name_found BIT = 0 OUTPUT
)
AS
SET @name_found = CASE WHEN EXISTS(SELECT 1 FROM [user] WHERE logon_name = @logon_name) THEN 1 ELSE 0 END
RETURN
0
 
edmund_mitchellAuthor Commented:
Thanks, Scott - if someone else does the VB then I'll split the points.  Do you happen to know if that search is case sensitive or insensitive?  
0
 
namasi_navaretnamCommented:
Here is the VB code for Scott's Proc with output parameter.

Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter

With cmd
    .CommandText = "sp_GetUserInfo"
    .CommandType = adCmdStoredProc
    .ActiveConnection = CONN_STRING
    Set param1 = .CreateParameter("UserId", adVarChar, adParamInput, 50, "JohnDoe")
    .Parameters.Append param1
   
    Set param2 = .CreateParameter("NameFound", adInteger, adParamOutput)
    .Parameters.Append param2
   
    .Execute Options:=adExecuteNoRecords
    Set .ActiveConnection = Nothing
    Set param1 = Nothing
End With


Notice that in the Execute method, we specified adExecuteNoRecords. Doing so tells ADO not to bother building and populating a recordset just to hold the return value.

Once executed, your code can retrieve the output parameter value one of two ways: either by accessing the Parameters collection, or by using the Parameter object directly.  The following code illustrates both methods and concludes the previous code procedure.

MsgBox param2.Value

Set param2 = Nothing
Set cmd = Nothing
End Sub

0
 
edmund_mitchellAuthor Commented:
I hope that worked -- I split the points equally.  Thanks to you both!
0
 
Scott PletcherSenior DBACommented:
>> Do you happen to know if that search is case sensitive or insensitive?  

That depends on the type of installation.  If the installation is case sensitive, the comparison will be; if it is not, the comparison won't be.  

Now, if you want to force it to be insensitive, even on a case sensitive installation, convert both to upper (or lower) case, for example:

SELECT 1 FROM [user] WHERE UPPER(logon_name) = UPPER(@logon_name)

If you want to force it to be case sensitive on a case-insensitive installation, you have to cast to varbinary, like so:

SELECT 1 FROM [user] WHERE CAST(logon_name AS VARBINARY(50)) = CAST(@logon_name AS VARBINARY(50))
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now