469,610 Members | 1,554 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

User input in SQL2000

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

Jul 23 '05 #1
8 5123
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

Jul 23 '05 #2
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

<rg*******@bellsouth.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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_query;

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

TIA

Rich

Jul 23 '05 #3

Dan Guzman wrote:
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

<rg*******@bellsouth.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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_query;

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

Jul 23 '05 #4
Nope, SQL Server QA doesn't have that capability..

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

Greg

Jul 23 '05 #5
> 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_query, int,>

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rg*******@bellsouth.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Dan Guzman wrote:
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

<rg*******@bellsouth.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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_query;
>
> 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

Jul 23 '05 #6
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

Jul 23 '05 #7
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*******@bellsouth.net> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.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

Jul 23 '05 #8
Will do. Thanks!

Rich

Jul 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by John | last post: by
1 post views Thread by Anote Sangkunakupt | last post: by
1 post views Thread by Cobra Pilot | last post: by
12 posts views Thread by Robert Bravery | last post: by
2 posts views Thread by PointMan | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.