Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: SQL Query Help
10-22-2013, 07:33 AM #1
- Join Date
- Jan 2010
- Thanked 1 Time in 1 Post
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
10-22-2013, 07:36 PM #2
- Join Date
- Sep 2002
- Saskatoon, Saskatchewan
- Thanked 2,659 Times in 2,628 Posts
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.
header('HTTP/1.1 420 Enhance Your Calm');
10-28-2013, 09:41 PM #3
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
Code:CREATE PROCEDURE yourProcedureName( @stockTakeID INT, @sProductCode VARCHAR(100) = '', @userID INT = 0 ) AS ...
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.