Upsizing query containing Form reference

[tani1978]

Hello Friends
I have a question related to upsizing. Can someone tell me how to upsize a query that contain Form reference such as

Select * from Orders
Where CustomerID LIKE Forms!Customers!CustomerID

 

[hmckillop]

What do you mean by upsizing -
This seems like an MS Access query
the equivalent in sql is

declare @ParameterValue Integer
SELECT * FROM ORders
WHERE CustomerID = @ParameterValue --(If its an ID you would unlikely want to use the LIKE comparison operator)

Where @ParameterValue is set from your client application.

Try the MS Access forum for more help.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[tani1978]

I mean by upsizing how this MS Access query would work in MS SQL Server which is Transact-SQL based.

 

[jbenson001]

You have to pass the sql a parameter:

declare @customerid -- some datatype.  You can't use like with numeric values, only character values.
Select * from Orders
Where CustomerID LIKE @customerid

Jim

 

[tani1978]

Shouold it be rewritten as Stored procedure, function or as a view. What will be the optimal solution in this type of query?

 

[SQLBI]

tani1978,

If its going to be called by your client app then i'd do it as a proc.

a. You then only have a single item to maintain and b. You can call the proc from other areas of your app if necessary without recoding.

One thing from a performance point of view, get rid of 'SELECT *' and list the columns specifically, this will make your query execution faster.

Hope this helps.

Cheers,
Leigh

Sure, if it has a microchip in it, it must be IT... Now what seems to be the problem with your toaster...?

 

[gmmastros]

I agree with Leigh, make a stored procedure. Also, you should only use LIKE if you are comparing PORTIONS of a string.

The proc.

Create Procedure GetOrderForCustomer
  @CustomerId Integer
As
Select Field1, Field2, Field3
From   Orders
Where  CustomerId = @CustomerId

You use LIKE when you want to do pattern matching on strings. This is handy for lookups (when you want to find a particular record in a long list of things).

Ex.

suppose you had a table like this....

Create
table #Temp(
CustomerId Integer,
CustomerName VarChar(100))

Insert Into #Temp(1, 'Microsoft')
Insert Into #Temp(1, 'Oracle')
Insert Into #Temp(1, 'IBM')

You could then write queries like...

Select CustomerId, CustomerName
From #Temp
Where CustomerName Like '%o%'
-- This query would return Microsoft and Oracle

Hope this helps.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom