SQL Query Help
I have been given the following query however it does not seem to work in Microsoft query it does not like the @ parts. Any ideas on how to amend.
For example [Err] 42000 - [SQL Server]Must declare the scalar variable "@stockTakeID".
SELECT stcProductID, prdProductCode,prdName,stcStockLocationID,stklCode,stklName,stcQuantity,stcBinLocation,tblUser.UserI D,userForename,userSurname FROM tblStockCount
INNER JOIN tblProduct ON stcProductID = prdID
INNER JOIN tblStockLocation ON stcStockLocationID = stklID AND stklError = 0
INNER JOIN tblUser ON userSID = stcUserSID
WHERE stcStockTakeID = @stockTakeID
AND CASE @sProductCode WHEN '' THEN '' ELSE prdProductCode END LIKE @sProductCode + '%'
AND CASE @userID WHEN 0 THEN 0 ELSE userID END = @userID
ORDER BY prdProductCode
Post the entire procedure code. Typically you'd declare the variable for use as a part of the procedure definition so it can be called and replace the variables with whatever runtime info you give it.
MySQL doesn't require that you pre-declare variables that start with @.
SQL Server requires that *ALL* variables be predeclared.
So you will need to do something like
CREATE PROCEDURE yourProcedureName(
@sProductCode VARCHAR(100) = '',
@userID INT = 0