Solved

stored proc in sql server

Posted on 2009-05-18
4
204 Views
Last Modified: 2012-05-07

I have a front end form where in a customer can enter

his cusotmerID or AddressLine1 or customer_type or his firstname or his social security number.
This data lets say is from single table :tbl_customer. Its not mandatory for him to enter all.
I want to create a stored procedure to search and display record according to his input

can it be shown.
0
Comment
Question by:dotnet0824
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 200 total points
ID: 24413637
CREATE PROCEDURE GetCustomerDetails
@ColumnName varchar(20) ,
@Value  nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @nSQL nvarchar(2000)
    SELECT @nSQL  = ' SELECT * FROM tbl_Customer WHERE '+@ColumnName +' = @Value  '
    exec sp_ExecuteSQL @nSQL, N'@Value nvarchar(100) ', @Value
END
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 50 total points
ID: 24414115
One small addition to aneesh's post.  Put [] around the column name in the generated SQL.
CREATE PROCEDURE GetCustomerDetails
@ColumnName varchar(20) ,
@Value  nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @nSQL nvarchar(2000)
    SELECT @nSQL  = ' SELECT * FROM tbl_Customer WHERE [' @ColumnName  '] = @Value  '
    exec sp_ExecuteSQL @nSQL, N'@Value nvarchar(100) ', @Value
END

Open in new window

0
 

Author Comment

by:dotnet0824
ID: 24415319
He can either either of those. any one can be entered or two can be entered or all can be entered

i suppose we need or statements in the query . hope i am clear
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24415341
"hope i am clear "

clear as mud.

Care to take another stab at it :).
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now