[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2003-11-12
5
Medium Priority
?
1,118 Views
Last Modified: 2012-08-13
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
Comment
Question by:edmund_mitchell
  • 2
  • 2
5 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 9732875
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
 
LVL 4

Author Comment

by:edmund_mitchell
ID: 9732936
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
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 500 total points
ID: 9733010
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
 
LVL 4

Author Comment

by:edmund_mitchell
ID: 9733069
I hope that worked -- I split the points equally.  Thanks to you both!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9733647
>> 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question