Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2003-11-12
5
Medium Priority
?
1,113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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 …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

636 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