asked on

Cursor and dynamic sql

Hello,

When I execute :

declare @sql7 varchar(max)
set @sql7 = '

DECLARE parcours_majsp_repl CURSOR FAST_FORWARD FOR SELECT a.SQLTEXT as LigneSQL,a.name as ProcName
FROM ['+@SrvAbonne + '].['+ @DbCible + '].dbo.maj_replsp a
OPEN parcours_majsp_repl
FETCH NEXT FROM parcours_majsp_repl INTO @strLigneSQL,@strProcName
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER OFF
IF EXISTS(SELECT * FROM ['+@SrvAbonne + '].['+ @DbCible + '].sys.objects where type=''P'' AND NAME= @strProcName)
BEGIN
SELECT * FROM ['+@SrvAbonne + '].['+ @DbCible + '].sys.objects
END
END
'
print @sql7
execute @sql7

I have this error

The name '

DECLARE parcours_majsp_repl CURSOR FAST_FORWARD FOR SELECT a.SQLTEXT as LigneSQL,a.name as ProcName
FROM [SRVD].[SOL].dbo.maj_replsp a
OPEN parcours_majsp_repl
FETCH NEXT FROM parcours_majsp_repl INTO @strLigneSQL,@strProcName
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER OFF
IF EXISTS(SELECT * FROM [SRVD].[SOL].sys.objects where type='P' AND NAME= @strProcName)
BEGIN
SELECT * FROM [SRV].[SOL].sys.objects
END
END
' is not a valid identifier. #203

Thanks

bibi

brutaldev

Not sure if that's even possible. Execution will not work on multiple statements, you can only pass one statement into sp_execute (or using the EXEC statement). Just select into a temp table first and run the cursor off that. Something like this should work:

SET QUOTED_IDENTIFIER OFF

-- Get results into a temp table first for the cursor
declare @sql7 varchar(max)
set @sql7 = '
SELECT a.SQLTEXT as LigneSQL,a.name as ProcName
INTO #myTempResults
FROM ['+ @SrvAbonne + '].['+ @DbCible + '].dbo.maj_replsp a
'
print @sql7
execute @sql7

DECLARE parcours_majsp_repl CURSOR FAST_FORWARD FOR
SELECT * FROM #myTempResults

OPEN parcours_majsp_repl
FETCH NEXT FROM parcours_majsp_repl INTO @strLigneSQL,@strProcName

WHILE @@FETCH_STATUS = 0 
BEGIN 
  SET @sql7 = '
    IF EXISTS(SELECT * FROM ['+@SrvAbonne + '].['+ @DbCible + '].sys.objects where type=''P'' AND NAME= @strProcName)
    BEGIN
      SELECT * FROM ['+@SrvAbonne + '].['+ @DbCible + '].sys.objects
    END
'
  -- Exec the second dynamic statement
  print @sql7
  execute @sql7
  FETCH NEXT FROM parcours_majsp_repl INTO @strLigneSQL,@strProcName
END
CLOSE parcours_majsp_repl
DEALLOCATE parcours_majsp_repl

Open in new window

You may want to make one or two tweaks to it but you should see what's changed here and why. Don't forget to close and deallocate the cursor as well. You should also get more records in the loop by fetching again.

bibi92

ASKER

Same error.thanks

ASKER CERTIFIED SOLUTION

brutaldev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bibi92

ASKER

Thanks bibi