September 10, 2003 at 11:17 am
Looking for help with Dynamic SQL. Within a SP, I create a temp table. Then create a cursor for the purpose of updating a field in the temp table.
I know my syntax is not correct. This is where I need help. "Must declare cursor @BR" is the error message returned.
Thanks for your help.
Here is an example.
--Temp Table
Create Table #tmpAverages
(
Branch varchar(2),
RDpart varchar(15),
PD varchar(2),
Qty int
)
--Cursor
DECLARE @SQLString nVarchar(3000)
DECLARE @BR varchar(2)
DECLARE BR_Cursor CURSOR FOR
Select Distinct BranchCode from tblAveragesDtl
WHERE BranchCode IS NOT NULL AND LEN(RTRIM(BranchCode)) <> 0
OPEN BR_Cursor
FETCH NEXT FROM BR_Cursor
INTO @BR
WHILE @@FETCH_STATUS =0
BEGIN
Set @SQLString = 'Branch = ' + '''' + @BR + ''''
Execute sp_executesql @SQLString
Set @SQLString = NULL
FETCH NEXT FROM BR_Cursor
INTO @BR
END
deallocate BR_Cursor
September 10, 2003 at 1:18 pm
Hi,
Are you trying to print out the various BranchCodes within the cursor??
you might try replacing the sp_executesql with the following :
Set @SQLString = 'Branch = ' + '''' + @BR + ''''
Print @SQLString
Set @SQLString = NULL
sp_executesql does not identify the statement as a T-SQL statement and hence the error....you could alternatively try :
SET @SQLString = 'PRINT '+'''Branch = '+@BR+''''
Execute sp_executesql @SQLString
HTH
September 17, 2003 at 8:59 am
Looking at your example I can't see where you are populating your temporary table.
More importantly I'm wondering whether you really need to use a cursor to do what you want (cursors are generally bad news in terms of performance). Perhaps there is a Select statement you can use to select the data you want into the table such as
SELECT DISTINCT 'Branch ' + BranchCode
FROM tblAveragesDtl
WHERE BranchCode IS NOT NULL AND LEN(RTRIM(BranchCode)) <> 0
(I'm assuming here that the 'Branch ' + BranchCode is the data you want to generate)
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply