How to parse comma delimited string into IN Clause in SQL Server

Hi Folks,

Today i was trying to pass multi-value parameter into my select query in SQL Server. To accomplish this i stored the multi-value parameter in a comma delimited string. Then, i want to pass it in my IN clause of the select statement. here is an example to what i want to accomplish:

Delcare @myVar varchar(500)
Set @myVar=’1,2,3,4,5,7′

Select * from Employee
where EmployeeId IN (@myVar)

You will get this error:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value ‘1,2,3,4,5,7’ to data type int.


It makes sense, because i have a varchar that holds all my values and i want to pass those in the IN clause that only accepts integer values!.

The solution for this problem is to parse this string into set of integers and pass it back to your query in your IN clause. For this reason, i have created a function that return a table of 1 column of type int. This will be passed back to the select statement i have mentioned above.

The function code to parse comma delimited string into set of integers:

– =============================================
– Author: Mostafa Elzoghbi
– Create date: 12/10/2012
– Description: Parse a string into set of numbers
– =============================================

CREATE FUNCTION [dbo].[ParseCommaDelimitedString]
( @CommaSeparatedStr
nvarchar(1000) =NULL)
RETURNS @myTable TABLE ([Id] [int] NOT NULL)
AS
BEGIN
declare
@pos int
declare @piece varchar(500)
– Need to tack a delimiter onto the end of the input string if one doesn’t exist
if right(rtrim(@CommaSeparatedStr ),1) <> ‘,’
set @CommaSeparatedStr = @CommaSeparatedStr + ‘,’
set @pos = patindex(‘%,%’ , @CommaSeparatedStr )
while @pos <> 0
begin
set @piece = left(@CommaSeparatedStr , @pos - 1)
– You have a piece of data, so insert it, print it, do whatever you want to with it.
insert @myTable
select @piece

set @CommaSeparatedStr = stuff(@CommaSeparatedStr , 1, @pos, )
set @pos = patindex(‘%,%’ , @CommaSeparatedStr )
end
RETURN
END
– =============================================

What you need after that to re-write your query as follows:

Select *

from Employee
where EmployeeId IN
( select * from ParseCommaDelimitedString(@myVar))

Now, you will be able to pass any comma delimited string, varchar,nvarchar to this function and it returns table of integer that you can set in the IN clause of you select statement or any other T-SQL statements you works with your logic.

Hope this helps.

–ME

Error: unable to retrieve column information from the data source in SSIS

Hi Folks,

I was trying to call a stored procedure from OLE DB Source in SSIS. and i getting this error:

“Error: unable to retrieve column information from the data source”

I wasn’t even able to get the columns returned from this stored procedure.

My stored procedure is having a dynamic query i’m building based on passed parameters from SSIS package.

My Stored procedure code:

Declare @sqlStatement as nvarchar(1000)

SET @sqlStatement = ‘ SELECT * FROM dbo.myTableWHERE ID IN (‘

+ @Ids + ‘ )’
– For tracing purposesprint @sqlStatement

– EXEC @sqlStatementexecute sp_executesql @sqlStatement



To fix this problem, the SSIS when it executes the stored procedure in the design time, it doesn’t pass any parameters, with that being said, you have to make sure that your stored procedure is working when you pass NULL values by default. and this was the trick to fix the problem. In my case, when i pass NULL values for my parameter that contains multiple values my SQL query statement is not valid!



I fixed my stored procedure through the following:



Declare @sqlStatement as nvarchar(1000)IF @Ids is not NULLSET @sqlStatement = ‘ SELECT * FROM myTable WHERE ID IN (‘ + @Ids + ‘ )’ELSESET @sqlStatement = ‘ SELECT *FROM myTable’


– For tracing purposesprint @sqlStatement

– EXEC @sqlStatementexecute sp_executesql @sqlStatement

After fixing my stored procedure to work with null passed values and my dynamic sql statement is correct i was able to view my columns returned from the stored procedure and it works like a charm!



Hope this tip helps you when you create any stored procedure that is being called from SSIS objects such as OLE DB source or SQL Task.



Thanks,

–ME

Study Material for Programming HTML5 and CSS 3 Microsoft Exam

Hi Folks,

I got a free voucher from Microsoft for programming HTML 5 and CSS 3 Exam. I studied and reviewed online video materials and i want to share with you my study notes and hope this will be helpful for passing the exam. Congratulations in advance for everyone!.

My Notes:

  1. Use accepts:’application/bin,text/xml’ to accepts only XML and binary content in HTML responses.
  2. Use the following condition to check if the html response content is binary: If(request.getResponseHeader(“Content-Type”)==”application/bint”
  3. To show the status of upload is displayed in the progress bar:Xhr.upload.onprogress=
  4. To support 2 way communication between a web page and a worker role, implement the following:
    1. From the main page, use onmessage event handler of the web worker to capture events.
    2. From the web worker, use the onmessage event handler of the main page to capture events.
  5. Standard-complaint screen readers must be able to identify the links contained within the navigation structure automatically: Use semantic markup.
  6. Use appendChild to add a control in DOM document.
  7. To extend a class with a method you should use prototype keyword as follows: Customer.prototype.GetCommission() = funct() {…}
  8. If you want an input control to allow only numeric values, use the following:    type=”number”
  9. To pass objects between web page and web worker you can use: JSON,String and JavaScript types.
  10. To register an event listener for the web worker use addEventListenr and to stop a web worker use: self.close();
  11. Anchor selector order:  a:link –> a:visted –> a:hover –> a:active
  12. Use Figure semantic markup to include image and its caption using figcaption markup.
  13. To store user’s information you can use: localStorage to get or set  user’s data.
  14. To write a code to throw an error: throw new Error(“Invalid”,200)
  15. To show @ in the email address for a submitted form,   Use: str=$(“form”).serialize() ;         str=decodeUriComeponent(str);
  16. Apply styles from highest to lowest priority: 
    1. User agent style sheets
    2. Author normal style sheets
    3. Author important style hseets
    4. User normal style sheets
    5. User important style sheets
  17. To make sure that the advertisement section on the right most of the page use:
  18. To handle automatically each time the request status change use xhr.onreadstatechange
  19. Catch specific error number by using e.number and not e.message
  20. To use text-transform in CSS, you should use captalize  semantic tag.
  21. You need to group page content together to maximize search engine readability use article semantic markup.
  22. When  you want the inner paragraph exactly far from the outer paragraph, then you should use Relative.
  23. To show JSON confirmation number in a label: $(“#txtValue”).text(JSONObject.Confirmation);
  24. To show text around an image is showing in the center of the page use: -ms-wrap-side: both
  25. Use header to apply css or classes to different heading markup such as: H1,H2
**Videos – Jump start from Microsoft Virtual academy:

https://www.microsoftvirtualacademy.com/tracks/developing-html5-apps-jump-start

Good Luck, Feel free to drop a line with any feedback you would like to add.



Functions vs. Methods in JavaScript with Encapsulation!

Hi Folks,

I found something interesting to share with all of you in Javascript. Functions in javascript as we all know is defined with function keyword while Method in Javascript is a function but can have a scope and you can hide it as we do in object oriented programming. I will be showing 2 examples that implement function versus method in your web page to add 2 numbers.

1) Function to add 2 numbers in Javascript:

In your script tag, this is the declaration of the function:

<script language=javascript >
function myfunction() {
document.getElementById(‘txtResult’).value = parseInt(document.getElementById(‘Text1′).value) +                                             parseInt(document.getElementById(‘Text2′).value);
</script>

My page, see the result after you change the second text box:

<p>Using Functions</p><input type=”text” id=”Text1″ /><br /><input type=”text” id=”Text2″ onchange=”myfunction();” /><br /><input type=”text” id=”txtResult” /><p>

2) Method to add 2 numbers using Methods:

<script language=javascript >
// Methods
var ops = {

add: function AddTwoNumbers() {
                              document.getElementById(‘txtResult2′).value =
                              parseInt(document.getElementById(‘Text3′).value) +
                                 parseInt(document.getElementById(‘Text4′).value);               
                                               }
         };</script>

My page:

<p>Using Methods</p><input type=”text” id=”Text3″ /><br /><input type=”text” id=”Text4″ onchange=”ops.add();” /><br /><input type=”text” id=”txtResult2″ />

You will see that we implemented the add function that has the scope within ops object, and in this way you can design and organize your functions within different scopes and with that being said you implement encapsulation for your methods!.

Anoter note, you gave an alias to your function with “add” alias name and this is not the actual name of the method since if you try to call the method as AddTwoNumbers you won’t be able to do that.

The running application:



Hope this helps!

Asynchronous Programming in C# – async/await


Asynchronous Programming in C# – async/wait

Hi Folks,

I want to share with you an important enhancement in .NET framework 4.5. The asynchronous programming paradigm for .NET developers using new C# keywords async/await. Since I’m C# developer; I will be highlighting this new design pattern keywords in C# for asynchronous programming which is async/await which is supported only starting from .NET framework 4.5.

what’s async/await for C#?

Async is a new modifier in C# 4.5. async specifies to the compiler that the method is executing in asynchronous mode and not synchronous.

For example – C# Code:

async Task Sum(int x, int y) {
// your method implementation.
}

The async method returns a Task object or doesn’t return any objects and this case you will write void instead of Task object.

The async method doesn’t allow input parameters by reference or output.

The  async method has to contain a line to call the async method by using await operator.

For example – C# Code:
private async void btnAdd_Click(object sender, RoutedEventArgs e)
{
     int x=10,y=15,z=0;
    // The complier will call Sum asynchronously and return the value in z.
    z=await Sum(x,y);
 }


An async method provides a convenient way to perform potentially long-running processing without blocking the caller’s thread.

The caller of an async method can resume its work without waiting for the async method to finish which makes your application more responsive and user friendly.


Read more about async/await from MSDN:
http://msdn.microsoft.com/en-us/library/hh156513(v=vs.110).aspx
http://msdn.microsoft.com/en-us/library/hh156528.aspx

Hope this helps.

-ME