Hi Kirk,

The dynamic SQL run by EXECUTE command or sp_ececutesql sproc is a separate batch. It cannot see variables in calling sproc. You may try sp_execute sql which accepts parameters.

CREATE PROCEDURE ct_Get_Note_ListItems
  @tiNoteID int,
  @gnMeetID  int

as
declare @tiMeetID int
declare @QueryString varchar(4000)

select @QueryString = querystring from NoteControlQueries where noteid = @tiNoteID
IF NOT IS NULL @QueryString 
  sp_executesql @QueryString, N'@tiMeetID int', @gnMeetID

Also, if you've only one query per noteid than you don't need a cursor.

>I have a table (NoteControlQueries) that contains a sql statement to load data into dynamic controls. Simple table with a noteid,querystring. There is one record for each noteid.
>
>I am trying to create a stored procedure that when passed the noteid and meetingsnumber will return a result set based off the querystring stored in the NoteControlQueries table for that noteid. The code below almost works, it gets the query string...but. The query string uses a variable called @tiMeetID. This is the meetingnumber I pass when I called the stored procedure allow with the noteid. But I get the following error in the messages dialog when running it from the Query Analyzer.
>
>

>Opening Cursor
>tcQueryString=SELECT dbo.co_lu_Ident.recordid,dbo.co_lu_Ident.idtype,
>	 ISNULL(dbo.co_pat_ID.answerflag,0) as answerflag,
>	ISNULL(co_pat_id.pat_id_ID,0) as patidid
>FROM dbo.co_lu_Ident LEFT OUTER JOIN
>	dbo.co_Pat_ID ON dbo.co_lu_Ident.recordid = dbo.co_pat_ID.RecordID 
and co_Pat_ID.meetingnumber=@tiMeetID
>Server: Msg 137, Level 15, State 2, Line 5
>Must declare the variable '@tiMeetID'.
>

>
>
>Here is the code in the stored procedure:
>
>

>CREATE PROCEDURE ct_Get_Note_ListItems
>@tiNoteID int,
>@gnMeetID  int
>
>as
>declare @tiMeetID int
>set @tiMeetID=@gnMeetID
>
>declare c cursor local
>for select isnull(querystring,' ') as QueryString from NoteControlQueries 
where noteid=@tiNoteID
>
>print 'Opening Cursor'
>
>Open c
>
>
>declare @tcQueryString varchar(4000)
>
>fetch c into @tcQueryString
>
>close c
>print 'tcQueryString=' + @tcQueryString
>
>exec(@tcQueryString)
>
>

>
>Thanks for any help
>
>Kirk