Choosing Data Type

Hi ... I have question on datatype on SQL Server 2005 EE

What is a good data type for email, password, Phone Number and ISBN number?

Thanks!I'd use varchar for all of those.|||Thanks for the reply. How about phone number? isn't that suppose to beNumeric? Or Numeric just for something that is calculateable?|||

iloveny:

Thanks for the reply. How about phone number? isn't that suppose to beNumeric? Or Numeric just for something that is calculateable?

You've hit the nail on the head. Only use numeric data types for something that is calculateable. Phone numbers, ZIP codes, etc. should definitely be one of the string data types (I suggest varchar).

Check Table Values in the Store Procedure...

HI

I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@.xyz.com)) , and this is working correctly.

Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@.xyz.com))\

how i can do that in the store procedure.

Comments will be appreciated.

Use the IsNull function.

|||

You could save the results to a temp table in the stored procedure then do an update replacing all nulls with "" then just return the contents of the temp table. e.g

CREATE TABLE #tmpTable
(
field1as NVARCHAR(200),
field2as integer
)

INSERT INTO #tmpTable (field1,Field2)
SELECT * FROM SelectionTable

UPDATE #tmpTable SET field1 ="" WHERE field1 isnull

SELECT * FROM #TmpTable

DROP #tmpTable

|||

You can use the Isnull function directly in your select query like this:

select firstName , lastName ,IsNull ( email ,'' )as emailfrom <table Name>

This way you are rest assured that for whichever row the email is null, it will automatically be converted to '' ( blank string ). You can write anything like 'Not Available' in the replacement part of the isnull function.

Hope this will help.

Check sql table input at certian times and send email

I have a .net web application and need to create a program that needs to check the database every two hours and if data has not been input send the user an email message. I have heard this can be done via SQL Server - events or triggers? Can anyone tell me where I can find example code or more information on this. Thank you in advance.

Hi,

Yes since your whole work is within the database then I think setting up a "SQL job" will be the best possible solution also you can schedule it to run on certain times. Basically you need to create a "stored procedure" which will check the table and will be sending mail and you can call this stored procedure from your job. Mails can be sent by using "SQL Mail" or using .NET framework (if you are using Sql Server 2005).

I have marked the importent terms in the mail. You can get best materials on them in Books Online in Sql Server.

Hope it helps!

Bhaskar!

|||

In a well trafficed website, I usually drop that in the login.aspx page, or if I have enabled cookies, then another often hit page.

You can also do this using sql agent. Write the stored procedure to scan the table, and generate the emails. Then tell sql agent to execute that stored procedure every two hours.