"Rudy" wrote:

> Hi Uri!
>
> I actually saw that earlier, and tried it. But it didn't seem to work. I
> don't have any NULL values in my data, maybe I should chnage it a littl bit.
> Here is what I have now. This still brings back everything.
>
> @FName nvarchar(50)=NULL,
> @LName nvarchar(50)=NULL,
> @DOB nvarchar(50)=NULL,
> @Acct nvarchar(50)=NULL,
> @Login nvarchar(50)=NULL,
> @Status nvarchar(50)=NULL,
> @Rmark nvarchar(255)=NULL,
> @Room nvarchar(50)=NULL,
> @Age nvarchar(50)=NULL,
> @Type nvarchar(50)=NULL,
> @Misc bit = Null,
> @debug bit = 0
>
> AS
> DECLARE @sql nvarchar(4000),
> @paramlist nvarchar(4000)
>
>
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> SELECT @sql =
> 'SELECT Active_Orders.First_Name, Active_Orders.Last_Name,
> Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,
> Locations.Loct_Desc, Active_Orders.Rm_Desc,
> Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat,
> Active_Orders.Order_ID, Active_Orders.Login
> FROM Active_Orders INNER JOIN
> Order_Status ON Active_Orders.Status_ID =
> Order_Status.Status_ID INNER JOIN
> Locations ON Active_Orders.Location_ID =
> Locations.Location_ID
> WHERE 1 = 1';
> SET @paramlist = N'@Fname nvarchar(50), @Lname nvarchar(50), @DOB
> nvarchar(50),
> @Acct nvarchar(50), @Login nvarchar(50), @Status nvarchar(50),
> @Rmark nvarchar(50), @Room nvarchar(50), @Age nvarchar(50),
> @Type nvarchar(50), @Misc bit'
> If @FName IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.First_Name LIKE @xFname + "%"'
>
> If @LName IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Last_Name LIKE @xLname + "%"'
>
> If @DOB IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.DOB LIKE @xDOB + "%"'
>
> If @Acct IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Account_Number LIKE @xAcct + "%"'
>
> If @Login IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Login LIKE @xLogin + "%"'
>
> If @Status IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Status LIKE @xStatus + "%"'
>
> If @Rmark IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Remarks LIKE @xRmark + "%"'
>
> If @Room IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Rm_Desc LIKE @xRoom + "%"'
>
> If @Age IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Age LIKE @xAge + "%"'
>
> If @Type IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Type LIKE @xType + "%"'
>
> If @Misc IS NOT NULL
> SET @SQL = @SQL + N'And Active_Orders.Misc LIKE @xMisc + "%"'
>
> --If @debug = 1
> --PRINT @sql
>
> SELECT @paramlist = '@xFName nvarchar(50),
> @xLName nvarchar(50),
> @xDOB nvarchar(50),
> @xAcct nvarchar(50),
> @xLogin nvarchar(50),
> @xStatus nvarchar(50),
> @xRmark nvarchar(255),
> @xRoom nvarchar(50),
> @xAge nvarchar(50),
> @xType nvarchar(50),
> @xMisc bit '
>
> EXEC sp_executesql @sql, @paramlist,
> @Fname, @Lname, @DOB, @Acct, @Login, @Status,
> @Rmark, @Room, @Age, @Type, @Misc
>
>
> THANKS!
>
> Rudy
>
> "Uri Dimant" wrote:
>
> > Rudy
> > http://www.sommarskog.se/dyn-search.html
> >
> >
> > "Rudy" <Rudy@discussions.microsoft.com> wrote in message
> > news:445792ED-6F6F-44F7-B127-8105FB09F8D7@microsoft.com...
> > > Hello All!
> > >
> > > I have a search query I would like to perform. For example, I have a table
> > > with Last, First and DOB columns. I would write this as a SP so I can call
> > > it
> > > in my ASP.net application.
> > > What I would like to do if a user want to search by first name only, it
> > > will
> > > return the results. Value = 'Joe, so it brings back all the joes. Now if
> > > the
> > > user brings the last name as Gibbs, I would like to have it return as a
> > > AND
> > > statement. So it brings back Joe Gibbs.
> > > Now I know if I have it as a OR operator, I can select any 1 or 2 or all 3
> > > variables that I want. But is brings back all the joes, and all the Gibbs.
> > > If
> > > I use the AND operator, now I have to fill in all 3 variables. I would
> > > like
> > > to have both worlds.
> > >
> > > Any ideas what is the best way to do this.?
> > >
> > > Thanks!
> > >
> > > Rudy
> >
> >