|
Hi,
I would like to implement Stored procedure naming conventions on my databases?
I don't want to mess up with developers for this. Is there any way with which my server automatically check
whether the request coming for creation of stored procedure following naming conventions or not??
and if not then it pass error message to developer.
Regards,
Aman
|
|
|
|
|
Try this
create a database[schema] level trigger for CREATE in your database.
go through this for more info.....
Data Base Level Triggers[^]
Cheers..
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
Does SQL Server support database level triggers?
Just realised that the OP doesn't say he is using SQL Server, but I guess the point is, not all databases support database or schema-level triggers.
|
|
|
|
|
David Skelly wrote: Does SQL Server support database level triggers?
Yes - Sqlserver supports the Schema Level Triggers.
Sql Server Schema level triggers syntex/examples[^]
David Skelly wrote: Just realised that the OP doesn't say he is using SQL Server, but I guess the point is, not all databases support database or schema-level triggers.
your right.., but popular database like Oracle, Sqlserver , Mysql .. etc supports Schema Level Triggers . and if you see the OP requirement is such complex/hard to do in the small databases.
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
First, have a policy; make sure everyone has a copy and has read it. They don't need to agree with it, only agree to comply with it.
Second, be sure your policy states that stored procedure names do not begin with sp_.
Third, try the honor system, and see if you need to take further steps.
Fourth, fire any offenders.
Fifth, do as Rajesh said.
Sixth, abandon using stored procedures, they are nothing but trouble.
|
|
|
|
|
PIEBALDconsult wrote: Sixth, abandon using stored procedures, they are nothing but trouble.
That may be your opinion, but many large companies (certainly most financial institutions) insist on them for ease of deployment - if your sql is in your code (dll or whatever) and you have thousands of users worldwide, changes are far more complex than a single stored proc change (even if it does get replicated).
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi All,
I have developed an accounting application in vb6 with mssql 7 as backend.
with the passage of time microsoft developed a lot and recently launched VS2010 and MSsql 2010.
Being a developer i updated myself up to VS2008 and Mssql 2008. Currently i am working in Vs2008 and Mssql 2008 express. Upto Mssql 2005 there was no problem between me and my clients. but now as i have upgraded my all systems to mssql express 2008 i am facing problems as some of my client have xp sp2 and mssql 2008 studio does not work with xp sp2.
whenever i tried to install to mssql 2008 studio on xp sp2 it required win powershell which only support on xp sp3. as result i am unable to resotre database to mssql 2005 developed in mssql 2008 express.
kindly suggest me the solution.
Thanks
rmshah
Developer
|
|
|
|
|
Script out your database and recreate it in SQL 2005 by running the script. Then import the data.
|
|
|
|
|
hi iam using sql server 2000
in my table orders the fields are
companyid int
voucherdate smalldate
description nvarchar(50)
the problem is when i say select * from orders ; it is displaying orders like this
1 1/21/2010 1234orderscomleted
2 2/2/2010 67012requireurgent
but i want to display in this format the number with paranthesis and the text
1 1/21/2010 (1234)orderscomleted
2 2/2/2010 (67012)requireurgent
can you give example which helps me
|
|
|
|
|
You can do that in programming language like c# or vb.net but
I think it is not possible using SQL.
Jinal Desai
|
|
|
|
|
By the look of your table design, the numbers and text are simply joined in the field "description". This means it will display exactly as the user has input it!! The easiest way would be for your users to input the data in the format you are after, or change your table design so that the number is stored in its own field, then you could simply select the number and append it into some parentheses!
eg: select companyid, voucherdate, '(' + ordernumber + ')' as FormattedNumber, description from orders
|
|
|
|
|
If the data are showing; comes from single entry then i would like to suggest you to re-design you database table.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
It's not the best database design in the world, so I agree with the others, your first approach should be to change the table design if you can.
However, sometimes you don't have that option and the database design is out of your hands, so this link may help:
http://www.kf7.co.uk/sql-server-function-get-numeric.aspx[^]
You should be able to start from that and get something that works for you. It's not going to be fast, though.
|
|
|
|
|
Hello,
I have a table matter (id_mat, libelle_mat, id_module)
to have names of matter on the header I used pivot table
select [411] AS '411 ', [412] AS '412', [413] AS '413',....
or 411 ,412.... are id_mat
how can I do if I dont know the number of matter? I wanted to display in the header every libelle_mat of a module. (I work with sqlserver2008)
Thank you.
|
|
|
|
|
|
Hi,
I think I did not explain my problem,how to display matter of a module in header if I dont know know the number of matter because the user add and delete it.
Thanks.
|
|
|
|
|
That is precisely what I do in the article, buid a string of column headers from the data and build a pivot query based on that. Read the article!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a table which has a date column "OrderedDate" which can be null in db.
Secondly i have a UI which has an frmdate and todate. While querying if the two textboxes are empty i need to show all the records( including the rows with null date). I have written the following query but it fetches the ros with non null values only.
AND
(
isnull(OrderedDate,@frmdate) between isnull(@frmdate,OrderedDate) and isnull(@todate,OrderedDate)
)
I can`t figure out whats the problem. Kindly look into the matter if possible. Thank You.
When you fail to plan, you are planning to fail.
|
|
|
|
|
You can construct separate queries for cases when the text boxes are empty.
|
|
|
|
|
Your logic seems flawed; I would try
AND
(
@frmdate IS NULL OR @todate IS NULL OR OrderedDate BETWEEN @frmdate AND @todate
)
warning: BETWEEN is inclusive or exclusive, depending on the actual database; when uncertain use explicit comparison with >= and <=
|
|
|
|
|
While I already answered your actual question, what I would really do myself is:
AND
(
@frmdate IS NULL OR OrderedDate >= @frmdate
}
AND
(
@todate IS NULL OR OrderedDate <= @todate
)
That allows you to leave either one empty, so you can get:
- all
- all up to some date
- all since some date
- all in an interval
|
|
|
|
|
There are two things you can do in this case.
1. Either you can check the null value of @frmdate and @todate at programming level and then
fire queries accordingly.
2. You can write the same condition in Stored procedure and fire query conditionally.
But, by using single query it is not possible.
Because when the @frmdate and @todate is null it will always consider those
fields with value and ignore rows having null in OrderDate column, there are
reason for such behavior.
1. Either between is evaluating while there is value in OrderDate column and
ignoring row while it is null.
Hope I will clear your problem.
Jinal Desai
|
|
|
|
|
I would take another approach. The Business Layer should not allow the UI layer to pass null values in; it should require proper DateTime values.
The UI layer should then pass in something like System.Data.SqlTypes.SqlDateTime.MinValue or System.Data.SqlTypes.SqlDateTime.MaxValue as appropriate.
You <i>are</i> parsing the TextBox values to DateTimes <i>before</i> passing them to the Business Layer <i>aren't</i> you?
In fact, why aren't you using DateTimePickers? 
|
|
|
|
|
PIEBALDconsult wrote: In fact, why aren't you using DateTimePickers?
Actually i am. I have a layered architecture and pass the datetime values as Nullable<datetime> which allows me to send a null datetime to my SP.
Anyways, Luc`s answer fits correctly for me.
AND
(
@frmdate IS NULL OR OrderedDate >= @frmdate
}
AND
(
@todate IS NULL OR OrderedDate <= @todate
)
Thanks again.
When you fail to plan, you are planning to fail.
|
|
|
|
|
Following is conditional code that will work for your situation.
declare @date1 as DateTime;
declare @date2 as DateTime;
If both the dates are specified the query goes as follow
set @date1='1970-04-04 00:00:00.000';
set @date2='1978-07-07 00:00:00.000';
if @date1 is null or @date2 is null
begin
select * from tblemployee
end
else
begin
select * from tblemployee WHERE
birthdate between @date1 and @date2
end
If any of the field or both of the field is null then the query goes as follow
set @date1=NULL;
set @date2='1978-07-07 00:00:00.000';
if @date1 is null or @date2 is null
begin
select * from tblemployee
end
else
begin
select * from tblemployee WHERE
birthdate between @date1 and @date2
end
Jinal Desai
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
