Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: TOP x query in Xml Template

    HI,

    how can I specify the number of records that have to be sent back dynamically in an Xml Template?

    I am currently busy developing a client-server application that pulls data from an Sql Server via Xml. If the client application has a bad connection to the server, the program has to get the data in small portions.

    When I use:

    SELECT TOP @amount *
    FROM employees

    I get the error: syntax near '@amount'.

    The query fails as wel when I tried to use:

    SET ROWCOUNT @amount

    SELECT *
    FROM employees

    SET ROWCOUNT 0

    This works fine in Sql analyser, but sql doesn't allow the integers to be parameters and casting them to int does not work either.

    thanks for your help!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pass amount as a string, then use dynamic SQL. Something like:
    Code:
    EXECUTE ('SELECT TOP ' + @amount + '  * FROM employees')
    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    dynamic queries via http too insecure

    It is possible to use dynamic sql, but that will be very insecure, cause everyone can intercept the sql statement and change it to whatever he wants. That is why I use xml templates, because the only thing a cracker can do with that is getting information that he is not supposed to see.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, I was thinking arguments to stored procedures, not passing dynamic SQL wholesale down the pipe. I still think that gets you exactly what you want, securely to boot!

    -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
  •