EXECUTE(@STRING) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

EXECUTE(@STRING)

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-03-22 : 12:02:38

I have the following fragment from a sproc. The actual stored procedure has multiple inserts and updates. The stored procedure is in an enterprise database where many of my sprocs for reports are. I want to be able to determine which database the sproc uses to pull it's data from, based on the @company parameter which is passed to the stored procedure.

SET NOCOUNT ON

DECLARE @CSTRING VARCHAR(55)

SET @CSTRING = (SELECT COMPANY1 =
CASE
WHEN @COMPANY = 55 THEN 'USE DATA_055'
WHEN @COMPANY = 61 THEN 'USE DATA_061'
WHEN @COMPANY = 2 THEN 'USE DATA_002'
END)
--
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '#IMBALANCE')
DROP TABLE #IMBALANCE
--
CREATE TABLE [#IMBALANCE] (
[ITEM_NO] [CHAR] (18) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NOT NULL ,
[ACTIVITY] [MONEY] NULL ,
[CALCULATED_BALANCE] [MONEY] NULL ,
[BEG_VALUE] [MONEY] NULL ,
[CURRENT_VALUE] [MONEY] NULL ,
[PROBLEMS] [MONEY] NULL ,
[IMBALANCE_ID] [INT] IDENTITY (1, 1) NOT NULL ,
[LOC] [CHAR] (3) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NULL ,
[RACTIVITY] [MONEY] NULL ,
[IACTIVITY] [MONEY] NULL ,
CONSTRAINT [PK___IMBALANCE__73C6B429] PRIMARY KEY CLUSTERED
(
[IMBALANCE_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
--
INSERT INTO #IMBALANCE
(ITEM_NO,BEG_VALUE)

/* Would like to insert "Execute(@CSTRING)" Here to set the database I use to pull the data*/

SELECT ITEM_NO,SUM(QUANTITY * UNIT_COST) AS Q1
FROM IMINVTRX_SQL

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-22 : 12:24:27

[code]
SET @CSTRING = (SELECT COMPANY1 =
CASE
WHEN @COMPANY = 55 THEN 'DATA_055'
WHEN @COMPANY = 61 THEN 'DATA_061'
WHEN @COMPANY = 2 THEN 'DATA_002'
END)

IF @CString IS NULL
BEGIN
-- Error Handle
Return -1
END

DECLARE @sql varchar(8000)

SELECT @sql = 'SELECT ITEM_NO,SUM(QUANTITY * UNIT_COST) AS Q1 FROM '
+ @Cstring + '..IMINVTRX_SQL'

EXEC(@sql)

-- Better yet

IF @CString IS NULL
BEGIN
-- Error Handle
Return -1
END
IF @COMPANY = 55
SELECT ITEM_NO,SUM(QUANTITY * UNIT_COST) AS Q1
FROM DATA_055..IMINVTRX_SQL
IF @COMPANY = 61
SELECT ITEM_NO,SUM(QUANTITY * UNIT_COST) AS Q1
FROM DATA_061..IMINVTRX_SQL
IF @COMPANY = 2
SELECT ITEM_NO,SUM(QUANTITY * UNIT_COST) AS Q1
FROM DATA_002..IMINVTRX_SQL

[/code]

Brett

8-)

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-03-22 : 15:42:37

Thanks, I thought about doing it the second way you showed, and I will do it that way, but the full query has several update, selects, and inserts using those same sets of tables, so I will have probably 200 lines of sproc.

Again I appreciate the help.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-22 : 16:21:14

Absolutley...

Create a driver sproc...do the IF conditions once and call 1 of three identical sprocs (execept for the db name of course)

Just create the first one, save it, then clone it, replace all, save, replace all save...done....

what'choo think?

Brett

8-)

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-03-23 : 09:20:30

Yes that sounds like a plan. Thanks Again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources