SQL Server Stored Procedures

[vbsquare]

Does anyone know how to write a SQL Server stored procedure that will check if an item is in a database? Here is what I have at the moment, but it doesn't seem to be working:

CREATE Procedure sp_mdLoginUser
@Username varChar(50),
@PassToken varChar(50)
As
DECLARE @UserID int
SELECT
Username,
PassToken
FROM
Users
WHERE
Username = @Username
If @@error = 0
BEGIN
SELECT @UserID = @@IDENTITY
return @UserID
END
ELSE
BEGIN
return(0)
END

Thanks

[Mongo]

I'm not clear what you're asking, but this is a Sybase quickie I use a lot. Remember to be cautious about using a system stored procedure prefix in SQL Server. This or looking at the helptext for sp_who may give you a few ideas. Hope this helps.

create proc usp_use (@suid int = 0)
as
IF @suid = 0
BEGIN
SELECT master..sysprocesses.spid
, master..syslogins.suid
, master..syslogins.fullname
, master..sysprocesses.status
, master..sysprocesses.hostname
, master..sysprocesses.cmd
, master..sysprocesses.cpu
, master..sysprocesses.physical_io
, master..sysprocesses.blocked
, master..sysprocesses.program_name
, master..syslogins.dbname
FROM master..sysprocesses
, master..syslogins
WHERE master..sysprocesses.suid = master..syslogins.suid
COMMIT
END
ELSE
BEGIN
SELECT master..sysprocesses.spid
, master..syslogins.suid
, master..syslogins.fullname
, master..sysprocesses.status
, master..sysprocesses.hostname
, master..sysprocesses.cmd
, master..sysprocesses.cpu
, master..sysprocesses.physical_io
, master..sysprocesses.blocked
, master..sysprocesses.program_name
, master..syslogins.dbname
FROM master..sysprocesses
, master..syslogins
WHERE master..sysprocesses.suid = master..syslogins.suid
AND master..syslogins.suid = @suid
COMMIT
END

[lenin]

What happens when you execute the current SP? he SQL itself looks fine.

[Mongo]

When I run "usp_use 115" it returns the following which gives me an idea how I'm doing. My suid is 115.

spid suid fullname status hostname cmd cpu physical_io blocked program_name dbname
----- ----- ------------------------------ ------------ ---------- ---------------- ---------- ----------- ------- ---------------- ------------------------------
78 115 my Name running SELECT 258 0 0 db_name
87 115 my Name sleeping COMMIT TRANSACTI 101 1 0 db_name


Oops, I'm a dope. Lenin, I misread your question, thinking you were the originator, vbsquare. Nevermind. *blush*

[Edited by Mongo on 06-14-2000 at 01:45 PM]

[vbsquare]

If you're refering to the SQL Stored Proc that I posted, then it simply returns a value of 0 each time even when correct details are passed.

Any ideas?

[lenin]

Why not use a simple if count(*)?

something like:

if ( select count(*) from query_status where Username = @Username ) >= 1
begin
SELECT @UserID = @@IDENTITY
return @UserID
end
else
return(0)

[vbsquare]

This is what i've got and it still isn't working:

CREATE Procedure sp_mdLoginUser
@Username char(50)
As
DECLARE @UserID int
If (select count(*) from Users Where Username = @Username) >= 1
begin
SELECT @UserID = @@Identity
return @UserID
end
else
return(0)

[Clunietp]

from SQL 7 books online


After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.


You are not doing any of the above, so you get NULL (but SQL really returns 0 instead)

You can do something like this:

CREATE PROCEDURE spu_test
@ID int
AS

declare @EmpID int
Select @EmpID = EmployeeID from Employees where EmployeeID = @ID

return @EmpID


or you can use an output parameter


CREATE PROCEDURE spu_test
@ID int,
@retIDVal int output
AS

Select @retIDVal = EmployeeID from Employees where EmployeeID = @ID


and to get the output parameter:

declare @retID int

execute spu_test 2, @retIDVal = @retID output

print "Return ID = " + cast(@retID as varchar(1))


HTH

Tom