stored proc in sql server


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.
dotnet0824Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
dotnet0824Author Commented:
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
 
BrandonGalderisiCommented:
"hope i am clear "

clear as mud.

Care to take another stab at it :).
0
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.

All Courses

From novice to tech pro — start learning today.