A little help on a proc

I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.comDavid
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message news:eShj
WtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||This is great, but how do I now access that recordset if it exists?
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23hYr9xRDFHA.1524@.TK2
MSFTNGP09.phx.gbl...
David
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message news:eShj
WtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a r
ecord set, look at me go. However, I need to get a little more advanced. I n
eed to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue throu
gh proc and return the necessary data, if not return an invalid status.
Thanks a million!!
--
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||This should get you started:
create proc foo as
@.loginname varchar(20),
@.password varchar(50)
as
--verify that the login is valid
if not exists (select * from sometable where somelogincolumn = @.loginname)
BEGIN
RETURN (1)
END
--verify that the password is valid
if not exists (select * from sometable where somelogincolumn = @.loginname
AND somepasswordcolumn = @.password)
BEGIN
RETURN (2)
END
select somecolumn
from sometable
where somelogincolumn = @.loginnamae
and somepasswordcolumn = @.password
RETURN (0)
GO
The return code will be 1 if the login does not exist, 2 if the password is
not correct. If the login exists and the password is correct the third
statement will be executed and the return code will be set to 0.
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return a
record set, look at me go. However, I need to get a little more advanced. I
need to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue
through proc and return the necessary data, if not return an invalid status.
Thanks a million!!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||So the best process is to rerun the query? Wouldnt that get extensive for
the server? Can I run something like this:
create proc users
@.Fullname VARCHAR Output, @.Security Integer OUTPUT, etc.
Run query that verifies username
if username valid
check password against previously returned recordset
if password valid
@.fullname = returned data, @.security, etc.
else
return 2
else
return 1
end
By the way, i'm accessing this from an ASP.NET page.
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMvhnASDFHA.2632@.TK2MSFTNGP12.phx.gbl...
> This should get you started:
> create proc foo as
> @.loginname varchar(20),
> @.password varchar(50)
> as
> --verify that the login is valid
> if not exists (select * from sometable where somelogincolumn = @.loginname)
> BEGIN
> RETURN (1)
> END
> --verify that the password is valid
> if not exists (select * from sometable where somelogincolumn = @.loginname
> AND somepasswordcolumn = @.password)
> BEGIN
> RETURN (2)
> END
> select somecolumn
> from sometable
> where somelogincolumn = @.loginnamae
> and somepasswordcolumn = @.password
> RETURN (0)
> GO
> The return code will be 1 if the login does not exist, 2 if the password
> is
> not correct. If the login exists and the password is correct the third
> statement will be executed and the return code will be set to 0.
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
> I'm fairly new to the stored procedure world, I can create one to return a
> record set, look at me go. However, I need to get a little more advanced.
> I
> need to do the following in a proc:
> Query a honken huge query and check if login is valid. If so, continue
> through proc and return the necessary data, if not return an invalid
> status.
> Thanks a million!!
>
> --
> David Lozzi
> Web Applications/Network Specialist
> Delphi Technology Solutions, Inc.
> dlozzi(remove-this)@.delphi-ts.com
>|||You would change the DO SOMETHING into a proper select statement that
returns the data that you are interested in.
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:ubZno6RDFHA.936@.TK2MSFTNGP12.phx.gbl...
This is great, but how do I now access that recordset if it exists?
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hYr9xRDFHA.1524@.TK2MSFTNGP09.phx.gbl...
David
(untested)
CREATE PROC spSomething
@.login VARCHAR(20)
AS
IF EXISTS(SELECT name FROM master..syslogins WHERE name =@.login )
BEGIN
DO something
ELSE
PRINT 'A login does not exist'
END
GO
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:eShjWtRDFHA.512@.TK2MSFTNGP15.phx.gbl...
I'm fairly new to the stored procedure world, I can create one to return
a record set, look at me go. However, I need to get a little more advanced.
I need to do the following in a proc:
Query a honken huge query and check if login is valid. If so, continue
through proc and return the necessary data, if not return an invalid status.
Thanks a million!!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com|||You can do anything you want. Do you care if the loginname is invalid? Do
you care if the password is invalid? Or do you just want a "Yes,
authentication passed -- here is your data" or an "Authentication Failed"
notification?
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:e8kNiGSDFHA.560@.TK2MSFTNGP15.phx.gbl...
> So the best process is to rerun the query? Wouldnt that get extensive for
> the server? Can I run something like this:
> create proc users
> @.Fullname VARCHAR Output, @.Security Integer OUTPUT, etc.
> Run query that verifies username
> if username valid
> check password against previously returned recordset
> if password valid
> @.fullname = returned data, @.security, etc.
> else
> return 2
> else
> return 1
> end
>
> By the way, i'm accessing this from an ASP.NET page.
> --
> David Lozzi
> Web Applications/Network Specialist
> Delphi Technology Solutions, Inc.
> dlozzi(remove-this)@.delphi-ts.com
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eMvhnASDFHA.2632@.TK2MSFTNGP12.phx.gbl...
@.loginname)
@.loginname
a
advanced.
>|||I guess, I'm confuzzled about how to access the initial query throughout the
rest of the proc?
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O3oE6RSDFHA.3452@.TK2MSFTNGP09.phx.gbl...
> You can do anything you want. Do you care if the loginname is invalid?
> Do
> you care if the password is invalid? Or do you just want a "Yes,
> authentication passed -- here is your data" or an "Authentication Failed"
> notification?
>
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:e8kNiGSDFHA.560@.TK2MSFTNGP15.phx.gbl...
> @.loginname)
> @.loginname
> a
> advanced.
>|||I am why you think you need to access the initial query throughout
the rest of the stored procedure.
It would be helpful if you told us what the requirements are for the stored
procedure. What are the inputs (parameters)? What are the expected results
(recordset/output variables/return code)?
Keith
"David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
news:%23UjJYVSDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> I guess, I'm confuzzled about how to access the initial query throughout
the
> rest of the proc?|||Yes, that would me sense, I reposted at 11:12.
thanks!
David Lozzi
Web Applications/Network Specialist
Delphi Technology Solutions, Inc.
dlozzi(remove-this)@.delphi-ts.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:e6HoWBTDFHA.392@.TK2MSFTNGP14.phx.gbl...
>I am why you think you need to access the initial query throughout
> the rest of the stored procedure.
> It would be helpful if you told us what the requirements are for the
> stored
> procedure. What are the inputs (parameters)? What are the expected
> results
> (recordset/output variables/return code)?
> --
> Keith
>
> "David Lozzi" <dlozzi@.(remove-this)delphi-ts.com> wrote in message
> news:%23UjJYVSDFHA.3120@.TK2MSFTNGP12.phx.gbl...
> the
>