parameters for query

I'm not sure if any1 can answer is on this forum, but any help would be VERY appriceted...

I am creating a report using Visual Studio.Net..I want to write a query using parameters BUT in this case I dont no what the parameter will be...

ie the user can enter a customer account OR a customer group
and sort can be on product OR total sales...

Any ideas?!

Thanks!!! :)The intuitively obvious answer would be to use a stored procedure instead of a query, then build the "smarts" into the procedure. There may be better answers, but that one jumps out at me.

-PatP|||The intuitively obvious answer would be to use a stored procedure instead of a query, then build the "smarts" into the procedure. There may be better answers, but that one jumps out at me.

-PatP

coool... neva dun that wiv stored proc... how would i do that? or do u know any useful links?!

THANKS :)|||Sounds like dynamic sql to me|||Sounds like dynamic sql to meThat is the fools way out... Infinite flexibilty, infinite risk.

I was hoping to kind of leash things a bit, finding a good compromise between flexibility and safety.

-PatP|||So would the best idea be to write a stored proc somefing like this?!:

CREATE PROCEDURE test (@.parameter varchar, @.variable varchar) AS
SELECT * from SALES
where @.parameter = @.variable
GO|||CREATE PROCEDURE test (@.parameter1 varchar, @.parameter2 varchar) AS
SELECT * from SALES
where (Column1 = @.Parameter1 or @.Parameter1 is null)
and (Column2 = @.Parameter2 or @.Parameter2 is null)
...but dump the "select *", and let your interface handle the sorting.|||CREATE PROCEDURE test (@.parameter1 varchar, @.parameter2 varchar) AS
SELECT * from SALES
where (Column1 = @.Parameter1 or @.Parameter1 is null)
and (Column2 = @.Parameter2 or @.Parameter2 is null)
...but dump the "select *", and let your interface handle the sorting.

That cant be write... if they enter a value for parameter 1 (eg 'X' only then the query will become:

where (Column1 = 'X')
and (Column2 is null)

right?! That would not be write...it would return nothing....

:eek: I dun no how 2 right it so the user can enter a account no OR customer name...|||would this work:

if @.Account Is Not Null then
write query using @.Acount in where part
break
else if @.Group Is Not Null then
write query using @.Group in where part

break
end

??|||No, but you should set defaults for the parameters, like so:
CREATE PROCEDURE test (@.parameter1 varchar = null, @.parameter2 varchar = null) AS
SELECT * from SALES
where (Column1 = @.Parameter1 or @.Parameter1 is null)
and (Column2 = @.Parameter2 or @.Parameter2 is null)
If a parameter is submitted, the result set will be filtered on that value. If the parameter is ommitted, no filtering will occur on the column.