Dynamic SQL Returning Value(s)

[WayneRyan]

The following "Action query" works:

Declare @sql Varchar(200)
Declare @DB Varchar(50)

Set @DB = 'SomeDatabase'
Set @sql = 'Insert Into ' + @DB + '..SomeTable From SomeTable'
Execute @sql

I can vary the database ... no problem.

But, if I need information returned like:

Set @sql = 'Declare @HowMany Int ' +
                'Select @HowMany = Count(*) From Sometable'
Execute @sql

I have no way to communicate with the dynamic SQL. Any dynamic SQL
that supports a multiple DB really hates --> "Select @Var = Field..."
Rightfully so, because the @Var has a very short lifespan.

How can I do the above with a variable database?
Also, how could I declare a Cursor with a variable database (or table)?

Is it because the Dynamic SQL has no "user interface"?

Thanks,
Wayne

 

[SQL_Hell]

Hey Wayne

You could do something like this:

declare @dbname varchar(50)
declare @sql varchar(100)
declare @count table
(
 icount int
)

set @dbname = 'master'
set @sql = 'SELECT COUNT(*) FROM ' +@dbname+ '..sysobjects'

insert into @count 
exec(@sql)
select * from @count

As for the cursor it should work dynamically, can you post an example that doesn't work?

 

[WayneRyan]

SQL Hell,

Thanks for the examples.

After a bit more research, dynamic cursors do work! That takes care of a lot of
my initial problems.

What I'd like to do however is find a technique that works for this:

Declare @Count Int
Select @Count = Count(*) From SomeTable

If SomeTable resides in an unknow database, I need:

Declare @Count Int
Declare @SomeDataBase Varchar(100)
Set @SomeDataBase = 'Master'

Select @Count = Count(*) From @SomeDatabase..SomeTable

Obviously that doesn't work.

The table variable seemed appropriate, but from the Help files:

"However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements."

Oh well ...

Thanks,
Wayne

 

[SQL_Hell]

Hi there

I am a bit confused, do you now need to execute stored procedure?

 

[WayneRyan]

SQL Hell,

Here's the story.

We have two primary databases:

EWDS_DB - Main database consisting of roughly 250 tables. This is an "evolving" database
with schema changes being a common part of each new release

EWDS_DB_Support - Companion database used primarily for updating the main database. It has
stored procedures and "Temp tables". We use BCP to populate the "Temp tables" with data
updates.

This setup has been successful, allowing us to send a monthly "data update" of about
one-megabyte (as opposed to sending a 350-megabyte database).

The latest effort is to Preserve User-Created between major releases.

1) User "tags" data
2) Stage extracted data in "Temp tables"
3) BCP data to disk file

4) BCP data into new support database
5) Alter the BCP data to new schema
6) Merge preserved data into "new" database

This became a set of 10 functional Stored Procedures with all references being:

EWDS_DB..SomeTable

EWDS_DB_Support..SomeTable

An executive decision was made to convert the 10 Stored Procedures into ones that have
no STATIC database references.

The cursors are no problem. The cursors defined by the dynamic SQL persist outside the
scope of the Execute statement.

Declare @sql Varchar(4000)
Declare @New_DB  Varchar(50)
Declare @New_Support  Varchar(50)

Set @New_DB = 'New_Main'
Set @New_Support = 'New_Support'

Set @sql = 'Declare MyCursor Cursor For ' +
           'Select Field1, Field2 From ' + @New_DB + '..SomeTable'
Execute (@sql)

The dynamic sql that are action queries are also not a problem.

Declare @sql Varchar(4000)
Declare @New_DB  Varchar(50)
Declare @New_Support  Varchar(50)

Set @New_DB = 'New_Main'
Set @New_Support = 'New_Support'

Set @sql = 'Insert Into ' + @New_DB + '..SomeTable ' +
           'Select * From ' + @New_Support + '..SomeTable'
Execute (@sql)

The problem is that there are many places in my code where I do something like:

Declare @HowMany Int
Select @HowMany = Count(*) From SomeTable Where ...
If @HowMany > 0 ...

That is very concise and easy, but with a variable database:

Declare @sql Varchar(4000)
Declare @New_DB  Varchar(50)
Declare @New_Support  Varchar(50)

Set @New_DB = 'New_Main'
Set @New_Support = 'New_Support'

Set @sql = 'Insert Into SomeStaticTable (Result) ' +
           'Select Count(*) From SomeTable Where ... '
Execute (@sql)

Select @HowMany = Result
From   SomeStaticTable 

If @HowMany > 0 ...

Not impossible, but a whole lot clumsier. I just hate the level of indirection
because the Execute sql doesn't "see" my local variables.

Thanks,
Wayne

 

[SQL_Hell]

Wayne,

Thanks for the detailed explanation, I think I have found a better solution than using a table variable or temp table, which to be fair is a bit clumsy when only holding one row.

exec sp_executesql allows an output parameter, look at this example taken from 2005 BOL:

USE AdventureWorks;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
  FROM Sales.SalesOrderHeader
  WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
                       @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
  @SQLString,
  @ParmDefinition,
  @CustomerID = @IntVariable,
  @SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

 

[WayneRyan]

SQL Hell,

Thanks for the idea. I have used sp_ExecuteSQL before. I'll be sure to
attach your sample to my estimate.

In all honesty, the database names are static. The schema for the
different releases can vary considerably, both in tables present and
the columns within them. This project was enough fun already dealing
with those differences. I didn't need this to complicate it further.

Thanks again,
Wayne