Sql Server Search with Sproc

I have an app where I want to do a generic search.   Meaning I want the user to choose the column to search on and type in a search string.

So I want to be able to use two parameters in my sproc as follows:

PROCEDURE [dbo].[sp_search]

      @searchColumn varchar
                     @searchValue varchar

AS
BEGIN
      SELECT  * from table where @searchColumn = @searchValue

END

I realize this is very basic but its also very useful and I have a hard time finding good and direct sql help.

Also How can I print out my sql in this scenario to the screen so I can see how it looks?

for example:  @sql = 'select * from table where '  + @searchColumn + '=' +  @searchValue

print @sql
rochestermnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
Dynamic SQL is your only option. You'll have problems searching for the different data types. The example below will do numeric data but if you want to search for string data you will need to wrap the @searchValue in single quotes within the dynamic SQL.

On a side note: http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/

Lee
PROCEDURE [dbo].[sp_search]
      @searchColumn varchar
                     @searchValue varchar
AS
BEGIN
      declare @sSQL as nvarchar(1000)

      select @sSQL = 'SELECT  * from table where ' + @searchColumn + ' = ' + @searchValue
END

Open in new window

0
Lee SavidgeCommented:
Forgot the exec.

Lee
PROCEDURE [dbo].[sp_search]
      @searchColumn varchar
                     @searchValue varchar
AS
BEGIN
      declare @sSQL as nvarchar(1000)

      select @sSQL = 'SELECT  * from table where ' + @searchColumn + ' = ' + @searchValue

      exec(@sSQL)
END

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
some fixes:
PROCEDURE [dbo].[sp_search]
( @searchColumn sysname
, @searchValue nvarchar(max)
)
AS
BEGIN
   declare @sSQL as nvarchar(max)

   set @sSQL = 'SELECT  * from table where [' + replace(@searchColumn, '''', '''''') + '] = ''' + replace(@searchValue, '''', '''''') + ''' '
   exec(@sSQL)
END

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

rochestermnAuthor Commented:
This works great except when Im joining two tables that have the same column name.

Im not sure how to get rid of the ambigous column name error.

so if I have inner join on tablea.fieldname = tableb.fieldname
where fieldname = value

It gives me the ambiguous error because im using a parm to pass in the column name I cant
be specific unless I get creative in the logic by saying case when column name like 'fieldname'
append a. or b.   Not sure how to handle it.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to specify the table name from where the column names comes from, aka:
PROCEDURE [dbo].[sp_search]
( @searchColumn sysname
, @searchValue nvarchar(max)
)
AS
BEGIN
   declare @sSQL as nvarchar(max)

   set @sSQL = 'SELECT  * from tableA a JOIN tableB b ON b.field = a.field where a.[' + replace(@searchColumn, '''', '''''') + '] = ''' + replace(@searchValue, '''', '''''') + ''' '
   exec(@sSQL)
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rochestermnAuthor Commented:
Thank you!

How do I append more columns to the where clause?  Im getting crossed up with all the ''''' marks.

Also need to order by something.
0
rochestermnAuthor Commented:
Can you please explain all the tic marks in a script such as this.  Ive never understood why you have to have so many ''''' I need to add a sort to the expression.  Thanks.
0
rochestermnAuthor Commented:
The question was answered but the solution was not fully understood.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Can you please explain all the tic marks in a script such as this.  Ive never understood why you have to have so many ''''' I need to add a sort to the expression.

let me explain it with some code:

declare @x varchar(1000)
set @x = 'this is 1 single quote >''<, in the t-sql it''s 2 single quotes. why? because the single quote itself is already a text delimiter, a special character, so it needs to be escaped. in t-sql, to escape the quote, you have to duplicate it'
select @x

set @x = ''''
select @x  -- this will return a single quote string.
-- the first quote is to start the string, the second and third are the string itself, the last is to finish off the string

Open in new window


the replace will try to replace 1 single quote in the variable by 2 single quotes to make sure the generated sql will have properly 2 single quotes...
so, 1 single quote to start the string, 2x2 single quotes to show 2 single quotes in the resulting text, and 1 final singl quote to finish the string varchar. makes 5 quotes ... :)

hope this clarifies


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.