Could not make it work

[OldSlowDog]

This is the first time I do dynamic query. I have a hard time to figure this out. Please help.
I am trying to walk through 10 different db in a SQL server with one query to pull the sales data into a collective table in Corpdb.
We have 10 stores and each store is a db in the server with the same table names and etc.:

SQLSVR_XZY(SQL SERVER 9.0..)
Corpdb
Store1db
Store2db
Store3db
Store4db
... and so on

My plan is to query a location table which contains the dbnames and using cursor and one dynamic sql to pull the sales data into a collective table. I got the rest going except the dynamic sql. Here is the dynamic sql:

CREATE PROCEDURE SalesReport_sp
(
@Storeid int,
@BegDt datetime,
@EndDt datetime,
@dbNme varchar(20)
)

AS
DECLARE @SQLCmd
SET @SQLCmd = 'SELECT '+ @Storeid + ' ItemId, DailySaleAmt, QuantityCnt FROM ' + quotename(@dbName) + '.dbo.DailySales_tbl WHERE TranDate > ' + @BegDt ' AND TranDate <= ' + @EndDt + ' ORDER BY TranDate'

INSERT INTO Corpdb.dbo.DlySales_tbl
EXEC sp_EXECUTESQL @SQLCmd, N'@Storeid int', @Storeid

I couldn't get the BegDt and EndDt working.
Thanks for helping.

 

[simian336]

SET @SQLCmd = 'SELECT '+ @Storeid + ' ItemId, DailySaleAmt, QuantityCnt FROM ' + quotename(@dbName) + '.dbo.DailySales_tbl WHERE TranDate > ' + @BegDt ' AND TranDate <= ' + @EndDt + ' ORDER BY TranDate'

In order to do this @Storeid would need to be the name of a field

SET @SQLCmd = 'SELECT '+ @Storeid+ ', ItemId...'

if I understand what your doing.

Simi

 

[simian336]

it would also help to print

Print @SQLCmd

Prior to execution... So you can see what will be processed.

Simi

 

[OldSlowDog]

Because I am pulling data off each db into a collective table that's why I use a new field 'Storeid' to identify which store is which.

 

[OldSlowDog]

My problem is don't knw how to deal with the BegDt and EndDt.
Should I use CAST or CONVERT?
How do I construct the SET @SQLCmd line for it to work with the passing values?
How do I construct the EXEC sp_EXECUTESQL @SQLCmd line so that values will pass properly?

Thanks.

 

[gmmastros]

Clearly, this is a follow up to an earlier question you asked.

The SQL Server setup:
XYZEnterprise(SQL Server 9.0.4035)
DataBases
Phoenix
Dallas
SaltLake
SanAntonio

It appears as though you have several databases that you want to report on. The report(s) should combine the data from several DB's. I have an idea that I think you should at least consider...

My suggestion is to create another database on your server specifically for generating reports. This database wouldn't have any data, in fact... it wouldn't even have any tables. Instead, it would have views. These views could be named that same as the tables in the separate database and have the same columns as the tables in the other databases. No doubt.... there would be some setup work involved in creating the views, but you could probably automate a lot of the setup work too.

Here's my suggestion...

Create Database Reports

Use Reports

Create View DailySales_tbl
As
Select 'Phoenix' As DB_Name, *
From   Phoenix.dbo.DailySales_tbl

UNION ALL

Select 'Dallas' As DB_Name, *
From   Dallas.dbo.DailySales_tbl

UNION ALL

Select 'SaltLake' As DB_Name, *
From   SaltLake.dbo.DailySales_tbl

UNION ALL

Select 'SanAntonio' As DB_Name, *
From   SanAntonio.dbo.DailySales_tbl

Now, if you want to see all of the data from the table...

Select *
From   DailySales_tbl

Notice that there is an additional column for database name. This allows you to use that information for filtering purposes.

Select *
From   DailySales_tbl
Where  DB_Name = 'Dallas'

The only drawback with this approach occurs when you add more databases that you want to include in your reports. When this happens, you'll need to modify each view that you have created. The modification won't be terribly difficult, but there may be a lot of them.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

This will create a new table each time. Just drop it after you get done with it.

SET @SQLCmd = 'SELECT Storeid, ItemId, DailySaleAmt, QuantityCnt '
SET @SQLCmd = @SQLCmd + 'INTO Corpdb.dbo.DlySales_tbl'
SET @SQLCmd = @SQLCmd + ' FROM ' + quotename(@dbName) + '.dbo.DailySales_tbl'
SET @SQLCmd = @SQLCmd + ' WHERE TranDate > ' + @BegDt ' AND TranDate <= ' + @EndDt
SET @SQLCmd = @SQLCmd + ' ORDER BY TranDate'

Print @SQLCmd

Exec @SQLCmd


Your dates should be fine.

Simi

 

[OldSlowDog]

Hello gmmastros and simiana336,
Yes, it was a follow up of my previous question.

I like all these ideas.
I am going to try them and get back with you all.

Thank you all for helping.

 

[donutman]

I really like the idea presented by gmmastros. It makes it easy to create reports that might include only a subset of all stores (with a simple inner join to a temporary Store-Report table).
I would tweak his idea by just creating a reporting table in the Corpdb database for the actual reporting. Create a stored procedure that deletes the contents of the reporting table and then goes on to insert all of the rows from the code that gmmastros has presented except using a series of Inserts instead of Unions. Of course you could do this with an in-memory temporary table, but that’s just being anal.
If you add a new store it’s a no brainer to update the stored procedure. Generally views are not considered very efficient. A cursor and/or dynamic SQL is a real no-no. You wouldn't need another database either.
Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]