Stored Procedure Select to Retrieve Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure Select to Retrieve Values

Author

Topic

joe iacoponi
Starting Member

11 Posts

Posted - 2002-04-04 : 15:49:05

I want to perform what seems like a simple SELECT statement in a Stored Procedure, but I can't find the answer.

In ASP, I would just use:
SELECT FirstName, LastName FROM Authors WHERE au_id = 111-22-3333
in order to retrieve the first and last name.

How do I do that in a Stored Procedure, however.

I initially used:
SELECT @LastName = (SELECT TOP 1 LastName FROM Authors WHERE au_id = 111-22-3333 )
SELECT @FirstName = (SELECT TOP 1 FirstName FROM Authors WHERE au_id = 111-22-3333 )

but this is such a waste. What am I missing here. Thanks, guys.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-04 : 15:51:51

do the same thing ... all ASP dynamic SQL can be plopped into a procedure (assuming that the dynamic SQL was written for SQL Server)

but i just realized your dilemma(sp?) .. you need ' around the string... in this case 111- whatever ... just make it '111- whatever' and you should be good to go

SELECT FirstName, LastName FROM Authors WHERE au_id = '111-22-3333'

Edited by - onamuji on 04/04/2002 15:53:40

joe iacoponi
Starting Member

11 Posts

Posted - 2002-04-04 : 15:55:42

Sorry, I meant to have the '' around the ID, bt my problem is with retrieveing two fields at the same time.

How do I get the results of first and last name into @FirstName and @LastName.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-04 : 16:11:08

you're still not making sense. so i'll try to make sense and give you three possible meanings for what you are asking...


#1
-- basic selecting of column value into variables.
 select @FirstName = FirstName,
        @LastName  = LastName
   from Authors
  where au_id = '111-22-3333'
 
#2
-- procedure to return name as output parameters (best bet for calling from ASP)
 create procedure get_name 
   @au_id varchar(32), 
   @FirstName varchar(256) = null output, 
   @LastName varchar(256) = null output
 as
 select @FirstName = FirstName,
        @LastName  = LastName
   from Authors
  where au_id = @au_id
go
 
declare @FirstName varchar(256)
declare @LastName varchar(256)
exec get_name '111-22-3333', @FirstName output, @LastName output
Print @FirstName + ' ' + @LastName
Print @LastName + ', ' + @FirstName
 
#3
-- Equivalent procedure to ASP dynamic sql which returns recordset
create procedure get_name @au_id varchar(256) as
 set nocount on

 select FirstName,
        LastName
   from Authors
  where au_id = @au_id
go

hope this helps.

Jay99

468 Posts

Posted - 2002-04-04 : 16:14:07


--create the proc
create proc usp_Joe
    @au_id varchar(25),
    @lastname varchar(50) out,
    @firstname varchar(50) out
as
select
    @lastname = lastname,
    @firstname = firstname
from
    Authors
where
    au_id = @au_id
go

--execute the proc
declare
    @first varchar(50),
    @last varchar(50)
exec usp_Joe '111-22-3333', @last out, @first out
select @last, @first
go

Jay
<O>

EDIT: Sniped ... I'm hit

Edited by - Jay99 on 04/04/2002 16:15:38

joe iacoponi
Starting Member

11 Posts

Posted - 2002-04-04 : 17:24:05

Thanks for this,

The first one is just what I'm looking for. I knew it was sooo simple, but I just didn't have it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources