User input in SQL2000

[rgonzale6]

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_quer y;

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

TIA

Rich

[Greg]

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

[Dan Guzman]

Try:

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rg*******@bell south.net> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

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_quer y;

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

TIA

Rich

[rgonzale6]

Dan Guzman wrote:

Try:

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rg*******@bell south.net> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

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_quer y;

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

Rich

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

[Greg]

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

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

Greg

[Dan Guzman]

> 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.
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_quer y, int,>

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rg*******@bell south.net> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .
Dan Guzman wrote:

Try:

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rg*******@bell south.net> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

> 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_quer y;
>
> Is this possible under SQL2000? Is this only a feature of SQL*Plus? >
> TIA
>
> Rich
>

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

[rgonzale6]

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

[Dan Guzman]

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

<rg*******@bell south.net> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .

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

[rgonzale6]

Will do. Thanks!

Rich