Databases
StackOverflow2013
Postsdbo
POCross-database query with database name in variable and cursor
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCross-database query with database name in variable and cursor
    primarykey
    data
    text
    <p>I have a problem with name of database in a cursor. Here the current code</p> <pre><code>DECLARE @IDES INT DECLARE @IDPROD INT DECLARE @count INT SET @count = 0 DECLARE CUR_CONSO CURSOR LOCAL fast_forward FOR SELECT E2.id_es ,P3.id_prod FROM [gpto_v004p001].[dbo].[GPTO_PRODETAB] P1 INNER JOIN [fer_v008].[dbo].[T_PRODUIT] P3 ON P3.GPTO_PRODUIT_ID = P1.GPTO_PRODUIT_ID INNER JOIN [gpto_v004p001].[dbo].[GPTO_ETAB] E1 ON E1.ETABID = P1.ETABID INNER JOIN [fer_v008].[dbo].[t_etablissement] E2 ON E1.ETABUC = LEFT(E2.code_es,5) LEFT JOIN [fer_v008].[dbo].[t_produit_etablissement] PE1 ON PE1.id_prod = P3.id_prod AND PE1.id_es = E2.id_es WHERE PE1.id_es IS NULL AND GPTO_PRODUIT_ETAPE = 4 OPEN CUR_CONSO FETCH CUR_CONSO INTO @IDES , @IDPROD WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS (Select * from [fer_v008].[dbo].[t_produit_etablissement] where id_es=@IDES and id_prod=@IDPROD) -- Pas d'enregistrements BEGIN INSERT INTO [fer_v008].[dbo].[t_produit_etablissement] ([id_es],[id_prod],[gest_prod]) VALUES (@IDES,@IDPROD,0) SET @count = @count + 1 END FETCH CUR_CONSO INTO @IDES , @IDPROD END CLOSE CUR_CONSO DEALLOCATE CUR_CONSO </code></pre> <p>As the database is versionned, I need to use database name as variable saved in parameter table. For easy script, I use Execute command as this</p> <pre><code>DECLARE @base_travail varchar(128) SELECT @base_travail = val_str_par FROM t_parametre WHERE nom_par = 'base_travail' DECLARE @execcmd varchar(max) SET @execcmd = 'insert into #tmpfiltres SELECT TOP 1 filtre_exu FROM ' + @base_travail + '.dbo.t_export_util WHERE id_exu =' + convert(varchar,@id_exu) Execute (@execcmd) </code></pre> <p>But how do this when I have a cursor ? The 1st sample code is just a sample, whole script go over 400 lines, so I can't switch all the script in string mode.</p> <p>Thanks for your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USUser.Anonymous
    1. USUser.Anonymous
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan you show how you want the cursor to actually work? What are you doing inside the cursor? You've shown two code samples here and I can't piece together how they're related at all.
      singulars
      1. POCross-database query with database name in variable and cursor
      1. USAaron Bertrand
    2. COAlso, [please don't specify varchar without length](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx).
      singulars
      1. POCross-database query with database name in variable and cursor
      1. USAaron Bertrand
    3. COI have add the 1st cursor. Script has 4 similar cursor.
      singulars
      1. POCross-database query with database name in variable and cursor
      1. USUser.Anonymous
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload