• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
bibi92
Asked:
bibi92
  • 2
  • 2
1 Solution
 
brutaldevCommented:
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.
0
 
bibi92Author Commented:
Same error.thanks
0
 
brutaldevCommented:
If you are still getting the same error then there must be something wring with that SQL.

I ran it locally but changes the tables names that did not exist and the results come through just fine.

Start by hard coding the SQL that will be executed so you can be convinced of no errors. Then wrap it in a string and try execute it again. This is the same process I went though to get you code working with tables that do not exist.
0
 
bibi92Author Commented:
Thanks bibi
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now