[thelist] Stored Procedure Problem (EXEC)

[Shaun Anderson]

--
[ Picked text/plain from multipart/alternative ]
I've got the following SP (Warning: I'm a SP Newbie):

---------------------------------------------------------------------------
CREATE PROCEDURE sp_CMSFormsSegments
  @Category int,
  @Language varchar(100)

AS
DECLARE @ExecCode varchar(8000)

SELECT @ExecCode = 'SELECT tblCMSFormsSegments_ID AS SegID, ' + @Language + ' AS TheText FROM tblCMSFormsSegments Segs INNER JOIN tblCMSTranslations Trans ON Segs.tblCMSTranslations_ID = Trans.tblCMSTranslations_ID WHERE Category = ' + CAST (@Category AS VARCHAR)
--  EXEC @ExecCode
PRINT @ExecCode

---------------------------------------------------------------------------

But it isn't working quite right.  It outputs:

SELECT tblCMSFormsSegments_ID AS SegID, English AS TheText FROM tblCMSFormsSegments Segs INNER JOIN tblCMSTranslations Trans ON Segs.tblCMSTranslations_ID = Trans.tblCMSTranslations_ID WHERE Category = 1

Like I would expect,but when I do:
EXEC @ExecCode

I get this error:

Server: Msg 911, Level 16, State 1, Line 9
Could not locate entry in sysdatabases for database 'SELECT tblCMSFormsSegments_ID AS SegID, English AS TheText FROM tblCMSFormsSegments Segs INNER JOIN tblCMSTranslations Trans ON Segs'. No entry found with that name. Make sure that the name is entered correctly.

But if I run what's PRINTed as a stand-alone query I get the columns and the correct rows that I want.

Why is it looking for "database 'SELECT......"?

I'm using SQL Server 7 if it makes a difference.

Thanks,
Shaun



--