I had a few people from here assist in getting this query to be able to save in Visual Studio as I wasn't able to figure out the syntax error. Now, as I'm running it, it will not execute. Can anyone see what might be the issue?
-- =============================================
-- Author: Lloyd Pederson
-- Updated On: 11/17/2015
-- Description: Checks the given Employer Code, or Employer Name
-- against the Threshold Macro Table
-- =============================================
ALTER PROCEDURE [dbo].[PHS_A_THRESHOLD_MACRO_SP]
-- Add the parameters for the stored procedure here
@strEmpCode varchar(20),
@strEmpName varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Client_Name, Employer_Code FROM PHS_A_THRESHOLD_MACRO WHERE Client_Name LIKE '%' + @strEmpName + '%' OR (Employer_Code = @strEmpCode)
END
When you say "it will not execute", what is the symptom? Are you getting an error message? Or are you getting no rows at all? Where/how are you executing it?
Try running the query from SQL Server management studio - for example: USE YourDatabaseNameHere GO EXEC [dbo].[PHS_A_THRESHOLD_MACRO_SP] @strEmpCode='123', @strEmpName = 'Jones';
Sub Query_Database()
On Error Resume Next
Set Connection = CreateObject("ADODB.Connection")
strConnect = "PROVIDER=sqloledb.1;Data Source=dbsw9095;Initial Catalog=A_and_A;Integrated Security=SSPI"
'strConnect = "PROVIDER=sqloledb.1;Data Source=dbsw0420cls;Initial Catalog=A_and_A;Integrated Security=SSPI"
Connection.Open strConnect
If Err.Number <> 0 Then ShowError("Cannot connect to database.")
On Error goto 0
On Error Resume Next
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = Connection
Set rs = CreateObject("ADODB.recordset")
cmd.CommandText = "[dbo].[PHS_A_THRESHOLD_MACRO_SP]"
cmd.CommandType = 4 'adCmdStoredProc
cmd.Parameters("@strEmpCode") = strEmpCode
If Err.Number <> 0 Then ShowError("Cannot connect with stored procedure.")
On Error goto 0
On Error Resume Next
Set rs = cmd.Execute
If Err.Number <> 0 Then ShowError("Could not execute query.")
On Error goto 0
Set cmd = Nothing
' Do Loop is used to read all record from start to end
Do Until rs.EOF
' Loop through each field
'Used logic to combine search result values in one result
i = 1
For Each strEmpCode In rs.Fields
Result(0, i) = Result(0, i - 1) & " " & strEmpCode.Value
lastresult = Result(0, i)
i = i + 1
Next
rs.MoveNext
Loop
If rs.EOF And lastresult = "" Then
MsgBox strEmployer & ": $25 SUBSTANTIATION THRESHOLD"
Else
MsgBox lastresult & ": NO SUBSTANTIATION THRESHOLD"
End If
' Close the connection to the database
rs.Close
Set rs = Nothing
Connection.Close
Set Connection = Nothing
End Sub
When I get past the line Set rs = cmd.Execute, it goes to ShowError and stops.
I ran it as a VBA in Excel and this is the error message I get:
That is one of the other variables I have coded, but only one should trigger the query to execute, not both. it's either one or the other that get sent to the procedure.
So, if I'm understanding you correctly, it's because the code I showed was only for one of the parameters and that's why it's failing because I didn't add the other one? If so, that's because there is completely different code the sets up the parameters for @strEmpName. I combined the queries for simplicity, but it seems I can't do that. I tried splitting them up like this:
-- =============================================
-- Author: Lloyd
Pederson
-- Updated On: 11/17/2015
-- Description: Checks the given
Employer Code, or Employer Name
-- against the Threshold Macro Table
--
=============================================
ALTER PROCEDURE
[dbo].[PHS_A_THRESHOLD_MACRO_SP]
-- Add the parameters for the stored
procedure here
@strEmpCode varchar(20),
@strEmpName
varchar(20)
AS
IF @strEmpCode = ""
BEGIN
-- SET NOCOUNT ON added
to prevent extra result sets from
-- interfering with SELECT
statements.
SET NOCOUNT ON;
-- Insert statements for procedure
here
SELECT Client_Name FROM PHS_A_THRESHOLD_MACRO WHERE Client_Name LIKE '%'
+ @strEmpName + '%'
END
IF @strEmpName = ""
BEGIN
-- SET NOCOUNT
ON added to prevent extra result sets from
-- interfering with SELECT
statements.
SET NOCOUNT ON;
-- Insert statements for procedure
here
SELECT Employer_Code FROM PHS_A_THRESHOLD_MACRO WHERE Employer_Code LIKE
'%' + @strEmpCode + '%'
END
But now I'm getting a different error:
Sorry if I'm a bit novice. I'm not sure what all these technical terms mean. I do really appreciate the help of the people here.
If you are trying to pass one parameter or the other (and want to omit the other), you should provide default values for the parameters, as shown below.
The following code will work, but it may not result in the most efficient query plan in some cases. If performance becomes an issue, look up how to handle catch-all queries - for example, here.
ALTER PROCEDURE [dbo].[PHS_A_THRESHOLD_MACRO_SP] @strEmpCode VARCHAR(20) = '' , @strEmpName VARCHAR(20) = '' AS SET NOCOUNT, XACT_ABORT ON;
IF @strEmpCode = ''
BEGIN
SELECT Client_Name
FROM PHS_A_THRESHOLD_MACRO
WHERE Client_Name LIKE '%' + @strEmpName + '%'
END
IF @strEmpName = ''
BEGIN
SELECT Employer_Code
FROM PHS_A_THRESHOLD_MACRO
WHERE Employer_Code LIKE '%' + @strEmpCode + '%'
END
Ever used SQL Server Profiler? It's an easy GUI tool that let's you capture what's being executed. It sees that something else is happening here. We just can't see it.
I would have to see if the company allows that program to be downloaded. If not, I won't be able to use it.
I did bring error message I got when running as a VBA up to one of our lead developers and they noticed I was running my IF statements like they are nested. He said I need to add ELSE in front of the second IF:
-- =============================================
-- Author: Lloyd
Pederson
-- Updated On: 11/17/2015
-- Description: Checks the given
Employer Code, or Employer Name
-- against the Threshold Macro Table
--
=============================================
ALTER PROCEDURE
[dbo].[PHS_A_THRESHOLD_MACRO_SP]
-- Add the parameters for the stored
procedure here
@strEmpCode varchar(20),
@strEmpName
varchar(20)
AS
IF @strEmpCode = NULL
BEGIN
-- SET NOCOUNT ON added
to prevent extra result sets from
-- interfering with SELECT
statements.
SET NOCOUNT ON;
-- Insert statements for procedure
here
SELECT Client_Name FROM PHS_A_THRESHOLD_MACRO WHERE Client_Name LIKE '%'
+ @strEmpName + '%'
END
ELSE
IF @strEmpName = NULL
BEGIN
-- SET NOCOUNT
ON added to prevent extra result sets from
-- interfering with SELECT
statements.
SET NOCOUNT ON;
-- Insert statements for procedure
here
SELECT Employer_Code FROM PHS_A_THRESHOLD_MACRO WHERE Employer_Code LIKE
'%' + @strEmpCode + '%'
END
I'm still getting the same error in VBA, so I'm not sure what to do to get it to go away.
SQL Profiler is part of the SQL Server Performance Tools that should already be on your system. You can invoke it from SQL Server Management Studio from Tools menu.
As @gbritton had indicated, the error message you are seeing is not coming from the stored procedure code you posted, nor is it coming from the VB code you posted. It is from some other part of the code or another stored procedure.
Set Visual Studio to break on exception (Tools -> Options -> Debugging -> General) and examine the call stack to see where the error is coming from.
Actually, JamesK, I just noticed you sent me a piece of code that I omitted a few lines from. I added = '' after each variable declaration and then added SET NOCOUNT, XACT_ABORT ON and now I don't get the error in VBA. I was able to get past the Set rs = cmd.Execute without error.
