please help me to cearte this stored procedure

Hi ,

I want to make a report of records of a table, there is abut 15 fields that this report based on them , so we need a select query like this

Select f1,f2from Table1where f3=@.f3and f4=@.f4 and ….and f17=@.f17

-- f1 = field1 and ….

The problem is sometimes @.fs are empty, for example if @.f4was empty so "and f4=@.f4" should be excluded from the select query .(and it means there is no limitation for f4 field)

I know, probably I couldn't explain my purpose very well, but I hope somebody kindly try to understand it .

how can I perform that in a stored procedure?

Please help me

Thank you

try it like this:

SELECT f1,f2FROM Table1WHERE (f3=@.f3OR @.f3ISNULL)AND (f4=@.f4OR @.f4ISNULL)AND ….AND (f17=@.f17OR @.f17ISNULL)

|||

Is in your example @.f4 empty or null? I think null, so I created the following query for you:

Select

f1from table1where f1= @.f1and(f4= @.f4or @.f4isnull)

This query selects all the records where f1 matches @.f1 and f4 matches @.f4 or @.f4 is null (and will be ignored then).

I hope this helps

Richard

|||

mbanavige & richardsoeteman.net thank you very, very much!.

|||

Hi

Assume there are 3 tables like these:

Table0

Primary key

Name

1

Name1

2

Name2

3

Name3

Table 1:

Foreign key

Column1

1

Data1

2

Data1

Table2:

Foreign key

Column2

2

Data2

3

Data2

And there are 2 parameters that they may be null: @.Data1 and @.Data2

I need a select query that

-selects "Name2" from Table0 if:

@.Data1="Data1"

@.Data2="Data2"

-selects "Name1, Name2" from Table0 if:

@.Data1="Data1"

@.Data2=null

-selects "Name2, Name3" from Table0 if:

@.Data1=null

@.Data2="Data2"

And selects "Name1, Name2, Name3" from Table0 if both of@.Data1 &@.Data2 wasnull.

I know I did not explain very well again but it's the final step of my project and I really need help. So please help me again

Thanks,

|||

Same concept:

Read this post:http://forums.asp.net/thread/1440706.aspx

|||

thank you Mike,

What about parameters that areint ordecimal and we want to ignore them , they can not benull ,they can be 0.

thank you Mike,

|||

You can use the same concept

Select

f1from table1where(f1= @.f1or @.f1=0)

|||Thank you Richard,

Please help me SQL 2005 and Access Databases

I have a restaurant POS application that is written in VB 6 and the data is being written to a access database. I need to access this database over the internet. I need to get certain tables (current sales) in this database to automatically send the updated data to a Web Server. My questions are: What is the best way to do this? Do I need to have sql express loaded on site and let it do the interactions with the Access server and then connect to SQL express over the internet? I am new to this and not sure of the best way to design it. Do I use Report services or analysis services to design?

Thanks

What about using a WebService which exposes the data grabbing functionality. The WebService can be additionally secured via various methods. I would not use SQL Server as a gateway to get the data in that case.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you. I will look into this. I don't really understand the background process to get the data but will learn.

Bob

|||

Web Service is the only way to go if you're going to use SQL Express. You can not connect directly to SQL Express through the internet as it does not support HTTP Endpoints.

Mike

Please help me solve this problem

Hello All,
I am new to crystalreport am using crystalreport 8.5. I have to provide the link for
a column whose value is > 1000. i don't want to suppress the values other than1000.
My Problem is the link should be enabled only when the value is > 1000. Please share your experience to solve this problem.
Thanks in advance
Vasuformat the field of the column using a formula..

if(value>100, "link text" & value, value)

?|||Thanks a lot cjard. it works well. the link is enabled for value > 1000
thanks and regards
Vasu

Please help me set up SQL Express

I am extremely new to ASP, SQL, everything. I'm trying to set up a simple username/password system for our website (built with Expression Web) that would only be used by about 15 employees. I am following directions in Zak Ruvalcaba's book on Expression Web, and I'm stuck. I have .NET Framework and SQL Express downloaded and installed. I'm running the ASP.NET SQL Server Setup Wizard, but Setup fails because it is unable to connect to SQL Server database. I'm completely lost. Any help would be greatly appreciated. Thanks.

How to configure services for Asp.net is covered in the link below, post again if you still need help.

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Please help me populate my date fields

Hi,

I have the following table:

CREATE TABLE [Orders] (
[OrdID] [int] ,
[OrderDate] [datetime] ,
[OrderDateONLY] [datetime],
[OrderDayName] [nvarchar] (15),
[OrderMonth] [nvarchar] (25),
[OrderDayOfMonth] [smallint] NULL ,
[OrderWeekofYear] [smallint] NULL
)
GO

The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:

Insert ORDERS
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
select
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
from
(select convert (char (8), OrderDate, 112)as d
from ORDERS
) as xupdate orders set orderdateonly=convert(varchar,orderdate,105),order Dayname=datename(dw,orderdate),orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||populate only orderDate in this

CREATE TABLE [dbo].[Orders] (
[OrdID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[OrderDateONLY] AS ([orderdate]) ,
[OrderDayName] AS (datename(weekday,[orderdate])) ,
[OrderMonth] AS (datename(month,[orderdate]))
.....
)|||harsal_in,

Thanks for the reply...i think i'm very close now except for one problem...when I run the statement i get an error message saying:

"The conversion of a char datatype to datetime datatype resulted in an out of range datetime value"

The problem seems to be with the "orderdateonly=convert(varchar,orderdate,105)" but I can't figure out :confused:|||it works very well on my machine.
anyways try this:
update orders set orderdateonly=convert(datetime,convert(varchar,ord erdate,105)),orderDayname=datename(dw,orderdate),o rderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)|||harshal_in,

I wonder what's wrong at my end becuase I am still getting the same error. I have SP1 and could this be the problem? I'll update to SP3 but for now is there a solution.

Thanks.|||Format 120 (or 121 Brett...) are better for date conversion because they are interpreted unambiguously by SQL Server.

update orders
set orderdateonly=convert(datetime,convert(char(10),or derdate,120)),
orderDayname=datename(dw,orderdate),
orderMonth=datename (m,orderdate),
orderDayOfMonth=day(orderdate),
orderweekofyear=datename(wk,orderdate)

...but the problem with your design here is that you will need to ensure that anytime the orderdate is modified that all the other columns are updated as well. I recommend that instead of having columns to store these values directly you should create them as calculated fields using the above formulas. This way, they will automatically be synchronized with the orderdate field.|||Unless it's a warehouse...or for performance reasons...which I can't see it...

NEVER store derived data....

What's the reason..

If you store derived data, then you get caught in the trap of making sure the derived data is TRUE to the source...all the time...

Which means an additional process..

This is OLTP, right?

in OLAP, it done once and never changes, so it is TRUE at the time of the derivation

OLTP is fluid, and alway in a state of flux...(like the capacitor :D )

So why?sql

Please help me out-no values for all the cells of the cubes?

Hi, all here,

I encountered a very very weird problem-I changed nothing, but suddenly all cell values are empty for one of my cubes? But the data is totally fine viewing from the data source view?

Why is that?

Please help me out and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi, experts,

Please help me out!

I am looking forward to hearing from you.

Thanks.

With best regards,

Yours sincerely,

|||Hi

Make sure that you havne't remove the 'CALCULATE' command in your script editor under the calculations tab.

Chris.
|||

Hi,Chris,

Thank you so much indeed! I really appreciate your brilliant help so much!

With kindest regards,

Yours sincerely,

please help me out

my mdf and ldf are in 1 drive only. i am facing query timeout problem for
this server which is mainly used for employees
if i separate ldf into another drive will there will be any perf benefit
can i add another log file
how to clean up my log fileraghu veer wrote:
> my mdf and ldf are in 1 drive only. i am facing query timeout problem
> for this server which is mainly used for employees
> if i separate ldf into another drive will there will be any perf
> benefit can i add another log file
> how to clean up my log file
No way to know for sure given the limited amount of information.
Assuming you do not have SQL tuning problems or CPU-related issues (not
enough CPU or other services/applications using too much), then a drive
change should be in order. It's always a good idea to separate data and
log files.
A query timeout occurs when you set a maximum limit for query execution
in your code. Have you set a maximum timeout value? Make sure you roll
back the transaction when the query times out.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||BOL has this
SET QUERY_GOVERNOR_COST_LIMIT
Overrides the currently configured value for the current connection.
Syntax
SET QUERY_GOVERNOR_COST_LIMIT value
Arguments
value
Is a numeric or integer value indicating if all queries are allowed to run
(value of 0) or if no queries are allowed to run with an estimated cost
greater than the specified nonzero value. If a numeric value is specified,
Microsoft? SQL Server? truncates it to an integer.
Remarks
Using SET QUERY_GOVERNOR_COST_LIMIT applies to the current connection only
and lasts the duration of the current connection. Use the query governor cos
t
limit option of sp_configure to change the server-wide query governor cost
limit value. For more information about configuring this option, see
sp_configure and Setting Configuration Options.
The setting of SET QUERY_GOVERNOR_COST_LIMIT is set at execute or run time
and not at parse time.
Permissions
SET QUERY_GOVERNOR_COST_LIMIT permissions default to members of the symin
fixed server role.
Regards
R.D
--Knowledge gets doubled when shared
"David Gugick" wrote:

> raghu veer wrote:
> No way to know for sure given the limited amount of information.
> Assuming you do not have SQL tuning problems or CPU-related issues (not
> enough CPU or other services/applications using too much), then a drive
> change should be in order. It's always a good idea to separate data and
> log files.
> A query timeout occurs when you set a maximum limit for query execution
> in your code. Have you set a maximum timeout value? Make sure you roll
> back the transaction when the query times out.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||R.D wrote:
> BOL has this
> SET QUERY_GOVERNOR_COST_LIMIT
> Overrides the currently configured value for the current connection.
> <SNIP>
Are you saying you are setting a governor limit? If so, I wouldn't think
resource overutilization on the server would cause queries to not run
using this setting. Could you clarify?
David Gugick
Quest Software
www.imceda.com
www.quest.com