SQLTeam.com | Weblogs | Forums

Query will not execute when called


#1

Hello,

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

#2

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';


#3

Here is where I'm executing it from:

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.


#4

does the proc run in ssms? If not, what error message does it give?


#5

Not sure what SSMS is.

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.


#6

OK, the error message tells you the problem! IIRC you have to add the parameters to the cmd object.

see here I don't see you calling the parameters "add" method.


#7

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.


#8

Did you post everything? I cannot see any SELECT INTO statement in what you posted.


#9

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

#10

That's everything. I'm not sure myself why the error is saying "SELECT INTO".


#11

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.


#12

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.


#13

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.


#14

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.