Solved

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

Posted on 2003-11-12
5
1,034 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 69

Accepted Solution

by:
ScottPletcher earned 125 total points
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
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
Comment Utility
I hope that worked -- I split the points equally.  Thanks to you both!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now