I've two tables in the database named Customer and orders. In my ASP.NET page I've checkboxes representing the the fields in those two tables.
The user can select from the checkboxes (Firstname,OrderitemName is compulsory). Based on the selected checkboxes, I need to generate a sql query. The query will be returning the results with filter applied from the CustomerType Drop down list when the user presses Submit button.For this, I checked whether the checkbox is selected in my code and passed it as bit type in my stored procedure. My stored procedure is,\[code\]CREATE PROCEDURE [dbo].[GetCustomerDetails] @LastName BIT,@Phone BIT,@Email BIT,@Quantity BIT,@OrderedDate BIT,@ReturnPolicy BIT,@CustomerType nvarchar(40) \[/code\]ASBEGIN SET NOCOUNT ON DELCARE @Sql NVARCHAR(4000)\[code\]SET @Sql='SELECT c.FirstName,
rderItemName'IF @LastName =1 BEGIN SET @Sql=@sql+'c.LastName,' END IF @Phone =1 BEGIN SET @Sql=@sql+'c.Phone,' END IF @Email =1 BEGIN SET @Sql=@sql+'c.Email,' ENDIF @Quantity =1 BEGIN SET @Sql=@sql+'o.Quantity,' END IF @OrderDate =1 BEGIN SET @Sql=@sql+'rderDate,' END IF @ReturnPolicy =1 BEGIN SET @Sql=@sql+'o.ReturnPolicy' ENDSET @sql=@sql +' ' +'from Customer c inner join Order o on c.CustomerId=o.CustomerID where c.CustomerType =' + '@CustomerType'EXEC(@sql)\[/code\]ENDRight now, many additional table columns (30 columns) had been added in the checkbox in the asp.net page. This is just adding too many bit type variables and if conditions to check those bit type variable and concatenate it to @sql based on those bit type variable in the stored procedure. Is there any better way to accomplish this? Please suggest.
rderItemName'IF @LastName =1 BEGIN SET @Sql=@sql+'c.LastName,' END IF @Phone =1 BEGIN SET @Sql=@sql+'c.Phone,' END IF @Email =1 BEGIN SET @Sql=@sql+'c.Email,' ENDIF @Quantity =1 BEGIN SET @Sql=@sql+'o.Quantity,' END IF @OrderDate =1 BEGIN SET @Sql=@sql+'rderDate,' END IF @ReturnPolicy =1 BEGIN SET @Sql=@sql+'o.ReturnPolicy' ENDSET @sql=@sql +' ' +'from Customer c inner join Order o on c.CustomerId=o.CustomerID where c.CustomerType =' + '@CustomerType'EXEC(@sql)\[/code\]ENDRight now, many additional table columns (30 columns) had been added in the checkbox in the asp.net page. This is just adding too many bit type variables and if conditions to check those bit type variable and concatenate it to @sql based on those bit type variable in the stored procedure. Is there any better way to accomplish this? Please suggest.