|
|
HI,
I WANT TO GET A COUNT OF ALL THE VALUES FOR A COLUMN AS NULL IN A STORED PROCEDURE.
CAN U HELP PLZ.
|
|
|
|
|
Well replies not reqd.
I've got the query:
pasting it here incase helps someone in future:
<pre>
BEGIN
OPEN SYMMETRIC KEY PasswordFieldSymmetricKey
DECRYPTION BY CERTIFICATE PasswordFieldCertificate;
SELECT COUNT(ApplicantID) FROM APPLICANT
WHERE IsNull (CONVERT(DATETIME, DecryptByKey(DOB), GETDATE()),'01/01/01')= '01/01/01'
END
</pre>
|
|
|
|
|
Whats wrong with just saying where dob is null ? (allowing for your decryption)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I have two tables. both tables have a common column (named=Rank)
I have made table1 column(Rank) as primary key
I have made table2 column(Rank) as foreign key
every thing is working properly
problem is when i enter some invalid data in table2(into Rank) which is not there in table1
I want to generate a pop up
is this possible
thanks in advance
|
|
|
|
|
U can enter only enter those values in table2(Rank) which are present in Primary Key.
This is the fundamental of having a foreign key. If you want to add values other than those in primary key remove the constraint.
|
|
|
|
|
thats wat i am saying
when i enter other values i want to generate an popup message showing error
|
|
|
|
|
If itz in a form, u can trap the error number and popup a window with the message.
Use "Try Catch"
and popup window in Catch
|
|
|
|
|
kiranreddy wrote: i want to generate an popup message showing error
You need to do that in your application, there is no such thing in sql.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
|
ID Name Access1 Access2 Access3 Access4 Access5
918a6fb8-573f-4744-b4de-019bc8322ce4 Gast False False False False True
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc Administrator True True True True False
I have multiple results for 1 user. In the example I want the results to get to one row, with the final result (True, True, True, True, True).
So the query has to go through all the results and check if the Access Right is True or False. Is one of them is True the result of the column is true.
I tried to use Temp Tables but im stuck Anyone has an idea?
I use MSSQL
|
|
|
|
|
That doesn't look the way I think it should. I'd rather have a table with more rows and fewer columns, and because of normalization I'd suggest two tables, one with names, one with access rights:
ID Name
918a6fb8-573f-4744-b4de-019bc8322ce4 Gast
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc Administrator
ID AccessNumber AccessRight
918a6fb8-573f-4744-b4de-019bc8322ce4 1 False
918a6fb8-573f-4744-b4de-019bc8322ce4 2 False
918a6fb8-573f-4744-b4de-019bc8322ce4 3 False
918a6fb8-573f-4744-b4de-019bc8322ce4 4 False
918a6fb8-573f-4744-b4de-019bc8322ce4 5 True
etc.
013b4a2b-3027-41b6-a7e1-e0e536dd0bbc 4 False
which would be much easier to query (you can join on ID; you can select on right=true, etc), as well as easier to extend when new access numbers become necessary.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
The MAX operator won't work on bits, but you can cast them to integers;
SELECT MAX(CAST(Access1 AS INT))
,MAX(CAST(Access2 AS INT))
,MAX(CAST(Access3 AS INT))
,MAX(CAST(Access4 AS INT))
,MAX(CAST(Access5 AS INT))
FROM dbo.TestTable You'd have to specify a decent WHERE -clause, though.
Another option would be the use of a function, that takes the ID as a parameter and which returns those five fields. It could then be used in a view
-- Edit --
..you should really consider Luc's post carefully, a simpeler design makes a lifetime of simple maintenance
"Every normal man must be tempted at times to spit on his hands, hoist the black flag, and begin to slit throats."
-- H.L. Mencken, US editor (1880 - 1956)
|
|
|
|
|
Yes i will consider that Havent thought about it that way.
|
|
|
|
|
Hi All,
Please tell me how to do the following:
XML save and load of data to SQL Server 2005 database?
Thanking you in advance,
Regards,
Ashwath Hegde.
|
|
|
|
|
Look at the MSDN documentation for either DataSet or DataTable . Both of them have ReadXml and WriteXml methods.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
You can have your column type as XML and SqlDbType[^] enum supports XML type. You can write something like,
using (SqlConnection conn = )
using (SqlCommand cmd = new SqlCommand("insert into your_table VALUES(@xml_content)", conn))
{
cmd.Parameters.Add("@xml_content", SqlDbType.Xml).Value = new SqlXml(reader);
} SqlXml can take a Stream or a XmlReader instance.
For reading data, work with the SqlDataReader.GetSqlXml()[^] method. SqlXml.ReadOuterXml() will give you a string representation of the XML.
|
|
|
|
|
Can you please give the complete code snippet so that i can understand fully.
|
|
|
|
|
ashwath1979 wrote: Can you please give the complete code snippet so that i can understand fully.
That is laziness. What have you tried and where you are stuck?
|
|
|
|
|
Here's a strange code which I found while maintaining
Convert(varchar, Select Convert(varchar,getdate(),106),106)
Do anyone have idea why this is written like this?
Actually it is used in a stor_proc where this phrase is used for comparing two dates.
Do you think is it going to make any difference when we don't convert it again?
Believe Yourself™ ™
modified on Monday, August 31, 2009 12:40 PM
|
|
|
|
|
Wrong forum; try "general database".
|
|
|
|
|
Hi all,
Is it possible to move SQL Express databases between users and computers?
TIA.
I hope you realise that hamsters are very creative when it comes to revenge. - Elaine
|
|
|
|
|
Yes.
At least, between computers is fairly easy (either copy or move), never tried between users but I would assume that it would also be relatively easy.
If you have SQL Server Management Studio, use the Search option from the Help Menu and search for move database.
If not, Google for sqlserver move database.
Either way you will get loads of hits, as always, some more useful than others.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Thanks Henry, I wasn't sure if the restrictions in the Express version meant that the databases were limited to one user or computer.
I hope you realise that hamsters are very creative when it comes to revenge. - Elaine
|
|
|
|
|
Coincidentally I have just found this[^] article on Database Journal about attaching Databases. Just in case you aren't aware of the method.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|