Solved

stored proc in sql server

Posted on 2009-05-18
4
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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