Results 1 to 6 of 6

Thread: Question

  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: Question

    In Oracle, you can write a statement that uses the "&" as a variable,

    For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.

    How do you do this in transact SQL for SQL Server?

    Thanks

    Lystra

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    u have to pass as parameter in sql server

    Code:
    --eg:
     
    create procedure sample_sp
    (
      @Enter_Operator_Name as varchar(100)
    )
    as
     
    select * from tbl_Stats where operator = @Enter_Operator_Name
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by milan1115
    For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.
    Dunno much about Oracle - but this sounds rather like Access which also is an integrated UI & dbms.

    The gist with SQL Server is that the app will have to get the parameter value itself and then pass this to the sproc (a la mallier's answer).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2004
    Posts
    191
    The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @Enter_Operator_Name.

    Any suggestions?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by milan1115
    The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @Enter_Operator_Name.

    Any suggestions?
    T-SQL is pretty different from Oracle with respect to handling variables. In T-SQL, you are going to end up having to do something like:

    Code:
    DECLARE @MyVar int
    
    SELECT @MyVar = 100
    
    SELECT * FROM tblCustomers WHERE CustID = @MyVar
    At runtime, you will have to replace the '= 100' with a value that you supply. Using SQLPlus, I know that you can do things from the command line calling files and replacing variables on the fly with parameters that you supply from the command line. This is doable in SQL Server as well, but you will need to use osql instead of the Query Analyzer to achieve the same effect (and even then I don't know exactly how to do it since I am not an osql guru).

    Regards,

    hmscott


    Edit to add: You said "Procedure". Is this a stored proc you are trying to create? If so, in your stored proce declaration you can do this:

    Code:
    CREATE PROC spGetCustomer (@CustID int)
    
    AS
    
    SET NOCOUNT ON
    
    SELECT * FROM tblCustomer WHERE CustID = @CustID
    Have you hugged your backup today?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQL*Plus is an environment tool as well as a script execution tool. You can compare it to Microsoft Access in that respect, in that both allow easy integration between the UI and the database engine.

    A pure database scripting tool can't (and shouldn't) support user interaction. It needs to execute scripts, and only execute scripts. If the data isn't coded into the script, you really need a different tool (such as VBS or Microsoft Access) to properly implement that functionality.

    -PatP

Posting Permissions

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