Hello. I'm using SQL query analyzer to run some queries against an
SQL2000 DB. Unfortunately, my previous SQL experience is some Oralce
SQL I took in school.

To put it simply, I'm trying to write a query to pull records that
match a variable input. In Oracle, I'd use a substitution variable
like this...

SELECT * FROM jobs WHERE jobid = &job_id_to_query;

Is this possible under SQL2000? Is this only a feature of SQL*Plus?

TIA

Rich

Hi,

If it's a one off - use the literal value in the WHERE clause.

If you need to execute it a bunch of times, create a stored procedure
which accepts the jobid as a parameter input and outputs the result
set.

Greg

Try:

DECLARE @job_id_to_query int
SET @job_id_to_query = 1
SELECT * FROM jobs WHERE jobid = @job_id_to_query

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rgonzale6 (AT) bellsouth (DOT) net> wrote

Dan Guzman wrote:
Would this codepiece bring up a user input prompt upon execution and
then run the query using the input that's passed? That was my
experience under Oracle MySQL.


Thanks for your response!

Rich

Nope, SQL Server QA doesn't have that capability..

You should be able to get what you need without the prompt though -
right?

Greg

Sorry I missed the prompt part of your requirement. Query Analyzer is more
of a DBA utility than an end user reporting tool. The closest thing is QA
is a template script like the example below. You can paste the script in a
QA window and press shift-ctrl-M to display the parameter replacement
window. Then click replace all and execute. You can save commonly used
template scripts in your QA template folder for easy selection using the
object browser.

SELECT * FROM jobs
WHERE jobid = <job_id_to_query, int,>

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rgonzale6 (AT) bellsouth (DOT) net> wrote

Well, what I'm trying to do is dump an SQL query into a command for
Crystal Reports reporting. I need the report to ask me for input upon
execution and have the SQL query return the results into the report
based on the input.

Thanks for your repsonse!

Rich

I don't know much about Crystal but I'm certain that Crystal can bind input
parameters to the SQL statement(s) needed for reporting. The parameter
values can be supplied to Crystal by your application at runtime.

You might try a Crystal reports forum
http://support.businessobjects.com/forums/default.asp.


--
Hope this helps.

Dan Guzman
SQL Server MVP

<rgonzale6 (AT) bellsouth (DOT) net> wrote

Will do. Thanks!

Rich