We help IT Professionals succeed at work.
Get Started

What is wrong with my SQL syntax in this Stored Procedure?

colinasad
colinasad asked
on
185 Views
Last Modified: 2021-04-21
I am developing an Access "project" (.adp) as a front-end to a SQL Server 2005 Express database.

In Access I am calling a Stored Procedure to copy selected records from a "master" stock table, "TBL_Stock" into a temporary table called "TBL_StockList".
I allow the operator to make some choices (wildcards and option buttons) and pass these choices into the SP as parameters that are then used in a SQL "WHERE" clause.
I have attached the main part of the SP.

Without the final parameter, "@paramFlagWhere", this all works OK, and the correct records are copied, obeying the wildcard settings.
What I am trying to do with the "@paramFlagWhere" parameter is pre-build a bit of syntax depending on how the operator has set a possible 8 flag options. The text of @paramFlagWhere when it is sent into the SP will be something like :
" AND Stock_Flag1 = '1' AND Stock_Flag4 = '0'", depending on the choices the operator has made.

I am fairly happy that I am building that piece of text OK (it works when I use it for other purposes), but it doesn't seem to work when I try to add it to my SP's WHERE clause using the simple " + @paramFlagWhere". When I try this no records are copied.

Can someone point out where I am going wrong and how I should be adding this further condition to my SP's WHERE clause?

Many thanks. Colin.

ALTER PROCEDURE dbo.USP_CopyMasterStocksToList
@paramListID int,
@paramStockWild nvarchar (50),
@paramSuppWild nvarchar (50),
@paramManufWild nvarchar (50),
@paramCategWild nvarchar (50),
@paramRangeWild nvarchar (50),
@paramFlagWhere nvarchar (300)
 
AS 
 
BEGIN
 
	SET NOCOUNT ON;
 
	INSERT  INTO 	TBL_StockList
			(StkList_ListID, StkList_MasterUniqID, StkList_Code,    StkList_Description, StkList_SuppCode, StkList_ManufCode,
			StkList_Range, StkList_Category, StkList_DateIntro)
	SELECT  		@paramListID, Stock_UniqID, Stock_Code, Stock_Description, Stock_SuppCode, Stock_ManufCode,
			Stock_Range, Stock_Category, Stock_DateIntro
	FROM 		TBL_Stock
	WHERE  		Stock_Code LIKE @paramStockWild AND Stock_SuppCode LIKE @paramSuppWild AND Stock_ManufCode LIKE @paramManufWild
			AND Stock_Category LIKE @paramCategWild AND Stock_Range LIKE @paramRangeWild + @paramFlagWhere
 
END

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE