route of select statement data

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)

route of select statement data

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 15:27:36

i have select statement which returns only one row, and i want that one of select statement returned value will be writed into parameter

for example

 
DECLARE @VARIABLE INT       --this variable i want to return by sp

   SELECT @VARIABLE=FIELD1,
   	            FIELD2,
   	            FIELD3,
   	            FIELD4,
   FROM OPTIONS WHERE ID=1 

   
   RETURN @VARIABLE

it is possible ?,
by this way i get the error = A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

maybe exists another ways for this ?

thanks..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-02 : 15:31:19

You need to OUTPUT the parameter. You also can not retrieve a record set when you are trying to put a column into a variable. You have to retrieve the record set in a different statement.

For example:

CREATE PROC usp_TestProc
(@VARIABLE INT OUTPUT)
AS

SELECT @VARIABLE = FIELD1
FROM OPTIONS
WHERE ID=1

SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM OPTIONS
WHERE ID=1

RETURN

Tara

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 15:43:28

reason for my post was 2 select using like

SELECT @VARIABLE = FIELD1
FROM OPTIONS
WHERE ID=1

SELECT FIELD1, FIELD2, FIELD3, FIELD4
FROM OPTIONS
WHERE ID=1

i' was trying to use one select, understandable, my previous post is not possible with their construction, ok i will trying something else,

only one way, is to place all fields into params

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-02 : 15:48:28

Yes you can put all columns into variables so that you can do it in one statement, but keep in mind that you must OUTPUT each of them in order to get the results back.

Tara

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 15:59:46

re>but keep in mind that you must OUTPUT each of them in order to get the results back

yes, i must to select this output params

here is v.simple sp



ALTER PROCEDURE P_DOSKA_RUBRIKA_GET
(
 @RAZDEL_ID D_MAIN_ID,
 @RUBRIKA_DIVIDE D_MAIN_ID = 2 OUTPUT,
 @OBIAVLENIE_SAG D_MAIN_ID = 2 OUTPUT,
 @OBIAVLENIE_SAG_PER_LINE D_MAIN_ID = 2 OUTPUT,
 @OBIAVLENIA_MAX_SELECTED D_MAIN_ID = 2 OUTPUT,
 @TEMA_NOFOTO D_DOSKA_OBIAVLENIA_TEMA_NOFOTO ='' OUTPUT 
)
AS
BEGIN

  --select statement with params begin
  SELECT @RUBRIKA_DIVIDE=COL_COUNT_RUBRIKA,
		 @OBIAVLENIE_SAG=OBIAVLENIE_SAG,
		 @OBIAVLENIE_SAG_PER_LINE=OBIAVLENIE_SAG_PER_LINE,
		 @OBIAVLENIA_MAX_SELECTED=OBIAVLENIA_MAX_SELECTED,
		 @TEMA_NOFOTO=TEMA_NOFOTO 
  FROM DOSKA_OPTIONS WHERE ID=1
  --select statement with params end


  SELECT a.ID,a.RUBRIKA_NAME,		  
         b.ID,b.PODRUBRIKA_NAME,b.OBIAVLENIA_COUNT,b.OBIAVLENIA_COUNT_NEW
  FROM DOSKA_RUBRIKA a JOIN DOSKA_PODRUBRIKA b
						 ON a.ID=b.RUBRIKA_ID
						    AND
							a.RAZDEL_ID=b.RAZDEL_ID
  WHERE a.RAZDEL_ID=@RAZDEL_ID

END

by this way ( see below ) i get results of this sp in QA and in my PHP

 
DECLARE @RUBRIKA_DIVIDE D_MAIN_ID,
		@RAZDEL_ID D_MAIN_ID,
		@OBIAVLENIE_SAG D_MAIN_ID,
		@OBIAVLENIE_SAG_PER_LINE D_MAIN_ID,
		@OBIAVLENIA_MAX_SELECTED D_MAIN_ID,
		@TEMA_NOFOTO D_DOSKA_OBIAVLENIA_TEMA_NOFOTO


EXECUTE P_DOSKA_RUBRIKA_GET @RAZDEL_ID=456,
							@RUBRIKA_DIVIDE=@RUBRIKA_DIVIDE OUTPUT,
							@OBIAVLENIE_SAG=@OBIAVLENIE_SAG OUTPUT,
							@OBIAVLENIE_SAG_PER_LINE=@OBIAVLENIE_SAG_PER_LINE OUTPUT,
						    @OBIAVLENIA_MAX_SELECTED=@OBIAVLENIA_MAX_SELECTED OUTPUT,
							@TEMA_NOFOTO=@TEMA_NOFOTO OUTPUT 
							
SELECT @RUBRIKA_DIVIDE,
	   @OBIAVLENIE_SAG,
	   @OBIAVLENIE_SAG_PER_LINE,
	   @OBIAVLENIA_MAX_SELECTED,	
	   @TEMA_NOFOTO

really said i hate this type of getting output params into QA and INTO PHP, this type takes much place of my php scripts....

now very important for me question ... ( question connects to the --select statement with params)

what will be better, two select statements in sp,

or

one select statement in sp, and much output params

in which of this two variants speed will be better ?

and what you can advice to me ,

thanks for help

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-02 : 16:01:48

Are you sure you're talking about int datatype?

If I understand you correctly, I think your asking for:


SELECT @VARIABLE=   FIELD1+' '+
   	            FIELD2+' '+
   	            FIELD3+' '+
   	            FIELD4
   FROM OPTIONS WHERE ID=1

Brett

8-)

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 16:04:53

re>If I understand you correctly, I think your asking for:
no, i dont talking about String Concatenation,noo

i'am very bad in english, and sometimes people dont understand what i am talking about

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-02 : 16:07:56

Well it just depends if you want all of the values. Do you want all 4 values? Or do you just want a record set returned? If you just want @VARIABLE, then only get that one:

CREATE PROC usp_TestProc
(@VARIABLE INT OUTPUT)
AS

SELECT @VARIABLE = FIELD1
FROM OPTIONS
WHERE ID=1

RETURN

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-02 : 16:08:49

Hey marconi8,

Where are you from:

Why not fill out your bio:

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=8143

Brett

8-)

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 16:17:40

some minutes ago i filled up my p.data

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-02 : 16:40:00

How's Latvia this time of the year?

I want to ask a basic question.

Is the stored procedure being called by another stored procedure, or an application, like something coded in Java, asp.net, ect?

Brett

8-)

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-02 : 16:45:26

sorry , that cannot answer at your question, i must go to the bus, because at this time is last bus to my district

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-03 : 08:31:51

re> How's Latvia this time of the year?

sorry, i dont know anything about Latvia and their events, this
country dont Interest me, i'am only interested in my work

re> Is the stored procedure being called by another stored procedure, or an application, like something coded in Java, asp.net, ect?

my stored procedure is called by two ways....

1st way is calling procedure in QA, in QA i call procedures for syntax and logic tests

2nd way is calling procedure in php area like this

$rs=$mssql_connection->EXECUTE("EXECUTE P_DOSKA_RUBRIKA_GET @RAZDEL_ID=".$HTTP_SESSION_VARS['rrpgp']['razdel']);
$rs1=$rs->GetArray();
$rs->NextRecordSet();
$rs2=$rs->GetArray();

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources