CODEWITHSUNDEEP
sqlstored-proceduresparameter-passingdynamic-sql
Colster
Colster

Reputation: 81

Using value from a paramterised query within a stored procedure that has a variable table name

I have a stored procedure that is called to establish sequence numbers for a specific client account. A simple table stores the last issued value for all clients and the SP will just take a parameter of SupplyChainID and how many numbers are required.

GetItemIDs

So SupplyChainID 12345 might have 5 as its last issued number, so running

GetItemIDs 12345,200

would return 5 and then set the value to 205 for next time

So the current SP does something like this as it has to check it does not go beyond 99,999,999. (goes back to 0 if it would go over)

IF (SELECT EIBItemID FROM ItemIDGeneration WHERE SupplyChainID = @SCID) + @DocumentCount > 99999999
    BEGIN
        SELECT 0
        UPDATE dbo.ItemIDGeneration SET EIBItemID = @DocumentCount,
                                        LastIssuedDate = getdate() WHERE SupplyChainID = @SCID      
END
ELSE
    BEGIN
        SELECT EIBItemID FROM ItemIDGeneration WHERE SupplyChainID = @SCID  
        UPDATE dbo.ItemIDGeneration SET EIBItemID = EIBItemID + @DocumentCount,
                                        LastIssuedDate = getdate() WHERE SupplyChainID = @SCID
END

What I need to add is a check of another table to see if that has a higher value and in which case use that one instead of the last issued number from the ItemGeneration table

Something like this:

 SELECT @HI_ID = MAX(EIBItemID) from 
 (SELECT EIBItemID FROM ItemIDGeneration WHERE SupplyChainID = @SCID 
 UNION ALL 
 SELECT ISNULL(MAX(EIBItemID),0) AS EIBItemID FROM dbo.SupplyChainID_'+ @tablename) as bigint 
    IF (@HI_ID) + @DocumentCount > 99999999
        BEGIN
            SELECT 0
            UPDATE dbo.ItemIDGeneration SET EIBItemID=@DocumentCount,LastIssuedDate = getdate() WHERE SupplyChainID = @SCID         END
    ELSE
            BEGIN
        SELECT @HI_ID--EIBItemID FROM ItemIDGeneration WHERE SupplyChainID = @SCID  
        UPDATE dbo.ItemIDGeneration SET EIBItemID = @HI_ID +@DocumentCount,LastIssuedDate = getdate() WHERE SupplyChainID = @SCID
    END

As you can see the table has the same SupplyChainID as part of the table name so I know I need to pass it in as part of a query and exec it but I can't get the following to work

 SET @tablename = 'dbo.SupplyChain_'+@SCID
 SET @tablequery = N'SELECT ISNULL(MAX(EIBItemID),0) AS EIBItemID FROM ' + @tablename

 execute  @tableID = sp_executesql @tablequery

 SELECT @HI_ID =    MAX(EIBItemID) from 
 (SELECT EIBItemID FROM ItemIDGeneration WHERE SupplyChainID = @SCID 
 union all 
 SELECT @tableID) as bigint 
IF (@HI_ID) + @DocumentCount > 99999999

The process doesn't error but I think I have something wrong in my syntax as when I run the following

  declare @return int
    exec @return = GetItemIDs 8001377,20
    select @return as result

I see EIBItemID returned as the correct result from the dbo.SupplyChain_xxxxx table but the return value from the end of the sp is the reply based on the ItemIDGeneration table (returns 200).

I think it's visibly returning the correct result when the exec is called but storing 0 in @tableid

Upvotes: 0

Views: 60

Answers (2)

Nițu Alexandru
Nițu Alexandru

Reputation: 714

You can try this to get the value:

DECLARE @SCID INT = 1
DECLARE @TableName NVARCHAR(100) = N'dbo.SupplyChain_' + CAST(@SCID AS NVARCHAR(10))
DECLARE @TableID BIGINT
DECLARE @TableQuery NVARCHAR(1000) = N'SELECT @TableID = ISNULL(MAX(EIBItemID), 0) FROM ' + @TableName

EXEC sp_ExecuteSql  @TableQuery, N'@TableID BIGINT OUTPUT', @TableID OUTPUT

PRINT @TableID

Upvotes: 1

Colster
Colster

Reputation: 81

Thanks to all that answered, I used Alex's suggestion with a small tweak

DECLARE @HI_ID as bigint
DECLARE @TableName NVARCHAR(100) = N'dbo.SupplyChain_' + CAST(@SCID AS NVARCHAR(10))
DECLARE @TableID BIGINT
DECLARE @TableQuery NVARCHAR(1000) = N'SELECT @TableID = ISNULL(MAX(EIBItemID),0) FROM ' + @TableName

EXEC sp_ExecuteSql  @TableQuery, N'@TableID BIGINT OUTPUT', @TableID OUTPUT

SELECT @TableID As EIBItemID

Upvotes: 0

Related Questions