Using substitution variables in TSQL

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 2005 Forums

Transact-SQL (2005)

Using substitution variables in TSQL

Author

Topic

vamsikgummadi
Starting Member

2 Posts

Posted - 2007-11-30 : 21:07:57

Hello friends,
We are working on a small application which needs to take value from the user as input and retrieve all the related rows.

In Oracle we use the concept of substitution variables to solve this query which is something like:

SELECT Deptno, Salary, Employee
FROM EMP
WHERE Deptno='&Deptno' ;

We will be prompted to give an input and suppose our input is 10
then o/p will be something as

Deptno Employee Salary
10 Tom 1000$
10 Jim 5000$
10 Pal 7000$

Is there any such method which has the same functionality in TSQL of MS SQL Server which prompts for an input from the user and displays all the related records from the table based on the input value supplied by the user.
Note: I do not want to use Full Text Indexing property available in TSQL for this purpose and I only need to solve this by using a SQL query.

Hope I am clear with my requirement. Please get back if you need some clarifications from my side. Hope to get a reply from you at the earliest with suggestions and clarifications to solve this query with some code snippets if possible.

Regards,
Vamsi K Gummadi.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-30 : 21:29:44

sql server is a server, not a client application. it is the responsibility of a client app to prompt users for input.

normally you would implement this query as a stored proc, and DeptNo would be passed as a parameter to the proc by your client app.

elsasoft.org

vamsikgummadi
Starting Member

2 Posts

Posted - 2007-12-01 : 20:25:53

Hello frined,
Thank you for the suggestion but the thing is that I want to implement the search functionality by using substitution variables in TSQL. I do not want to use full text indexing.
Regards,
Vamsi K Gummadi.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2007-12-01 : 20:44:19

[code]
Declare @Deptno int
Set @Deptno = 10

SELECT Deptno, Salary, Employee
FROM EMP
WHERE Deptno = @Deptno
[/code]

And have your application prompt for the input.

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
› As far as myself... I do this for fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources