Select statement to a Sting variable issues

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 2005 Forums

Transact-SQL (2005)

Select statement to a Sting variable issues

Author

Topic

wingfielda
Starting Member

2 Posts

Posted - 2007-07-02 : 14:50:06

I am getting the following error:
Msg 137, Level 15, State 1, Line 1 Must declare the variable '@MbrLname'.

the code from my stored procedure:

DECLARE @MbrLname VARCHAR(40), @MbrFname VARCHAR(30), @IndvlSrch VARCHAR(400)

SET @IndvlSrch = 'SELECT @MbrLname = LastName, @MbrFname = FirstName
FROM MbrDetail
WHERE (MbrId = @MbrId)'

EXEC(@IndvlSrch)

If I run the select statement it works just fine. When I execute it as a string variable, SQL Server returns an error and null values for the names. What am I doing wrong?

This is part of a larger dynamic query that I am using to print mailing invoices. Based on other variables control the output. I chose the namse cause I thought that would be the easy to understand.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-02 : 14:53:14

Why are you using dynamic SQL for this? Local variables are not visible inside the dynamic SQL session, which is why are getting the error.

Why not just do this:

DECLARE @MbrLname VARCHAR(40), @MbrFname VARCHAR(30), @IndvlSrch VARCHAR(400)

SELECT @MbrLname = LastName, @MbrFname = FirstName
FROM MbrDetail
WHERE MbrId = @MbrId

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 14:54:01

You are using Dynamic SQL.

http://www.sommarskog.se/dynamic_sql.html

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 14:54:25

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources