noob sql questions

Message Bookmarked
Bookmark Removed

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

bnw, Friday, 14 August 2009 21:36 (nine years ago) Permalink

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

another thoroughly exhausting autotuned song about The Club (Deric W. Haircare), Friday, 14 August 2009 21:40 (nine years ago) Permalink

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.

bnw, Friday, 14 August 2009 21:47 (nine years ago) Permalink

basically:

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

bnw, Friday, 14 August 2009 22:08 (nine years ago) Permalink

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

( ´_ゝ˙) (Dr. Phil), Saturday, 15 August 2009 19:53 (nine years ago) Permalink

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.

bnw, Friday, 21 August 2009 04:17 (nine years ago) Permalink

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

( ´_ゝ˙) (Dr. Phil), Tuesday, 25 August 2009 20:33 (nine years ago) Permalink

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?

someone who is ranked fairly highly in an army of poo (Colonel Poo), Tuesday, 25 August 2009 20:40 (nine years ago) Permalink

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

someone who is ranked fairly highly in an army of poo (Colonel Poo), Tuesday, 25 August 2009 20:40 (nine years ago) Permalink

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

( ´_ゝ˙) (Dr. Phil), Monday, 31 August 2009 14:16 (nine years ago) Permalink

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 have a set of penises leftover from some bach party somewhere (HI DERE), Monday, 31 August 2009 14:20 (nine years ago) Permalink

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.

someone who is ranked fairly highly in an army of poo (Colonel Poo), Monday, 31 August 2009 15:46 (nine years ago) Permalink

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

haha no i just liked the way it sounded

mince lice (electricsound), Tuesday, 1 September 2009 06:07 (nine years ago) Permalink

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?

someone who is ranked fairly highly in an army of poo (Colonel Poo), Tuesday, 1 September 2009 14:20 (nine years ago) Permalink

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

a fact-checker with The New Yorker magazine (HI DERE), Tuesday, 1 September 2009 14:23 (nine years ago) Permalink

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

someone who is ranked fairly highly in an army of poo (Colonel Poo), Tuesday, 1 September 2009 14:25 (nine years ago) Permalink

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.

a fact-checker with The New Yorker magazine (HI DERE), Tuesday, 1 September 2009 14:38 (nine years ago) Permalink

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

am0n, Tuesday, 1 September 2009 14:42 (nine years ago) Permalink

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

someone who is ranked fairly highly in an army of poo (Colonel Poo), Tuesday, 1 September 2009 14:44 (nine years ago) Permalink

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

bnw, Wednesday, 9 September 2009 16:18 (nine years ago) Permalink

Dude seriously

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

Colonel Poo, Wednesday, 9 September 2009 20:27 (nine years ago) Permalink

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.

bnw, Wednesday, 9 September 2009 21:10 (nine years ago) Permalink


You must be logged in to post. Please either login here, or if you are not registered, you may register here.