Well theres a few, one is an error converting null to int but I fixed that. Then I just get "There was an error near )" or "There was an error near AND". 

Does it successfuly execute in a SQL Studio query?

Yes it does.

I guess my main question is why my if statements are not correct in workflow. They work fine in sql studio.

Workflow just passes the parameter data and the query to the SQL driver, it doesn't do any validation or parsing on the query. So that error comes from the driver. Given that you're naming your parameters, I take you're using the "direct" option for the data source?

I have 3 parameters set which the user enters then I query based off these values using the if statements. I had the values set to NULL in studio but have them changed to be a different value as the null was causing problems.

I was looking around and found Advanced Search Syntax 8.2 pdf so I tried my code like this in workflow:

IF ( @CPFile <> "99-9999" & @Serial ="99999" & @RefNo = "99999999")
    	SELECT
		PAGE0.CompanyNm , 
		PAGE0.ConsumerNm , 
		PAGE0.CPFileNo , 
		PAGE0.defld , 
		PAGE0.RefNo , 
		PAGE0.Staff , 
		PAGE0.InitType , 
		Typelaw.TypeName , 
		NOTES.DUEDATE , 
		NOTES.NOTEBY , 
		NOTES.NOTETYPE , 
		NOTES.SERIAL

FROM PAGE0
  
INNER JOIN Typelaw
ON ( PAGE0.TYPELAW = Typelaw.Serial )
  
INNER JOIN NOTES
ON ( PAGE0.SERIAL = NOTES.CaseSerial )
WHERE (  CPFileNo = @CPFile)

@CPFile, @RefNo, and @Serial are the three parameters also.
Any ideas what I should do?

Hi Josh,

You could avoid the problem by putting your SQL code into a stored procedure and then just calling that from Workflow. Given stored procedures are pre-compiled by the sql server, it may even execute measurably faster.

http://msdn.microsoft.com/en-us/library/dn133184.aspx

-Ben

Thank you, here is what I've got so far:

-- =============================================
-- Author:		Josh Schweigert
-- Create date: 05/28/2014
-- Description:	Stored procedure of the combined query for workflow
-- ===========================================

CREATE PROCEDURE Consumer_Stored_Procedure_2
(
	 @CPFile varchar (25),
	 @RefNo varchar (25),
	 @Serial varchar (25)


)

AS 
BEGIN

                                         /* Correspondence Logic
   =======================================================================================================
-- If ( User Input (Serial) != NULL ) = WHERE (Serial = User Input)
-- If (User Input (Reference) != NULL ) = WHERE (Reference = User Input)
-- If (User Input (CPFile AND Reference != NULL ) = WHERE (CPFile = User Input AND Reference = User Input )
-- If (User Input (Reference AND Serial != NULL ) = WHERE (Reference = User Input AND Serial = User Input)
-- If (User Input (All) != NULL ) = WHERE (All = User Input)
   ======================================================================================================= */





/* === If user input for the CPFile Number and Reference Number fields are NULL and Serial is populated ===
   ======================================================================================================== */
IF ( ( @CPFile IS NULL ) AND ( @RefNo IS NULL ) AND ( @Serial IS NOT NULL ) )

	SELECT

	-- From PAGE0 Table
		ATLB.dbo.PAGE0.CompanyNm , ATLB.dbo.PAGE0.ConsumerNm , ATLB.dbo.PAGE0.CPFileNo , ATLB.dbo.PAGE0.defld , ATLB.dbo.PAGE0.RefNo ,										ATLB.dbo.PAGE0.Staff , ATLB.dbo.PAGE0.InitType ,

	-- From TypeLaw Table
		ATLB.dbo.Typelaw.TypeName ,

	-- From NOTES Table
		ATLB.dbo.NOTES.DUEDATE , ATLB.dbo.NOTES.NOTEBY , ATLB.dbo.NOTES.NOTETYPE , ATLB.dbo.NOTES.SERIAL

FROM [ATLB].[dbo].[PAGE0]
  
INNER JOIN ATLB.dbo.Typelaw
ON ( PAGE0.TYPELAW = ATLB.dbo.Typelaw.Serial )
  
INNER JOIN ATLB.dbo.NOTES
ON ( PAGE0.SERIAL = NOTES.CaseSerial )

WHERE ( ATLB.dbo.NOTES.SERIAL = @Serial )
/* =============================================== End =================================================== */
/* ======================================================================================================= */











/*     If user input for the CPFile Number and Serial fields are NULL and Reference Number is populated
   ======================================================================================================= */
ELSE IF ( ( @CPFile IS NULL ) AND ( @Serial IS NULL ) AND ( @RefNo IS NOT NULL ) )

		SELECT

	-- From PAGE0 Table
		ATLB.dbo.PAGE0.CompanyNm, ATLB.dbo.PAGE0.ConsumerNm, ATLB.dbo.PAGE0.CPFileNo, ATLB.dbo.PAGE0.defld, ATLB.dbo.PAGE0.RefNo,											ATLB.dbo.PAGE0.Staff, ATLB.dbo.PAGE0.InitType,

	-- From TypeLaw Table
		ATLB.dbo.Typelaw.TypeName,

	-- From NOTES Table
		ATLB.dbo.NOTES.DUEDATE, ATLB.dbo.NOTES.NOTEBY, ATLB.dbo.NOTES.NOTETYPE, ATLB.dbo.NOTES.SERIAL

FROM [ATLB].[dbo].[PAGE0]
  
INNER JOIN ATLB.dbo.Typelaw
ON ( PAGE0.TYPELAW = ATLB.dbo.Typelaw.Serial )
  
INNER JOIN ATLB.dbo.NOTES
ON ( PAGE0.SERIAL = NOTES.CaseSerial )

WHERE ( RefNo = @RefNo )
/* =============================================== End =================================================== */
/* ======================================================================================================= */











/* === If user input for the CPFile Number is NULL and Reference Number and Serial Number are populated ===
   ======================================================================================================== */
ELSE IF ( ( @CPFile IS NULL ) AND ( @RefNo IS NOT NULL ) AND ( @Serial IS NOT NULL ) )

		SELECT

	-- From PAGE0 Table
		ATLB.dbo.PAGE0.CompanyNm , ATLB.dbo.PAGE0.ConsumerNm , ATLB.dbo.PAGE0.CPFileNo , ATLB.dbo.PAGE0.defld , ATLB.dbo.PAGE0.RefNo ,										ATLB.dbo.PAGE0.Staff , ATLB.dbo.PAGE0.InitType ,

	-- From TypeLaw Table
		ATLB.dbo.Typelaw.TypeName ,

	-- From NOTES Table
		ATLB.dbo.NOTES.DUEDATE , ATLB.dbo.NOTES.NOTEBY , ATLB.dbo.NOTES.NOTETYPE , ATLB.dbo.NOTES.SERIAL

FROM [ATLB].[dbo].[PAGE0]
  
INNER JOIN ATLB.dbo.Typelaw
ON ( PAGE0.TYPELAW = ATLB.dbo.Typelaw.Serial )
  
INNER JOIN ATLB.dbo.NOTES
ON ( PAGE0.SERIAL = NOTES.CaseSerial )

WHERE ( ( RefNo = @RefNo ) AND ( ATLB.dbo.NOTES.SERIAL = @Serial ) )
/* =============================================== End =================================================== */
/* ======================================================================================================= */










/*	============================ If user input for all fields is populated ===============================
   ======================================================================================================= */
ELSE
		SELECT

	-- From PAGE0 Table
		ATLB.dbo.PAGE0.CompanyNm , ATLB.dbo.PAGE0.ConsumerNm , ATLB.dbo.PAGE0.CPFileNo , ATLB.dbo.PAGE0.defld , ATLB.dbo.PAGE0.RefNo ,									    ATLB.dbo.PAGE0.Staff , ATLB.dbo.PAGE0.InitType ,

	-- From TypeLaw Table
		ATLB.dbo.Typelaw.TypeName ,

	-- From NOTES Table
		ATLB.dbo.NOTES.DUEDATE , ATLB.dbo.NOTES.NOTEBY , ATLB.dbo.NOTES.NOTETYPE , ATLB.dbo.NOTES.SERIAL

FROM [ATLB].[dbo].[PAGE0]
  
INNER JOIN ATLB.dbo.Typelaw
ON ( PAGE0.TYPELAW = ATLB.dbo.Typelaw.Serial )
  
INNER JOIN ATLB.dbo.NOTES
ON ( PAGE0.SERIAL = NOTES.CaseSerial )

WHERE ( ( RefNo = @RefNo ) AND ( ATLB.dbo.NOTES.SERIAL = @Serial ) AND ( CPFileNo = @CPFile ) )
/* =============================================== End =================================================== */
/* ======================================================================================================= */

END

Is this the correct way to do this? In workflow I have it call the procedure using:

exec "procedure" @Input_parameter1...

But I still dont get results.

Scratch that - now I have a closer look at your code (I jumped the gun earlier) It looks like a solution would be to create a set of individual views and have a decision branch activity decide which one to select from. 

a stored procedures would need output variables defined but at any rate, I think creating views would be more appropriate to your requirement.

So what exactly do you mean by "views" like case statements?

So it allows you to just do a single query by using a select statement where a field has a specific value? How would I use IF cases in the workflow query editor to use this on specific cases of user input?

Thank you for all your help thus far!

Here is what I did and I get the following error:

(If the user input for any of the three main variables is blank it is set to a default value. CPFile = 99-9999, Serial = 99999, and RefNo = 99999999

CREATE VIEW QueryView2 AS

SELECT
PAGE0.CompanyNm , 
PAGE0.ConsumerNm ,
PAGE0.CPFileNo ,
PAGE0.defld ,
PAGE0.RefNo ,										PAGE0.Staff ,
ATLB.dbo.PAGE0.InitType ,
Typelaw.TypeName ,
NOTES.DUEDATE , 
NOTES.NOTEBY , 
NOTES.NOTETYPE , 
NOTES.SERIAL

FROM PAGE0
  
INNER JOIN Typelaw
ON ( PAGE0.TYPELAW = Typelaw.Serial )
  
INNER JOIN NOTES
ON ( PAGE0.SERIAL = NOTES.CaseSerial )
go

IF ( @CPFile = '99-9999' AND @RefNo = '99999999' AND @Serial <> '99999' )
     SELECT * FROM QueryView2 WHERE NOTES.SERIAL = @Serial

Hi you can use WF decision branches to decide how to query the view.

Your IF statement contains non-declared variables and shouldn't be run the same time as you crate your view (not sure if that's causing the error but it can be problematic). I think you should read an introductory SQL book over the weekend ;). I'm really not in a position to give you advice, just point you in a direction and hope it's the right one! 

Well the decision branches are how its set up now. I was trying to convert all of them into a single query rather than 4 separate branches. Do you have any recommendations of books to read? I've just been winging it and learning off google!

Thanks.