Writing 1 SP for multiple searches

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

Transact-SQL (2000)

Writing 1 SP for multiple searches

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 07:14:57

Imagine I have 2 buttons on a webpage. If user clicks button1, then I want to perform a search qury on column1, if user clicks button2, then I want to perform a search qury on column2.

How do I write a search query for this situation. I can create 2 SP, so if user clicks button1, SP1 is called and vice versa.

But is it possible that I can create only 1 SP, which can determine which columns to search for?? What about this:

CREATE PROCEDURE spv_test
@columnname nvarchar (50),
@query nvarchar (50)

AS
SELECT * FROM products
where @columnname = @query
GO

Any ideas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-30 : 07:29:19

You can try this


CREATE PROCEDURE spv_test
@colno int,
@value nvarchar (50)

AS
if @colno=1
SELECT * FROM products 
where [col1] = @value
else
SELECT * FROM tt 
where [col2] = @value

EDIT : Pass column number and its value as parameter

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-30 : 07:36:26

You can tweak Madhivanan's code to use only a single SELECT statement and no IF clause:

CREATE PROCEDURE spv_test @colno int, @value nvarchar (50) AS
SET NOCOUNT ON
SELECT * FROM products
where (@colno=1 AND [col1] = @value)
OR (@colno=2 AND [col2] = @value)

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 07:38:44

just curious to know why @columnname would not work:
SELECT * FROM products
where @columnname = @query

??

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 07:39:03

just curious to know why @columnname would not work:
SELECT * FROM products
where @columnname = @query

??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-30 : 07:41:18

For that you need to use Dynamic SQL which in this case not advisable

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-30 : 07:43:01

It's part of the syntax of the command. SQL will compare the variable to the value, it will NOT substitute the name of the column in that variable when it checks for equality. And even if it could do that, the query optimizer could not do its job properly if the columns are not explicitly named in the query.

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-06-30 : 08:13:18

thanks for the replies.

I have tried both code. But madhivanan's code is working, but robvolk's does not seem to be working on columns that are nvarchar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources