What Does A Query Return? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

What Does A Query Return?

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-26 : 09:30:54

Stupid sounding question, isn't it?

"What does a query return?"

The obvious answer is that it returns data; however, if I want to assign the results of a query to some variable VAR1, what data type does VAR1 need to be?

Declare @VAR1 UnknownType(unknownSize)
Exec @VAR1=StoredProcedureWhatever12
-- Do stuff with @VAR1, but what data type does
-- @VAR1 need to be?

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 09:35:43

it depends on datatype of returned data from procedure

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 09:36:14

INT.

See Return Codes in Books Online.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 09:37:55

quote:
Originally posted by visakh16

it depends on datatype of returned data from procedure

create proc visakh16
AS

return 'abc'
GO

EXEC visakh16

Conversion failed when converting the varchar value 'abc' to data type int.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 09:39:01

jp2code, you maybe are referring to OUTPUT parameters?

declare @var1 ...
declare @rc int

Exec @rc = StoredProcedureWhatever12 @VAR1 OUT

select @rc, @var1

@rc is the RETURN CODE from the stored procedure, and @var1 is the output parameter for the stored procedure.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 09:40:34

i was referring to OUTPUT parameter

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-26 : 10:30:16

Peso,

I think you are pretty close to what I'm looking for, but I've never seen a way to write a procedure to use an OUTPUT parameter. Do I need to modify my procedure for that, or just call it and place the @VAR1 in the propper spot?

Would this be what I'm getting at?

Declare @VAR1 varchar(1000)
Declare @rc int
Exec @rc=StoredProcedureWhatever12 @VAR1 OUT

In other words, would I declare my variable as a varchar array? How to I make sure it is the right size? Can I pass it a variable that can grow as needed?

Also, what does the last portion of your code say (select @rc, @var1)? What is it for?

Avoid Sears Home Improvement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:33:10

How does your stored procedure look like?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:38:56

quote:
Originally posted by jp2code

Peso,

I think you are pretty close to what I'm looking for, but I've never seen a way to write a procedure to use an OUTPUT parameter. Do I need to modify my procedure for that, or just call it and place the @VAR1 in the propper spot?

Would this be what I'm getting at?
Declare @VAR1 varchar(1000)
Declare @rc int
Exec @rc=StoredProcedureWhatever12 @VAR1 OUT

In other words, would I declare my variable as a varchar array? How to I make sure it is the right size? Can I pass it a variable that can grow as needed?

Also, what does the last portion of your code say (select @rc, @var1)? What is it for?

Avoid Sears Home Improvement

see this

http://www.sqlteam.com/article/stored-procedures-returning-data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources