|
Hi all,
i was trying to do a mutiple select with following fileds
ServiceCategoryName(field)->ServiceCategory(Table)
ServiceName(field)->Service(Table)
ServiceLocation(field)->Location(Table)
Created Date,SendersName,SendersEmaild(fields) ->ServiceItemForward (Table)
DB:SQLServer 2008
and the sp i wrote is following
what changes should i make to this
ALTER PROC [dbo].[usp__Search_ServiceItemForward]
@CategoryID int,
@ServiceID int,
@LocationName varchar(100),
@createdFromDate date,
@createdToDate date,
@sender varchar(100),
@senderEmail varchar(100)
AS
if(@createdFromDate is null)
set @createdFromDate =dateadd(d,-7, CONVERT(date, GETDATE()))
if(@createdToDate is null)
set @createdToDate=CONVERT(date, GETDATE())
Begin
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
SELECT dbo.ServiceItemForward.* FROM dbo.ServiceItemForward INNER JOIN
dbo.ServiceItems ON dbo.ServiceItemForward.ServiceItemID = dbo.ServiceItems.ServiceItemID INNER JOIN
dbo.Service ON dbo.ServiceItems.ServiceID = dbo.Service.ServiceID INNER JOIN
dbo.ServiceCategory ON dbo.Service.ServiceCategoryID = dbo.ServiceCategory.ServiceCategoryID
where
(@CategoryID=0 or ServiceCategory.ServiceCategoryID = @CategoryID)
and (@ServiceID=0 or Service.ServiceID = @ServiceID)
and (@LocationName=0 or ServiceItems.ServiceLocation = @LocationName)
and (ServiceItemForward.CreatedDate Between @createdFromDate and @createdToDate)
and(@sender is null or ServiceItemForward.SendersName=@sender)
and (@senderEmail is null or ServiceItemForward.SendersEmaild=@senderEmail)
COMMIT
End
please help me out in htis....
Thanks in advance
modified 13-Jan-12 5:48am.
|
|
|
|
|
You do realise in your null date test you are populating @createdToDate in both tests.
It would help to know what the problem is!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have cooreected that statement and the code was not wrkng.....
|
|
|
|
|
reogeo2008 wrote: I have cooreected that statement and the code was not wrkng....
If you post a problem, then describe what the code is supposed to do and what the error is. All we see is your code and the statement that it ain't workin', but you didn't explain what the desired result would be like or what you're trying to achieve.
Why is there a commit, if you are only selecting records?
Bastard Programmer from Hell 
|
|
|
|
|
Eddy Vluggen wrote: Why is there a commit, if you are only selecting records?
Just to be sure that he is committed to selecting records only. Nothing else! 
|
|
|
|
|
Not sure it belongs in this forum, I hope it does.
I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds.
How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome.
TIA
|
|
|
|
|
 What is the world coming to? Luc asking questions!!!!
I've done similar things in the past, but not using SQL Server.
I don't know what IPC capabilities are in your server or could be easily grafted onto it.
Given these two points, I'd say:
1. Make the server do it. Client to client gets way too messy when you consider clients appearing/disappearing on the fly. At least the server has a reasonably current idea of who's out there.
2. How I did it was to use sockets with the following simple protocol:
client sends requests
at most one outstanding
first request of a session is a logon
server sends responses to requests OR asynchronous notifications
any socket error => forced logoff
(therefore unexpected logon at server => kill old session, start new)
HTH
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Thanks Peter, yes I need to earn some Enquirer points too you know. 
I'm currently considering a UDP multicast, where producing clients just throw their "new critical data" message up in the air, and consuming clients react on it. I was hoping SQL Server itself would have some built-in mechanism, I need to read up on it I guess.
|
|
|
|
|
Sounds like your app is a little less demanding of accuracy. Mine was a multi-terminal raffle selling setup - server in the back room, one or two fixed PCs and some mobiles (J2ME phones with bluetooth-speaking battery-powered printers) wandering around amongst the patrons. Request/response were the obvious ticket sales (or not), logon/off, operator cash management, etc; the notifications were things like raffle closed, new raffle opening (i.e. clients, come and get the details)
I'd be tempted to write it up - there are some interesting design considerations - except that a lot of the code is Hall of Shame material. 
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
I don't mind reading an article with substandard code in it, as long as it says so while also offering valuable insight in concepts and/or design. And if the code really is horrible, we can always turn it into a "guess who wrote this code" competition in the appropriate forum...
|
|
|
|
|
I believe SQL Server has an event notification process that can inform a client of data change. That is as much as I know, thankfully I have never had to implement such a horrible scenario, to date I have always managed to talk the client out of this based on the cost and perceived (by me) fragility of the solution.
I have implemented a polling solution a long time ago (SQL Server 6.5) but was never really happy with the results.
[edit] have a look through Mika's[^] stuff I think he had some info on it at some stage [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Luc Pattyn wrote:
How would you implement this?
Depends on the full specs, sensei. If there were no additional concerns, I'd simply have an update-trigger log the primary key and a datestamp in a table, and have the clients poll the table's count every second. That would also enable you to limit the update-logging to specific fields.
Something like an RSS idea for SQL
Bastard Programmer from Hell
|
|
|
|
|
Thanks Eddy. I was hoping for a solution that doesn't poll, something event driven. Maybe an SQL statement that actually blocks until a specified row/field gets modified or added (Yes I could spend an entire connection!).
|
|
|
|
|
My pleasure
An event upon data change still sounds like a trigger. Is CLR Integration on? You could write a trigger[^] in C#.
..and if you can spend a connection, you probably can spend a thread; monitoring a custom lightweight trace might be an option too.
Bastard Programmer from Hell
|
|
|
|
|
Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A simple scenario would be: client1 is looking at a customers list; client2 (different PC, different user) is looking at the same list; client1 adds/modifies a customer record, client2 should get his display updated, or at least get a warning the data is stale. Of course polling can do that easily, I hope for an event driven solution though.
|
|
|
|
|
Luc Pattyn wrote: Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A trigger, traditionally, executes a piece of SQL when data is inserted/updated/deleted from a table. You can check *which* fields change in the trigger. MSDN has an example on a CLR Trigger[^] that uses pipes to communicate with the outside world. I'm not sure whether you can launch a socket from there, but once you can catch the change, you can inform your clients - one way or the other.
My client would then typically use the INotifyPropertyChanged interface to propagate changes.
Bastard Programmer from Hell
|
|
|
|
|
OK, I'll investigate how that works and fits with what I have already. Thanks again.
![Beer | [beer]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/beer.gif)
|
|
|
|
|
My pleasure, and thanks
|
|
|
|
|
I have to create a job that when a task is due the employee should get reminder notification 14 days before the due date and second notification before 3 days before the due date. how can i do this in SQL Server 2005
|
|
|
|
|
Create a job that checks the data store where the job is, compare the system date with the job due date, get the email address from the employee table and send the email using SQL mail.
The information about jobs can be found using sysobjects and the related sys% tables. You will need to do some research as to where it can be found. Employee information is your problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In table i have column that is type of varchar(n), and it's not primary/foreign key.
Now, what I need is SQL query (stored procedure) that will select all the records in that table like:
(pseudo)
select * from table1 where SUBSTRING(table1.column2,1,2)<@param1 and SUBSTRING(table1.column2,1,2)>@param2
Parameters are first two letters of any record that contains data for that column.
if param1 is aa and param2 is cc then output should be all the records with first two letters like:
aa,ab,ac,...,az,ba,..,bz,ca,cb,cc
Any suggestions would be appreciated!
|
|
|
|
|
First off, I think you have your signs backward.
Other than that, have you tried it?
|
|
|
|
|
Already solved and you're right, in solution that is similar to pseudo code I posted, the signs were backward... but it was just a hint for possible solution that someone could offer. Thanks for your comment anyway, cheers
|
|
|
|
|
select * from table1 where SUBSTRING(table1.column2,1,2)
like '%@param1%'
or SUBSTRING(table1.column2,1,2)>'%@param2%'
use dynamic query
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
