Tech-Today

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'

- How To Change Varchar To Nvarchar In Mssql Using Hibernate
In my recent assignment I was asked to change how hibernate creates a table changing varchar fields to nvarchar. The problem is in MSSQL we need an nvarchar to support UTF-8 characters. We are actually using a JDBC MSSQL driver available here: http://technet.microsoft.com/en-us/library/ms378422.aspx...

- How To Execute A Group By And Sum Query In Entity Framework
The following code is the native sql with the converted code in entity framework. It queries the sum of quantity group by branch, model, year and week no. //native sql select weekyear, weekno, branchid, modelid, sum(quantity) from selloutmobiles where...

- Calling Mssql's Aspnet_membership_createuser Stored Procedure
Here's how you would want to call mssql's aspnet_Membership_CreateUser method: DECLARE @return_value int, @UserId uniqueidentifier, @nowUtc datetime, @now datetime set @nowUtc = getutcdate() set @now = getdate() EXEC @return_value = [dbo].[aspnet_Membership_CreateUser]...

- Insert An Md5 Password In Mssql
To md5 a string in mssql we execute this query: select HASHBYTES('MD5', 'password') //But this has a problem because it returns a VarBinary data type which is a garbled text. So it should be converted first to NVarchar: SELECT CONVERT(NVARCHAR(32),HashBytes('MD5',...

- Mssql Get The Date From Datetime
...

Tech-Today