Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Unanswered: Parameter Query Issues

    Hi all I am trying to create a parameter in sql getting an error message dont know why?/
    Last edited by desireemm; 04-15-09 at 17:23.

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Question

    Is this u try to do?
    Code:
    use pubs
    go
    declare @stateCode varchar(10)
    set @stateCode='CA'
    select 
     au_lname,
     au_fname 
    from 
     authors
    where 
     state= @stateCode
    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
    Alpine Califormia
    Posts
    1,789

    That worked

    that worked but I wonder why mine doesnt work I did it according to books online

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The question mark is used to denote where a parameter goes in a parameterized query in either OLEDB or ADO. The question mark is associated with the library, not with SQL itself... To mangle a line from "Gone with the Wind", the Transact-SQL interpreter don't know nothing about question marks.

    -PatP

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by desireemm
    that worked but I wonder why mine doesnt work I did it according to books online
    Perhaps you were reading through the section on DTS parameters? In DTS, question marks are acceptable place holders for parameters which are defined on the parameters tab.

    T-SQL parameters != DTS parameters

    Perhaps it would be better to reference T-SQL parameters by a different name: variables. See @local_variable in SQL BOL.

    Now, if someone could explain variables in Oracle stored procs for me, I would indeed be very grateful.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Heres where I got it from

    Quoting Books Online

    Parameter Queries
    In some cases you want to create a query that you can use many times, but with a different value each time. For example, you might frequently query a titles table to find all the books written by one author. You could run the same query for each request, except that the author's ID or name would be different each time.

    To create a query that can have different values at different times, you use parameters in the query. A parameter is a placeholder for a value that is supplied when the query runs. An SQL statement with a parameter might look like the following, where "?" represents the parameter for the author's state:

    SELECT au_lname, au_fname
    FROM state
    WHERE state = ?
    Where You Can Use Parameters
    You can use parameters as placeholders for literal values for either text or numeric values. Most commonly, parameters are used as placeholders in search conditions for individual rows or for groups (that is, in the WHERE or HAVING clauses of an SQL statement).

    Some databases allow you to use parameters as placeholders in expressions. For example, you might want to calculate discounted prices by supplying a different discount value each time you run a query. To do so, you could specify the following expression:

    (price * ?)
    For details about where you can use parameters, see Parameters.

    Specifying Unnamed and Named Parameters
    You can specify two types of parameters: unnamed and named. An unnamed parameter is a question mark (?) that you put anywhere in the query that you want to prompt for or substitute a literal value. For example, if you use an unnamed parameter to search for an author's id in a titles table, the resulting statement in the SQL pane might look like this:

    SELECT au_lname, au_fname
    FROM state
    WHERE state = ?
    When you run the query in the Query Designer, the Define Query Parameters dialog box appears with "?" as the name of the parameter, as in the following illustration.



    Alternatively, you can assign a name to a parameter. Named parameters are particularly useful if you have multiple parameters in a query. For example, if you use named parameters to search for an author's first and last names in an authors table, the resulting statement in the SQL pane might look like this:

    SELECT au_id
    FROM authors
    WHERE au_fname = %first name% AND
    au_lname = %last name%
    When you run the query in the Query Designer, the Define Query Parameters dialog box appears with a list of named parameters, as in the following illustration.

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Thank you

    Thanks guys for educating me on that, I sure appreciate it

Posting Permissions

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