CASE in WHERE clause?

I would like to select customers from my database depending on some criteria that the users choose - such as area code, company size etc. Since i have about 15 criteria and the users want to be able to choose many criteria for one selection i can't make one procedure for each criteria - i would end up with about a hundred procedures. I want to let the users choose what criteria to search on and have ONE single select statement that return the customers. Something like:

select name, address, city etc
from customer
where if users want to see customers of a certain type then type = @.myInParameterForType
and if users want to see customers from a certain area then area = @.myInParameterForArea

is this possible? sorry if i made you all confused...my english is not perfect! Thanks in advance!Yes, you may use CASE in where|||one easy (but not very performant) way is to build your "where" clause in your application (ie:web form ) and send it to your sp:
------------------------
CREATE PROCEDURE mySP

(
@.WhereClause varchar(4000)
)

AS
Declare @.SQL varchar(6000)

begin

set @.SQL = 'SELECT field1, field2 ... from myTable where ' + @.WhereClause + ' ORDER BY someID'

end

EXEC (@.SQL)

RETURN

GO
------------------------

when you have many optional parameter it is very conveniant

Cascading Prompts

Hi everyone
I've a report set up which requires cascading prompts to select report
criteria. This works fine on my development box, but not once it's been
deployed to the report server.
The error I'm getting is:
One or more data sources is missing credentials.
The scenario is that a list of available years is pulled from the database
(Sql Server), the selected year is passed into a stored procedure to pull out
a list of available, which in turn is used to pull back available days.
I'm sure I've simply over looked something in the data source setup.
I'm using a custom data source, with credentials stored on the server. The
account details are for a domain admin account, with full rights on all boxes.
I have checked "Use as Windows credentials when connecting to the data source"
Any ideas?
TIA
JoeJust go to the datasource again and retype your passwords and save it. When
going to reports you must have just gone into datasource credential part as
well. so it assumes that you are going to enter a new one.
Amarnath
"Joe" wrote:
> Hi everyone
> I've a report set up which requires cascading prompts to select report
> criteria. This works fine on my development box, but not once it's been
> deployed to the report server.
> The error I'm getting is:
> One or more data sources is missing credentials.
> The scenario is that a list of available years is pulled from the database
> (Sql Server), the selected year is passed into a stored procedure to pull out
> a list of available, which in turn is used to pull back available days.
> I'm sure I've simply over looked something in the data source setup.
> I'm using a custom data source, with credentials stored on the server. The
> account details are for a domain admin account, with full rights on all boxes.
> I have checked "Use as Windows credentials when connecting to the data source"
> Any ideas?
> TIA
> Joe
>|||Hi Amarnath
Unfortunately, I've tried that, and still no joy. It's as though the
credentials a being dropped when the page refreshes. When the report first
loads up, it queries the database to get the values for the first drop down
list. This works fine, so the credentials are OK at that point. Once a value
is selected from the first drop down list, I need to use that value to
populate the second. It's at this point I'm getting the error message.
Joe
"Amarnath" wrote:
> Just go to the datasource again and retype your passwords and save it. When
> going to reports you must have just gone into datasource credential part as
> well. so it assumes that you are going to enter a new one.
> Amarnath
> "Joe" wrote:
> > Hi everyone
> >
> > I've a report set up which requires cascading prompts to select report
> > criteria. This works fine on my development box, but not once it's been
> > deployed to the report server.
> >
> > The error I'm getting is:
> >
> > One or more data sources is missing credentials.
> >
> > The scenario is that a list of available years is pulled from the database
> > (Sql Server), the selected year is passed into a stored procedure to pull out
> > a list of available, which in turn is used to pull back available days.
> >
> > I'm sure I've simply over looked something in the data source setup.
> >
> > I'm using a custom data source, with credentials stored on the server. The
> > account details are for a domain admin account, with full rights on all boxes.
> >
> > I have checked "Use as Windows credentials when connecting to the data source"
> >
> > Any ideas?
> >
> > TIA
> >
> > Joe
> >|||check the name of the stored procedure. We had this issue when some
users were not creating the procedures using "dbo.spname".
It works on your box if you were the one who created the procedure, but
will not run when you upload the report to the report server.
Joe wrote:
> Hi Amarnath
> Unfortunately, I've tried that, and still no joy. It's as though the
> credentials a being dropped when the page refreshes. When the report first
> loads up, it queries the database to get the values for the first drop down
> list. This works fine, so the credentials are OK at that point. Once a value
> is selected from the first drop down list, I need to use that value to
> populate the second. It's at this point I'm getting the error message.
> Joe
> "Amarnath" wrote:
> > Just go to the datasource again and retype your passwords and save it. When
> > going to reports you must have just gone into datasource credential part as
> > well. so it assumes that you are going to enter a new one.
> >
> > Amarnath
> >
> > "Joe" wrote:
> >
> > > Hi everyone
> > >
> > > I've a report set up which requires cascading prompts to select report
> > > criteria. This works fine on my development box, but not once it's been
> > > deployed to the report server.
> > >
> > > The error I'm getting is:
> > >
> > > One or more data sources is missing credentials.
> > >
> > > The scenario is that a list of available years is pulled from the database
> > > (Sql Server), the selected year is passed into a stored procedure to pull out
> > > a list of available, which in turn is used to pull back available days.
> > >
> > > I'm sure I've simply over looked something in the data source setup.
> > >
> > > I'm using a custom data source, with credentials stored on the server. The
> > > account details are for a domain admin account, with full rights on all boxes.
> > >
> > > I have checked "Use as Windows credentials when connecting to the data source"
> > >
> > > Any ideas?
> > >
> > > TIA
> > >
> > > Joe
> > >|||Was this issue resolved? As I seem to be having the same problem. I have just
upgraded to the SP2 CTP and when I use cascading parameters I get the same
error "One or more data sources is missing credentials". I have tried
various options on my data source including windows integrated and sql logins
but problem remains.
Regards,
David S

Capturing the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.