How to assign values to variables in a procedure in with sele

I tried that but get an error for syntax check
Error141 - A select statement that assigns a value to a variable must not be
combined with data retrieval operation.
I am using SQL Server 7 .
Any help will be greatly appreciated.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT @.name = name, @.sex = sex
> FROM table1
> WHERE (something that guarantees exactly one row)
>
> "JS" <JS@.discussions.microsoft.com> wrote in message
> news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
>
>> Error141 - A select statement that assigns a value to a variable must not
> be
> combined with data retrieval operation.
Then you didn't run exactly what I posted. Could you show exactly what you
tried to run?
You can't SELECT and assign in the same statement. So, if you are sure that
the WHERE clause will always limit to one row, *and* for some reason you
need to SELECT the data *and* return it in output parameters (why would you
need to do both?), you can do this:
-- assign variable values from table
SELECT @.name = name, @.sex = sex FROM table WHERE ...
-- return *variables* to client
SELECT name = @.name, sex = @.sex|||JS wrote:
> I tried that
I doubt that you tried exactly what he said. It would help if you showed us
the revised code, but I suspect your statement now looks like:
SELECT NAME,SEX,@.name = name, @.sex = sex
from table1 where .....
Right?

> but get an error for syntax check
> Error141 - A select statement that assigns a value to a variable must
> not be combined with data retrieval operation.
That's a pretty self-explanatory error message: in a single sql statement
you can either return data to the client or assign values to variables. You
cannot do both in a single statement.
I suspect what you are trying to to do is:
SELECT name = name, @.sex = sex
from table1 where .....
SELECT @.name, @.sex

> I am using SQL Server 7 .
>
Doesn't matter
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Here it is:
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Then you didn't run exactly what I posted. Could you show exactly what yo
u
> tried to run?
> You can't SELECT and assign in the same statement. So, if you are sure th
at
> the WHERE clause will always limit to one row, *and* for some reason you
> need to SELECT the data *and* return it in output parameters (why would yo
u
> need to do both?), you can do this:
> -- assign variable values from table
> SELECT @.name = name, @.sex = sex FROM table WHERE ...
> -- return *variables* to client
> SELECT name = @.name, sex = @.sex
>
>|||Here is my code
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
What I am trying to do is assign the values to the OUTPUT variables, so that
the client can see the values of the OUTPUT field.
Please help
"Bob Barrows [MVP]" wrote:

> JS wrote:
> I doubt that you tried exactly what he said. It would help if you showed u
s
> the revised code, but I suspect your statement now looks like:
> SELECT NAME,SEX,@.name = name, @.sex = sex
> from table1 where .....
> Right?
>
> That's a pretty self-explanatory error message: in a single sql statement
> you can either return data to the client or assign values to variables. Yo
u
> cannot do both in a single statement.
> I suspect what you are trying to to do is:
> SELECT name = name, @.sex = sex
> from table1 where .....
> SELECT @.name, @.sex
>
> Doesn't matter
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30)
> OUTPUT,@.sex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
(a) do you not have a primary key on SSN? If so, there is no need to set
rowcount 1, since there will only ever be a maximum of one match. If there
is no primary key, why not?
(b) sorry, but there is no way that the procedure above yields the error you
mentioned earlier. Either you transcribed it wrong or you are looking at
the wrong code.|||Also,
(a) can you come up with a more useless and generic name for your procedure
than GET_DATA? At least you use the dbo prefix...
(b) I recommend better formatting so your procedure is readable.
(c) I strongly recommend against these implicit, non-ANSI join syntaxes.
How about:
CREATE PROCEDURE dbo.GetNameSexData
@.ssn VARCHAR(9),
@.name VARCHAR(30) OUTPUT,
@.sex VARCHAR(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT 1
SELECT
@.name = nt.Name,
@.sex = ot.Sex
FROM
Name_Table nt
INNER JOIN Other_Table ot
ON nt.ssn = ot.ssn
WHERE nt.ssn = @.ssn
END
GO
Now, if that produces an error when you *call* it, show the method you used
to *call* it.|||Got it I was declaring the variables but assigining all the values in the
select statement for the output type. Thanks very much for all the help
"JS" wrote:
> Here is my code
> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.s
ex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
> What I am trying to do is assign the values to the OUTPUT variables, so th
at
> the client can see the values of the OUTPUT field.
> Please help
> "Bob Barrows [MVP]" wrote:
>

How to add a TCP endpoint in SQl Server 2005 Beta 2

Hi All,

I would like to know the syntax of creating an endpoint in SQL Server 2005 Beta 2 version.

The syntax which i am using is as follows:

create endpoint yuk1
state = started
as TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE trpyuk1)

And i am getting the following error :

Incorrect syntax near 'CERTIFICATE'.

Regards
Deepak

You are using beta 3 syntax. Is there any reason why you are trying to use beta 2?|||Hi,

Ok, thanks for the update. SO is this syntax ok for the June Community Version?

Regards
Deepak|||Yes.

-mike

how to accquer certain number of records

hi,every body.
I want to use SQL syntax to get records from a database table. As the
table may contain large number of records,I would like to get a certain
number of records first and also can get more records later as necessary.Can
anyone help me?
CrespoHi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||Good work.Thank Uri Dimant very much!
Crespo
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>|||"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
necessary.Can
> anyone help me?
If using SQL 2005, look into using ROW_NUMBER also.
>
> Crespo
>|||Thank Uri Dimant. your suggestion works well. Thans again.
Crespo
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢
news:eEkxHJflGHA.4716@.TK2MSFTNGP04.phx.gbl...
Hi
http://www.aspfaq.com/show.asp?id=2120
"Crespo" <wuhuanfa@.21cn.com> wrote in message
news:eWB4YGflGHA.4716@.TK2MSFTNGP04.phx.gbl...
> hi,every body.
> I want to use SQL syntax to get records from a database table. As the
> table may contain large number of records,I would like to get a certain
> number of records first and also can get more records later as
> necessary.Can
> anyone help me?
>
> Crespo
>