Passing an array of objects to mssql stored procedure

Oftentimes you need to pass an array of objects (could be ids, types, etc) in an mssql stored procedure that you either need to insert into a table or use as filter. The following codes will explain the latter:
Pass an array of integers:
ALTER PROCEDURE [dbo].[GetBranchReport]
(
 @startDate DATETIME,
 @endDate DATETIME,
 @dealer INT,
 @branches VARCHAR(2000)
) 
AS
BEGIN 
DECLARE @sqlStatement NVARCHAR(4000), @paramDefinition NVARCHAR(100)

SET @sqlStatement = '
SELECT b.Dealer, d.Branch, c.Qty, c.Amount
FROM Dealer b
JOIN (
 SELECT a.ClientId, a.BranchId, SUM(a.Quantity) AS Qty, SUM(a.InvoiceAmount) as Amount
 FROM SellOutMobile a
 WHERE (a.DateCreated BETWEEN CONVERT(DATETIME, @startDateLocal, 101) AND CONVERT(DATETIME, @endDateLocal, 101))
 AND a.ClientId=@dealerLocal AND a.BranchId IN ('+@branches+')
 GROUP BY a.ClientId, a.BranchId
 ) as c on b.DealerId=c.ClientId
join Branch d on c.BranchId=d.BranchId
ORDER BY b.Dealer, d.BranchId'

SET @paramDefinition = '@dealerLocal INT, @startDateLocal DATETIME, @endDateLocal DATETIME'

EXECUTE sp_executesql @sqlStatement, @paramDefinition, @dealer, @startDate, @endDate

END
This one is more challenging, pass an array of strings:
ALTER PROCEDURE spGetWeddingLookup(@groupName VARCHAR(50), @value VARCHAR(100)) 
AS 
BEGIN 
 DECLARE @query as nvarchar(500) 
 set @value = '''' + replace(@value,',',''',''') + '''' 
 SET @query = 'SELECT Name FROM weddinglookups WHERE GroupName='''+@groupName+''' AND VALUE IN ('+@value+')'
 EXEC(@query)
END
//Which you can invoke in MSSQL Management Studio as:
execute spGetWeddingLookup 'GroupName', 'Code1,Code2'
Passing an array of objects to mssql stored procedure Passing an array of objects to mssql stored procedure Reviewed by Edward Legaspi on Saturday, November 12, 2011 Rating: 5

No comments:

Powered by Blogger.