Re: Why can't variable assignment occur in a data retrieval operat

From: Novice (6tc1ATqlinkDOTqueensuDOTca)
Date: 11/22/04 

Date: Mon, 22 Nov 2004 15:53:07 -0800

You've answered my question - I didn't realize that the problem _wasn't_ that
it is not allowed to have both variable assingment in a select statement in a
stored procedure and then another select statement that uses those variables.
 The actual restriction is that you can't have a single select statement both
assign to variables and return results.

I still find that strange - since the server should be able to return the
portion of the select statement that you are not assigning to variables - but
whatever.

Thanks for you help,
Novice

"Adam Machanic" wrote:

> That restriction is in place, I assume, because of the logical problem with
> how to construct a rowset when some of the rowset should be sent to the
> client and some assigned to a variable... But based on your narrative, I
> don't understand why you're running into the issue. Can you post some code?
>
> It's legal to do:
>
> SELECT @A = ColA,
> @B = ColB
> FROM YourTable
> WHERE ...
>
> SELECT *
> FROM OtherTable
> WHERE ColA = @A
> AND ColB = @B
>
>
>
> What's not legal is:
>
> SELECT @A = ColA,
> @B = ColB,
> ColC
> FROM YourTable
> WHERE ...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "Novice" <6tc1ATqlinkDOTqueensuDOTca> wrote in message
> news:026E848F-B367-4F47-B518-CA17D02742E9@microsoft.com...
> > Hey all, I just ran across a peculiar "feature" in SQL server. I wrote
> this
> > stored procedure that is set up to return multiple result sets (from 3
> > different select statements). I could make the 3 different select
> statements
> > MUCH more efficient if I could assign some data from the first select
> > statement to a couple of variables and then use those variables in the
> next
> > two select statements.
> >
> > However, if I try to return any result sets from my stored procedure as
> well
> > as assign data from a previous select to a variable I get this error:
> > A SELECT statement that assigns a value to a variable must not be combined
> > with data-retrieval operation
> >
> > I have another way that I can construct the query - but I can't for the
> life
> > of me think of why SQL Server would prohibit this in a stored procedure.
> Is
> > this a security feature?
> >
> > Novice
>
>
>

Relevant Pages

  • Re: Why cant variable assignment occur in a data retrieval operation?
    ... how to construct a rowset when some of the rowset should be sent to the ... AND ColB = @B ... I just ran across a peculiar "feature" in SQL server. ... if I try to return any result sets from my stored procedure as ...
    (microsoft.public.sqlserver.programming)
  • Re: connect sql mobil to "main sql server"
    ... While there are ways to replicate between SQL Server and SQLCe, ... way to import data directly from a SP rowset. ... Hitchhiker's Guide to Visual Studio and SQL Server ... stored procedure runs and pulls data from the main database to the SQL ...
    (microsoft.public.sqlserver.ce)
  • Re: Stored procedure with insert or update doesnt return rowset.
    ... > I'm using SQLOLEDB with SQL Server 2000. ... > Stored procedure like this: ... > - doesn't return rowset. ...
    (microsoft.public.data.oledb)
  • Re: Can A stored procedure return a table variable?
    ... It can simply return the rows as a rowset. ... SQL Server MVP ... Columnist, SQL Server Professional ... Can a stored procedure return an table output parameter? ...
    (microsoft.public.sqlserver.programming)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)