Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: SQL Query Help

  1. #1
    Regular Coder
    Join Date
    Jan 2010
    Posts
    160
    Thanks
    10
    Thanked 1 Time in 1 Post

    SQL Query Help

    Hi

    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



    Thanks
    Roy

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •