May 3, 2009 at 7:55 pm
I have MS SQL Server 2008
Requirement: Need ability to handle different database names in SQL statements. (A third party software vendor allows clients to create application databases with their name as the database name).
Result: The requirement causes me to use the EXECUTE command to make the SQL dynamic.
Problem: However the EXECUTE causes a problem of variable scope since the variables in the EXECUTE are not in the same scope as the outer code in the stored procedure.
Possible Solution: I can use a temp table to solve this (see below), but was wondering if this is the best way.
Suggestions?
The example code works below:
ALTER PROCEDURE [dbo].[spPullData]
( @OtherDBNameParam varchar(25),
@StatusCode int OUTPUT,
@StatusMsg varchar(250) OUTPUT)
AS
DECLARE @MaxNum INT
BEGIN
CREATE TABLE #TEST
(MaxCountNum INT,
CountID UNIQUEIDENTIFIER,
WarehouseName VARCHAR(50),
StatusCode INT,
StatusMsg varchar(250))
-- Picks the latest unposted physical count batch number
EXECUTE ('
DECLARE @MaxCountNum INT,
@CountID UNIQUEIDENTIFIER,
@WarehouseName VARCHAR(50),
@PhysCountRows INT,
@StatusCode int,
@StatusMsg varchar(250)
SET @StatusCode = 0
SET @StatusMsg = ''OK''
SELECT @MaxCountNum = MAX(pc.CountNumber)
FROM ' + @OtherDBNameParam + '.dbo.PhysicalCount pc (nolock)
WHERE pc.IsPosted = 0
SET @PhysCountRows = @@ROWCOUNT
IF (@PhysCountRows = 0)
BEGIN
SET @StatusCode = 1
SET @StatusMsg = ''No unposted physical counts.''
END
ELSE
BEGIN
SELECT @CountID = pc.CountID, @WarehouseName = w.WarehouseName
FROM ' + @OtherDBNameParam + '.dbo.PhysicalCount pc
INNER JOIN ' + @OtherDBNameParam + '.dbo.Warehouse w ON pc.WarehouseId = w.WarehouseId
WHERE pc.CountNumber = @MaxCountNum
END
INSERT INTO #TEST
(MaxCountNum, CountID, WarehouseName, StatusCode, StatusMsg)
SELECT @MaxCountNum, @CountID, @WarehouseName, @StatusCode, @StatusMsg ')
SELECT @MaxNum = MaxCountNum,
@StatusCode = StatusCode,
@StatusMsg = StatusMsg
FROM #TEST
PRINT @MaxNum
PRINT @StatusCode
DROP TABLE #TEST
END
May 4, 2009 at 2:10 pm
Thought of another way to do it. Could build a dynamic USE command changing the implicit database and since my database name is static, I could reduce the number of dynamic EXECUTE command so I do not have the scope issue as bad.
May 4, 2009 at 2:51 pm
Use sp_ExecuteSQL passing and returning parameters, instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply