|
Hi
I guess you could try something like this.
CREATE PROCEDURE client_stuff
@variable1 int = NULL,
@variable2 varchar(10) = NULL,
@variable3 double = NULL
AS
SELECT * FROM mytable t1
WHERE client_id = ISNULL(@variable1,t1.client_id)
AND client_name = ISNULL(@variable2, t1.client_name)
AND client_balance = ISNULL(@variable3, t1.client_balance)
... etc
I hope that is the right syntax for your DB. Basically you pass in NULLABLE parameters which may or may not be specified, then in the select use ISNULL to replace any NULL values with the value in the column, so if NULL then give me everything in this column, otherwise give me only records whose value in the column match the parameter.
I hope that helps,
Cheers,
Kevin
|
|
|
|
|
hi all
i want to manipulate the receive the table as a parameter in SQL procedure and then want to iterate it to get values and use them in select caluse
i havent done it before if any one can help me out
.............
|
|
|
|
|
You are looking at using dynamic sql, but without further information I cannot point you to any further help.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have hug number of records and the query take many time to execute how can i solve this problem
thank you
|
|
|
|
|
mahmoudinirat wrote: i have hug number of records and the query take many time to execute how can i solve this problem
Look up how to create and use indexes.
Without looking at the structure of your tables and your query that seems to run slow, I don't think we can help a lot.
|
|
|
|
|
Well, you need to look at optimising your queries and indices. With no other information nobody can help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i wonder how you managed to achieve that!!!
i couldn´t to that if i wanted to!!!
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi ,
i want to know how can i get memory occupied by table of a database through query. if some one can provide me a google link where i can find it or the search string to be used for googling it out. it would be of great help.
Also assume memory occupied by the table when no rows present is M , and when row is present the memory occupied is M*1, etc
|
|
|
|
|
Use system database sp_spaceused. Executing it without supplying parameters will return database size and with a table name gives it's size.
|
|
|
|
|
Hi,
I have 2 db's for my application one as primary and another as backup.
At any time both will be having same data.
If any DB goes down i will sync it after the DB comes alive. If an entry is present in both the DB's , I now use last updated time to find the latest affected record , but it is causing some problems.
Is there any other way to keep track of the correct record if the entry is present in both the DB's
Thanks in Advance
krishnan.S
|
|
|
|
|
It depend on how you are keeping them in sync in the first place. If you are using replication then it should be straight forward as this is one of its features.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Actually Iam not using replication , Iam mainting a seperate table for the records to be synced.
Is there any way to identify the latest record without using time as key.
krishnan.S 
|
|
|
|
|
Probably not then.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I take it that the problem is because of the resolution of time in sql server (meaning you can have the same time on multiple rows).
You could use constantly growing surrogate key for each table but not as a key but as a change identification. This would give you unique, growing values for each row so you could easily identify differences between tables based on this column.
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
why 2 databases? use backups trigger them often.
if it goes down i would wait after everthing ok and put online again, and use backup to restore data.
if a create a 2nd database i would use it just when 1st goes to "vacations"
and use it like temp store until 1st returns
from that create your own strategies
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Could you pls tell me how you are created backup database. where i am using only single database to keep my 10000 record of info. if something goes wrong what to do...
************ S G KORE *******************
|
|
|
|
|
Hi,
I have 2 identical tables in 2 db's . Is there any easier way to compare the tables and give us the result?
Thanks
krishnan.s
|
|
|
|
|
If you want to find identical rows or differenting rows, have a look at EXCEPT and INTERSECT operands
Mika
The need to optimize rises from a bad design
|
|
|
|
|
i created a application that connects to sql server database.
i show data in a datagridview
i show info msg in a label that belongs to a statusstrip(about changes in datagridview, meaning database)
So, datagridview i fill with tables data and label i fill with c# validations ansers about what happened(OK: client added;ERROR: can´t delete product).
it works but,
i rather fill both (datagridview and label) with data from sql procedures.
any suggestions? maybe write a trigger, add a new table? i really don´t imagine a algorithm for that. 
(no need to post code)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
We've been storing credit card expiration dates in varchar format as MMYY.
Well, before we would just create a new record and keep the old credit card number instead of deleting it (because in the same table we hold the address and it was a means of keeping customers previous addresses on file), but since we're switching to a new system we need to get rid of the old expired card numbers so we don't get charged for authorizations on cards that we KNOW are expired.
Lastly, we need this to work on a continual basis so when we upload an updated csv file to our processor, we *must* give them the date in the format of MMYY, any updates to what we're doing now need to factor that in... so when we pull that file, we're exporting directly from Enterprise Manager into a text file(csv) ready to import into their system.
So, my question is... how can we...
A. Leave the dates in varchar format and find out what cards expired and delete those records?
OR
B. Convert the varchar dates into datetime dates and delete the old card records without converting to a full date?
P.S. I'd like to add that some dates in these text fields aren't 4 digits. For example: 09/10 is in the database as 910.
* * * * Structure * * * *
==CardNum=|=CardExp==|====BillingAddress====
====int===|varchar(8)|====varchar (40)====
4444****** | 06/09 |123 Bobbaganoosh Lane
Any help would be appreciated!
"The whole world steps aside, for the man who knows where he is going. -bd"
|
|
|
|
|
Well, one way would be
declare @now4 varchar(4), @now3 varchar(4)
select @now3 = convert(varchar,datepart(month,dateadd(month,-1,getdate()))) + right(convert(varchar,datepart(year,getdate())),2)
select @now4 = right('0' + @now3,4)
you can now compare to @now3 (such as 808) or @now4 such as (0808)
Hopefully this gives you a starting point.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
that 910 thing is bad
use datediff(),dateadd(),getdate() functions will help a lot.
i builted a movie rental application, some rents were up to 3 days, i had a report procedure when triggered counts day between rent day and delivery day or current day.
As you can guess i got a varchar that could be for exmple:
-1) meening i expect movie to be delivered tomorrow
0) meening i expect movie to be delivered today
+3) meening movie 3 days delay
in store) if movie is back to base.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hello,
I have made a database in SQL 7 and copyied the Date.mdf and log.ldf files from the SQL\Data Directory and copied them to another system but I cannot oipen the database .
How should I open and copy Databases?
Are there any version problems?
Priya
|
|
|
|
|
The best way to do this is to perform a full backup on one database, then restore it to the other computer. Backup and restore will guarantee that the data is consistent. Opening and copying database files from the operating system could potentially cause data corruption by locking the datafile outside the scope of the database engine.
|
|
|
|
|
If one of the servers is SQL 2005 you can use Copy DataBase Wizard
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
