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)
DECLARE @sqlStatement NVARCHAR(4000), @paramDefinition NVARCHAR(100)

SET @sqlStatement = '
SELECT b.Dealer, d.Branch, c.Qty, c.Amount
FROM Dealer b
 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

This one is more challenging, pass an array of strings:
ALTER PROCEDURE spGetWeddingLookup(@groupName VARCHAR(50), @value VARCHAR(100)) 
 DECLARE @query as nvarchar(500) 
 set @value = '''' + replace(@value,',',''',''') + '''' 
 SET @query = 'SELECT Name FROM weddinglookups WHERE GroupName='''+@groupName+''' AND VALUE IN ('+@value+')'
//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.