Build Dynamic WHERE Clause

Apr 14, 2008

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'CNN')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'AOL')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'ABC')

Thanks for your help

View 2 Replies

Build Dynamic WHERE Clause

Apr 14, 2008

Hello,

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyName = 'CNN'
AND CompanyName = 'AOL'
AND CompanyName = 'ABC'

P.S I know that above select doesn��t really make sense , but I have a bigger query that would be hard to explain in this topic so I just simplified it.

Thank you

View 8 Replies View Related

Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database

Jul 17, 2015

I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.

SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011'' in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)

View 9 Replies View Related

Build WHERE Clause

Apr 8, 2008

I have a stored procedure which expects one parameter @City

Let’s say @City holds pipe delimited value: "New York|London|Paris"

In my stored procedure I need to replace all pipes with OR operator? In other words my select statement needs to look like this :

SELECT * FROM City WHERE CityName= 'New York' OR CityName= 'London' OR CityName= 'Paris'

Thank you

View 7 Replies View Related

How To Build FROM Clause Dynamically

Mar 29, 2004

I look trough the forum, but did not find any simular problem. Somebody, help, please!
What I need to do is to write an algorithm which create a FROM clause for SQL query, using tables and joined fields, specified by the user. There could be up to 25 tables with any type of join (INNER, OUTER, FULL, CROSS). I know the basic structure of the FROM clause: "from T1 inner(or other type) join T2 on T1.field=T2.field" etc., but the main problem that users can specify tables in any order and I have to re-arrange them to create valid statement.

View 10 Replies View Related

How Do You Build A Dynamic WHERE Statement?

Mar 9, 2006

I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext.
How would I go about building this efficiently?
Would I Declare a bit value in the sp called WhereSet = 0
IF @client IS NOT NULL IF @WhereSet = 0 SET @Where = 'WHERE ClientID=@client' SET @SetWhere = 1 ELSE SET @Where = @Where + ' AND CleintID=@client' . . ....
Or would this be a lot easier using adhoc SQL instead of a Stored Procedure?
(note: I am using a SQL DataSource)
Please help, I am going bald from pulling my hair our...

View 2 Replies View Related

Trying To Build Dynamic Stored Procedure

Jun 6, 2008

My existing ASP 1.0 site keeps getting hacked using SQL injections. I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search.
I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck. Getting either errors or double LIKE statements. Need some help.
string[] SqlKWSrch;
SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ','); int AStop = SqlKWSrch.Length; int i = 0; foreach( string a in SqlKWSearch ) { if (i <= AStop) { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND "; } else { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' "; } i++; }
1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure
GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion.
PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1
RETURN

Thank you

View 5 Replies View Related

Build Dynamic Query Using Sp_executesql

Sep 20, 2004

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

Thanks

View 1 Replies View Related

How Build Web Application (asp.net + C# ) With Dynamic Database Schema?

Mar 26, 2008

Hi all,

I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).

With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).

Therefore, I want to ask all of you that
- Is there anyway to solve this problem?
- how to design database that meet extended requirements as I present above?

Thanks for all of your advices.

Quan.

View 17 Replies View Related

Build SQL Query From Dynamic Checkbox List

Nov 26, 2003

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.

View 1 Replies View Related

Build Dynamic Table Columns Issue

Jun 4, 2004

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!

J827

View 4 Replies View Related

SQL Server 2012 :: Build Search Condition Dynamic

Oct 6, 2015

IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'

[code]....

i am trying to build a dynamic where "or" clause finding difficulties.

View 7 Replies View Related

Dynamic Build SQL In Store Procedure Based On Select

Jul 23, 2005

I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL

View 3 Replies View Related

Using Dynamic IN Clause

May 9, 2007

I am looking to do a dynamic IN clause, such as the following.create procedured usp_SPTest

@variable varchar(150)
AS

select * from table
where ID IN (@variable)
GO

Then execuuting this withusp_SPTest '''1'',''2'''
Basically this won't work and gives an error
Syntax error converting the varchar value ''1','2'' to a column of data type int.
which makes sense. Is there anyway to do this outside of a dynamic SQL statement?
According to http://www.sommarskog.se/dynamic_sql.html#List it doesn't look promising but unpacking the list into a table is a bit over board for this in my opinion. Any help is appreciated.

View 3 Replies View Related

Need Help With Dynamic Where Clause

Jul 19, 2007

Hi. I can't seem to get my dynamic where clause right.

The pseudo code is such:

when gender is not null and is not 'show all' then create the condition "when tab1.gender = @gender".
and, when country is not null and is not 'show all' then create the condition "when tab1.country = @country".
and, finally, create the condition where tab1.bday_year is
between @toage AND @fromage (this will never be empty or null).

I have tried many variations, the most recent being below.
But, the resulting data does not act as it should (or it won't compile) :

Code:

WHERECASEWHEN @gender <> 'show all' THEN tab1.gender = @gender
WHEN @country <> 'show all' THEN tab1.country = @country
tab1.bday_year BETWEEN @toage AND @fromage END

thanks for any suggestions provided.

View 6 Replies View Related

Dynamic 'where' Clause

Nov 13, 2006

HiI need some advice on which direction to take!Consider this statement:SELECT business_name FROM myTable WHERE town = @town AND county = @countyMy problem is that i will not always have the @county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @county variable is not available, it has a value of 0.thanks againPs, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.

View 5 Replies View Related

Dynamic WHERE Clause

Dec 6, 2007

Hi all,

I pass a WHERE clause to a sp and use EXEC to execute it. it works fine if it does not include datetime Type in WHERE clause. however, if it did, the an error will be shown up "Unclosed quotation mark after the character string '2'.". I try to copy that clause and run as query in SQL server. there is no problem.

the Where clause is (Status='Open' or Status='On Going' or Status='Hold' or Status='Close') And (Found_Date BETWEEN ' '2007-11-01'' And ''2007-11-30'')

View 20 Replies View Related

Dynamic Where Clause

Sep 18, 2007

Hello everyone,

I want to build a dynamic where clause which makes :

WHERE column1 = (@parameter1 if @parameter1 is not null) / (anything if @parameter1 is null)

Basically I do not know how to set column1 = ANYTHING

Best regards and thanks.

View 5 Replies View Related

Dynamic Where Clause

Aug 23, 2007

I am trying to write a stored procedure usp_select using dynamic sql to select from a table. The stored procedure will accept the where clause and/or the where clause parameters. I have tried 3 different methods -

Method 1 -
exec usp_select @whereCondition='col1 like ''abc%'' and col2 = ''xyz'''
In usp_select, I'll build and execute the sql like -
set @sql = N'select * from table ' + @whereConition
exec sp_executesql @sql
(basically @sql becomes - select * from table where col1 like 'abc%' and col2 = 'xyz')

Method 2 -
exec usp_select @whereCondition='col1 like @p1 and col2 = @p2', @WhereParams='@p1=abc%,@p2=xyz'

In usp_select, I'll parse out the values in @WhereParams and then build and execute the sql like -

set @sql = N'declare @p1 nvarchar(10),
@p2 nvarchar(10);
set @p1 = ''' + @parsedValue1 + ''', @p2 = ''' + @parsedValue2 + '''; ' +

N'select col1 from table1 ' + @whereCondition

exec (@sql)

(basically @sql becomes - declare @p1 nvarchar(10), @p2 nvarchar(10);
set @pt = 'abc%', @p2 = 'xyz';
select col1 from table1 where col1 like @p1 and col2 = @p2)

Method 3 -

similar to Method 2 but exec(@sql) will be structured to become -
exec(declare @vparam nvarchar(100), @p1 nvarchar(10), @p2 nvarchar(10);

set @vparam='@p1 nvarchar(10), @p2 nvarchar(10)'

set @p1 = 'abc%', @p2 = 'xyz';

execute sp_executesql N''select col1 from table1 where col1 like @p1 and col2 = @p2', @vparam, @p, @p2)

When I run sql profiler on the 3 methods, method 1 and 2 always result in a Cache Miss on the entire sql structure.
On method 3, a Cache Miss always occurs on the first part of the sql, ie, the first 3 lines where I declare and set the variables. Then a Cache Hit will happen on the execute sp_executesql part.

Do I have any performance gain using method 3 with both a Cache Miss and a Cache Hit?

I hope this is not too confusing. Because I do not know the where condition to the select procedure and hardcoding the values as in method 1 always results in a Cache Miss, therefore, I come up with the ideas in Method 2 and 3.

Any advice would be appreciated.

View 5 Replies View Related

Dynamic Where Clause

Jan 29, 2008

I have a table (tblRestore) that has the following data
FilePath Included FileType
C:DataProjectsBeacon 1 Directory
C:DataProjectsBeaconPDA 1 Directory
C:DataProjectsBeaconBeaconVisual Studio 2005 0 Directory
C:DataProjectsBeaconVisual Studio 2005 0 Directory
C:DataProjectsBeaconPDAAuditPDAWM5 0 Directory

I want to use the data in this table to dynamically create a where clause that would look like this

WHERE (ParentDirectory + '' LIKE 'C:DataProjectsBeacon\%'
OR ParentDirectory + '' LIKE 'C:DataProjectsBeaconPDA\%')

AND (ParentDirectory NOT LIKE('C:DataProjectsBeaconVisual Studio 2005%')

AND ParentDirectory NOT LIKE('C:DataProjectsBeaconBeaconVisual Studio 2005%')

AND ParentDirectory NOT LIKE('C:DataProjectsBeaconPDAAuditPDAWM5%')

)

Can I do this using Sql (Sql Compact 3.0)?

thanks,

View 1 Replies View Related

Dynamic Where Clause

Mar 10, 2008

Hi

I'm using SQL Server 2000...

In a stored procedure I am trying to use a parameter in a where clause which is set dependent upon another parameter... ie if report 1 is run, I want to evaluate against the StartDate field ... otherwise evaluate against the EndDate field.

declare @StartDt datetime;
declare @EndDt datetime;
declare @WhereDate varchar(30);

if @report = 1 then
begin

set @WhereDate = 'Table.EndDate'
end
else set @WhereDate = 'Table.StartDate'

select *
from MyTable
Where @WhereDate >= @StartDt and @WhereDate < @EndDt

I'm getting syntax errors, so my question is.... is it possible... if so what syntax should I use?

Cheers

Jon

View 8 Replies View Related

WHERE-Clause With Dynamic Field?

May 21, 2007

Hi,
i have a stored procedure like this:
CREATE PROCEDURE dbo.spQuery1

@dynamicField nvarchar(10)

AS

SELECT id, productName, productDescription

FROM products

WHERE @dynamicField = 'True'

RETURN
The products table has id, productName, productDescription, property1, property2, property3 fields
So my query variable should be, for example, "property1" and the result should be every row with "property1" set to true... but it doesnt work with the sql like this...
any help?

View 8 Replies View Related

Dynamic WHERE Clause Alternative.

Jun 26, 2007

I have 3 tables: Authors, Employee and Stores.
I need to create a stored procedure which will take 3 comma delimited parameters, to be able to query above 3 tables.
Basically my front end user can say give me Authors with last name starting from ‘A,B’ and Employee with first name starting from ‘J,N,K’ and Stores with city starting from ‘New, Los’.
So, stored procedure call will look like this Exec myStoredProcedure 'A,B' , 'J,N,K', 'New,Los' .

My question is, how should I handle WHERE clause in stored procedure if I don’t want it to be a “dynamic WHERE�?.

Thank you

View 1 Replies View Related

How To Create Dynamic T-SQL Clause

May 6, 2008

Hi,

I have a project which is need dynamically t-sql clause...I've created interface for the create dynamic t-sql clause.
But the clause should get data from relational database. It needs inner join...so it's very hard to do it..

for example

lkpProduct

id product
1 Mouse
2 Keyboard

3 Modem
4 Monitor

main

id productid

1 4
2 1
3 2
4 3
5 4

You can get this table with 2 ways like this..

1. Select main.id, lkpProduct.product from main,lkpProduct where main.productid = lkpProduct.id
2. Select id, case productid when 1 then 'Mouse'.....end as product from main

mytable

id product
1 Monitor
2 Mouse
3 Keyboard
4 Modem
5 Monitor

My question is; how can i use CASE function dynamically ?? Mouse should get from lkpProduct....I do not want to write long table manually..

I hope it is clear for you..

Regards.

View 4 Replies View Related

Parameterized IN Clause In Dynamic SQL

Nov 16, 2007

Hi,

I am trying to build a parameterized query where I pass a set of integer values into the dynamic sql. Please see below example.

DECLARE @SQLQUERY NVARCHAR(4000)
DECLARE @PARAMDEF NVARCHAR(1000)
DECLARE @VALUES VARCHAR(100)

SET @PARAMDEF = N'@IN_VAL VARCHAR(100)'

SET @VALUES = '1,2,3,4'

SET @SQLQUERY = 'SELECT * FROM TABLEA WHERE COLUMNA IN (@IN_VAL)'

EXEC SP_EXECUTESQL @SQLQUERY,@PARAMDEF,@IN_VAL=@VALUES

This fails with the error "cannot convert varchar to numeric". I believe since ColumnA is numeric, its trying to convert the dynamic paramter to numeric leading to the failure.

has someone implemented an In clause as a parameter? Please do not tell me that I can append the values as string and construct a dynamic query. I want to use a parameterized version. I will be calling this repeatedly and dont want recompile overhead.

TIA

View 7 Replies View Related

Dynamic Where Clause With If..else Or Case

Jun 13, 2006

Hello all...

I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select

i.e

if...@where = @where + ' llll '

if...@where = @where + ' llll '

select @statement = @statement + @where

exec(@statement)

I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.

Is it possible to use either statement inside a where clause??

i.e

where if a = 1 then d=e

else contains(.....)

thanks

View 9 Replies View Related

Dynamic WHERE Clause To Stored Procedure

May 25, 2004

Hi all!
I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:

---------------------------------------------------
@crit varchar(100)

SELECT fldID, fldName FROM tblUsers
WHERE @crit
----------------------------------------------------

Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.

cheers!
pelle

View 2 Replies View Related

Dynamic Where Clause In Stored Procedure

Jul 23, 2004

Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @Path, @Status, @Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.

View 1 Replies View Related

Using A UDF To Return Values For A Dynamic WHERE IN () Clause

Mar 2, 2006

Greetings,

I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...

The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.

The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()

RETURNS TABLE

AS

BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END

Then I could call this function by doing something such as:

SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())

I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.

Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"

(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()

RETURNS @t1 TABLE (i INT)

AS

BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END

Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.

Any help is very much appreciated.

- Jeff

View 3 Replies View Related

Help With Dynamic Where Clause In Stored Procedure

Aug 20, 2007

I have a stored procedure being called based on user search criteria. Some, the colour and vendor fields are optional in the search so i do not want that portion of the procedure to run.

at this point i keep getting errors in the section bolded below
it never seems to recognize anything after the if @myColours <> 'SelectAll'

CREATE Procedure PG_getAdvWheelSearchResults3
(
@SearchDiameter NVarchar( 20 ),
@SearchWidth NVarchar( 20 ),
@minOffset int ,
@maxOffset int ,
@boltpattern1 NVarchar( 20 ),
@VendorName NVarchar( 40 ),
@myColours NVarchar( 40 )
)
As
BEGIN TRANSACTION
SELECT *, dbo.VENDORS.*, dbo.WHEEL_IMAGES.Wheel_Thumbnail AS Wheel_Thumbnail, dbo.WHEEL_IMAGES.Wheel_Image AS Wheel_Image,
dbo.WHEELS.*, dbo.VENDOR_IMAGES.Vendor_Thumbnail AS Expr1, dbo.VENDOR_IMAGES.Vendor_AltTags AS Expr2
FROM WHEEL_CHARACTERISTICS INNER JOIN
dbo.VENDORS ON WHEEL_CHARACTERISTICS.Vendor_ID = dbo.VENDORS.Vendor_ID INNER JOIN
dbo.WHEEL_IMAGES ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEEL_IMAGES.Wheel_ID INNER JOIN
FILTER_CLIENT_WHEELS5 ON WHEEL_CHARACTERISTICS.Wheel_ID = FILTER_CLIENT_WHEELS5.Wheel_ID INNER JOIN
dbo.WHEELS ON WHEEL_CHARACTERISTICS.Wheel_ID = dbo.WHEELS.Wheel_ID INNER JOIN
CLIENT_WHEEL_PRICES5 ON FILTER_CLIENT_WHEELS5.Client_ID = CLIENT_WHEEL_PRICES5.ClientId AND
WHEEL_CHARACTERISTICS.Wheel_Char_ID = CLIENT_WHEEL_PRICES5.Wheel_Char_ID INNER JOIN
dbo.VENDOR_IMAGES ON dbo.VENDORS.Vendor_ID = dbo.VENDOR_IMAGES.Vendor_ID
WHERE (dbo.VENDORS.Vendor_Active = 'y') AND (FILTER_CLIENT_WHEELS5.FCW_Active = 'y')
AND (FILTER_CLIENT_WHEELS5.Client_ID = '1039')
AND (WHEEL_CHARACTERISTICS.Wheel_Diameter =@SearchDiameter)
AND (WHEEL_CHARACTERISTICS.Wheel_Width =@Searchwidth)
AND (WHEEL_CHARACTERISTICS.Wheel_Bolt_Pattern_1 = @boltpattern1)

if @myColours <> 'SelectAll'
and WHEEL_CHARACTERISTICS.Wheel_Search_Colour = @myColours
end if

AND (cast(WHEEL_CHARACTERISTICS.wheel_Offset as int(4)) BETWEEN @minOffset AND @maxOffset)

ORDER BY CLIENT_WHEEL_PRICES5.Price asc
COMMIT TRANSACTION
GO

Anyone know how i should word the if...statements?
I have not found anything that works yet.
Thanks

View 2 Replies View Related

Highly Dynamic Where Clause In A Stored Procedure

Apr 23, 2008

I have a situation where I'll need to get results from tables based on totally arbitrary filters. The user can select the field to compare against, the value, the comparison operator, and the boolean operator, so each bit in brackets would be configurable:[field] [>] [value] [and]The user can specify an arbitrary number of these, including zero of them. I like the coalesce function for situations that are a little more structured, but I think I'm stuck generating a dynamic query for this -- please correct e if I'm wrong!

View 1 Replies View Related

How To Pass Dynamic Parameter To Order BY Clause

Sep 13, 2004

Hi,

I use a DataGrid to show the data, and I want it has a sorting and Paging function,
so I use dataset to collect the data from middle tier function and stored procedure.
I have code in aspx page like BindData(ViewState("SortExpr")).

In the stored procedure I pass SortExpr as parameter as following:

CREATE Procedure Ruying_AutoSearch10
(
@Make varchar(50),
@Model varchar(50) = NULL,
@Condition varchar(20) = NULL,
@Miles float,
@Zipcode varchar(5),
@SortExpr varchar(100)
)
AS

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'

if @RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @Make
AND a.Model = IsNull(@Model,a.Model)
AND a.Condition = IsNull(@Condition, a.Condition)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
ORDER BY @SortExpr
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO

but I got the error as "variables are only allowed when ordering by an expression referenceing
a column name". How I fix this error? Please help.

Thanks.

Lin

View 9 Replies View Related

Stored Procedure With Optional/dynamic Where Clause

Apr 21, 2006

If I do this with a function and multiple inline sql statements, I could probably do it much easier, but here at work, sprocs are required, and I can't seem to stretch my knowledge and Google searches far enough to find the answer. Plus, I don't really think that creating 4 separate sProcs is the most efficient way of doing this
I need to select and return 8 columns from a table, but the problem is I need to feed the sProc parameters in such a way, that I can use different criteria in the Where Clause.
for instance, I need to combine these 4 select statements into one:1. Select (fields) from (table) Where TechID=@TechID and Status=@Status)2. Select (fields) from (table) Where TechID=@TechID3. Select (fields) from (table) Where OrdNum=@OrdNum3. Select (fields) from (table) Where CustNum=@CustNum
In all instances, the fields and the table are the same - how can I combine all these possible Where clauses (if/then - Select Case?) so that it's only one Stored Procedure?
(or, is this even possible?)

View 4 Replies View Related