Using input parameter in view or sql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using input parameter in view or sql query

Author

Topic

s121701
Starting Member

6 Posts

Posted - 2007-07-12 : 20:33:25

I am new to sql server. I am using sql 2000. I created a view that shows customer information. I need to include a user parameter that lets user enter ID's every time they run this query. I tried using ? instead of id but I am getting incorrect sql syntax error message. Here's how my query look like now:

SELECT Customer.ID, LastName, FirstName
FROM Customer
WHERE (Customer.ID = ?)

Would appriciate any help!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 21:55:01

View can't have an input parameter. You can use write it as a stored procedure or UDF (user defined function) to accept the input parameter.

or you create the view like this

CREATE VIEW view_Customer 
AS
SELECT ID, LastName, FirstName
FROM   Customer

and
when selecting from the view for ID = 1234

SELECT * FROM view_Customer WHERE ID = 1234

KH
[spoiler]Time is always against us[/spoiler]

s121701
Starting Member

6 Posts

Posted - 2007-07-13 : 13:24:54

thanks. I would like user to enter the ID. Is there a way we can do that in UDF or store procedure. Can you show a simple example.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 13:51:36

quote:
Originally posted by s121701

thanks. I would like user to enter the ID. Is there a way we can do that in UDF or store procedure. Can you show a simple example.

Thats what KH did. He provided an example of how you can use a view for a specific parameter.

Use a statement like this in your app:
SELECT * FROM view_Customer WHERE ID = @ID

And pass the value from the user to the parameter via @ID.

You can also do this via proc. Just a matter of personal preference.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-13 : 15:30:27

quote:
Originally posted by s121701

thanks. I would like user to enter the ID. Is there a way we can do that in UDF or store procedure. Can you show a simple example.

SQL is not a user interface language; it is a database querying language. To prompt a user for a parameter is not the job of the database server, but a client application like a web page, reporting application, or windows app. SQL just declares placeholders for the parameters and uses them to retrieve the data; it is up to the front end applications to assign those parameters values. Hope this helps. It's a pretty key concept to really get a hold of if you are working with database servers.

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-13 : 23:55:30

Also note that you cant simulate ACCESS's

Select columns from table where column="?"

Which will prompt you to enter value for column

Better you create a stored procedure that accepts Id as parameter and pass the value when executing

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources