|
Home > Archive > Microsoft SQL Server forum > July 2005 > stored procedure question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
stored procedure question
|
|
| wolfing1@yahoo.com 2005-07-06, 9:23 am |
| Hi. I'm creating a stored procedure that inserts a record if it's not
already there, otherwise returns -1. It's like this...
---
CREATE PROCEDURE createsession
@username varchar(20)
AS
begin
--Validate session not already there
select 1 from a..Sessioninfo where username = @username
if @@rowcount = 0
begin
insert into a..Sessioninfo (username)
values (@username)
select @@identity
end
else
select -1
end
GO
----
Problem is, it's returning 2 rows I think, one from the 'Select 1
from...' and the other from the 'select @@identity'. I know I could
use an 'output parameter' instead, but just for my knowledge, how could
I make the first select so it doesn't return a row to the outside?
| |
|
| instead of
select 1 from a..Sessioninfo where username = @username
if @@rowcount = 0
use
select @count=count(*) from a..Sessioninfo where username = @username
if @count = 0
| |
| Simon Hayes 2005-07-06, 11:23 am |
| If you do a 'straight' SELECT like that, it will always return a result
set, so your client application has to handle it. A better option in
this case would probably be something like this:
CREATE PROCEDURE dbo.CreateSession
@username varchar(20),
@out int OUTPUT
AS
begin
set @out = -1
if not exists (select * from a.dbo.SessionInfo where username =
@username)
begin
insert into a.dbo.SessionInfo (username)
values (@username)
set @out = scope_identity()
end
end
It's usually much faster to return scalar values in output parameters
than in a result set.
Simon
| |
| vjdileo 2005-07-06, 11:23 am |
| Use the 'if exists' logic. Here's the setup:
/****** Create a table with an identity column. ******/
create table #mytemp ( idnum int identity(1,1), col_1 char(1) )
/****** Insert a test value *******/
insert #mytemp (col_1) values('a')
/***** Try this ******/
if exists ( select col_1 from #mytemp where col_1 = 'a' )
select 'it exists'
else
select 'it does not exist'
/***********/
/****** Your sp might look like this ******/
create procedure mynewproc
@input char(1)
as
begin
if not exists ( select col_1 from #mytemp where col_1 = @input )
begin
insert #mytemp (col_1) values( @input )
select @@identity
end
else
select -1
end
/**********/
hth,
victor dileo
|
|
|
|
|