Hi,
I have created a simple stored procedure and I am getting some errors in it.
I couldn't figure out why the error is.
Any help would be appreciated.
Here is my sp:
---
CREATE PROCEDURE dbo.test
(
@.ID int,
@.NUMBERS nvarchar(2000)
)
AS
DECLARE @.REF int
EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
FROM TABLE2
WHERE (DS_ID IN (8))
AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')))
AND (REF <= 0) AND (S_ID ='+ @.ID + '))
GROUP BY REF')
OPEN TESTING
FETCH NEXT FROM TESTING
INTO @.REF
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doing something
FETCH NEXT FROM TESTING INTO @.REF
END
CLOSE TESTING
DEALLOCATE TESTING
---
When I try to run this, the errors I get
Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
A cursor with the name 'TESTING' does not exist.
Thanks
KiranKiran,
The rest of the Cursor code need to be within the EXEC statement.
Gopi
"Kiran" <kiran_nospam@.gmail.com> wrote in message
news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
> Hi,
> I have created a simple stored procedure and I am getting some errors in
> it.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' +
> ')))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Don't declare it as a local cursor, that makes it be local to the EXEC. And,
consider if you can do
without the cursor in the first place, tend to be code easier to read and pe
rform better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gb
l...
> Hi,
> I have created a simple stored procedure and I am getting some errors in i
t.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')
))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Thanks a ton Tibor.
Kiran kumar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ed39iCfWFHA.616@.TK2MSFTNGP12.phx.gbl...
> Don't declare it as a local cursor, that makes it be local to the EXEC.
> And, consider if you can do without the cursor in the first place, tend to
> be code easier to read and perform better.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kiran" <kiran_nospam@.gmail.com> wrote in message
> news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
>
