Stupid stored proc question

15 Dec 2005 9:13 PM

Bob

Whats the syntax for assigning the return value of a select statement in a
stored proc to a variable in the stored proc?
In the sample below I want to look up a value in an existing table and use
it later in the stored proc
Create PROC [dbo].[ShowMyStuff]

Myval = (Select TOP 1 Myfield from Mytable where CurrentUserNAme =
SUSER_SNAME())

etc....

The SELECT TOP statement is OK, it works in the query editor but how do I
pass it to Myval so I can use Myval later in the proc.

Sorry if it sounds stoopid but I can't put my finger on it. Any help
appreciated.

Bob

15 Dec 2005 9:23 PM

Raymond D'Anjou

All parameter names in SQL server start with @

Select TOP 1 @Myval = Myfield from Mytable where CurrentUserNAme =
SUSER_SNAME()
or
Set @Myval = (select TOP 1 Myfield from Mytable where CurrentUserNAme =
SUSER_SNAME())

If assigning a value to one parameter, I prefer using the second method.
If I need to assign values to 2 or more parameter I use the first one (the
first method doesn't work).

Show quote

"Bob" <bduf***@sgiims.com> wrote in message
news:ePgTixbAGHA.272@TK2MSFTNGP09.phx.gbl...
> Whats the syntax for assigning the return value of a select statement in a
> stored proc to a variable in the stored proc?
> In the sample below I want to look up a value in an existing table and use
> it later in the stored proc
> Create PROC [dbo].[ShowMyStuff]
>
> Myval = (Select TOP 1 Myfield from Mytable where CurrentUserNAme =
> SUSER_SNAME())
>
> etc....
>
> The SELECT TOP statement is OK, it works in the query editor but how do I
> pass it to Myval so I can use Myval later in the proc.
>
> Sorry if it sounds stoopid but I can't put my finger on it. Any help
> appreciated.
>
>
>
> Bob
>
>
>
>
>
>
>
>

15 Dec 2005 9:26 PM

Sericinus hunter

Try:

set @Myval = (select ...)

Bob wrote:
Show quote

> Whats the syntax for assigning the return value of a select statement in a
> stored proc to a variable in the stored proc?
> In the sample below I want to look up a value in an existing table and use
> it later in the stored proc
> Create PROC [dbo].[ShowMyStuff]
>
> Myval = (Select TOP 1 Myfield from Mytable where CurrentUserNAme =
> SUSER_SNAME())
>
> etc....
>
> The SELECT TOP statement is OK, it works in the query editor but how do I
> pass it to Myval so I can use Myval later in the proc.

16 Dec 2005 3:40 AM

Michael Cheng [MSFT]

Hi Bob,

Welcome to use MSDN Managed Newsgroup!

As Raymond and Sericinus have provided great response, here is some
articles about how to share the data between stored procedures, which I
think might help you extend the ideas in the future, for your reference.

How to share data between stored procedures
http://www.sommarskog.se/share_data.html

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.