noob sql questions

I may start posting stupid sql questions here in hopes someone knows something. Good plan?

Sure. I'm an advanced noob, so I may be of some assistance.

I have a simple query that updates one column (login time) of a single table (employees) based on another column (emp password). I want emp password to work as a variable outside the query if that makes sense.

The goal is to have a batch or vb script that prompts for the emp password and then runs the query with that input.

basically:

DECLARE @LoginPassword varchar(10) -- employee password.
set @LoginPassword = <INPUTVARIABLE>
Update tblEmployee
set LoginDateTime = null
where LoginPassword = @LoginPassword

are you asking about the syntax? also why is logindatetime getting set to null

oh .... I've kinda gathered its going to require some sort of programming to deliver the variable to the database like vbscript. been having problems getting sql to accept an ALTER command that way though.

The database program the company uses sets your login time to null when you logout. If its not at 'null' it assumes you are logged in and won't let you back. I'm trying to set it up so employees can log themselves out via their password.

i follow what you're trying to do but have no suggestions as to why its not working

Do you just want a stored procedure?

i.e.

create procedure ResetLoginDate @employeePassword varchar(10)

as

Update tblEmployee
set LoginDateTime = null
where LoginPassword = @LoginPassword

?

Also what happens if 2 employees by coincidence have the same password?

sorry that should say @loginPassword not @employeePassword but you get the idea

lol @ new board description. was it that dumb of a question

i dunno i thought sql had a problem with accepting null for datetime
http://www.c-sharpcorner.com/UploadFile/sd_patel/EnterNullValuesForDateTime11222005015742AM/EnterNullValuesForDateTime.aspx
http://geekswithblogs.net/TimH/archive/2006/11/08/96546.aspx

Also what happens if 2 employees by coincidence have the same password?

This is kind of critical; you need to use the employee's unique login ID if you want to make sure that one person isn't logging off multiple olther employees because they coincidentally have the same password.

i dunno i thought sql had a problem with accepting null for datetime
Looking at those links it looks like some .NET bug, there is no problem setting datetime to null in T-SQL that I've ever come across.

lol @ new board description. was it that dumb of a question

haha no i just liked the way it sounded

While we're at it, I have an SQL question, specifically SQL Server:

Is there any underneath-the-surface difference in what SQL Server actually does between the following 2 statements:

-- 1)

update p
set x = d.x
from p
inner join d
on p.id = d.id

-- 2)

update p
set x = d.x
from d
where p.id = d.id

??

I reckon functionally it's the same thing, but we have a consultant who insists the 2nd form is more efficient, that SQL Server does something different behind the scenes. Trouble is our not-really-enforced SQL coding standard requires we always use the 1st example (and I think that's the ANSI way anyway).

Any expert users on ILX who can shed light on this?

My understanding is that they generate the same query plan; however, I'm no SQL Server guru.

That is my understanding also. This guy generally knows his stuff, but I think he's wrong on this one.

http://lists.evolt.org/archive/Week-of-Mon-20050829/175368.html

At least in SQL 2000, they were supposed to resolve to the same query plan.

as an intermediate noob i would only use 1. the "more efficient" thing sounds bogus

We're on SQL Server 2005 but I can't imagine it's any different there.

I got my thing to work ^________^

batch file

echo off
Set objShell = CreateObject("Wscript.Shell")
Echo Please enter password and press return
Set /p var1=
osql -d AFW3 -U sa -P password -S TESTSERVER -q "Exec EmployeePasswordLogout @LoginPassword=%var1%"

sql stored proc

CREATE PROC EmployeePasswordLogout (@LoginPassword varchar (10))
As
Update tblEmployee
set LoginDateTime = null
where @LoginPassword = LoginPassword
GO

Dude seriously

Also what happens if 2 employees by coincidence have the same password?

Being logged out while you are still using the database is only a problem if you were to try to enter it again which is pretty rare. But it probably isn't too hard to get username in there too.