Home > SSRS Expert > SSRS #36 – SQL Command Type StoredProcedure‏

SSRS #36 – SQL Command Type StoredProcedure‏

Did you ever wonder why we sometimes need to use the StoredProcedure for our SQL Command Type. Why can we just type this and choose Text as the SL Command Type?

execute myProcedure param1, param2

clip_image002

There is a good reason why we do prefer using StoredProcedure as our SQL Command Type over simply typing the execute command as plan Text.

You just simply type the name of the procedure when you choose StoredProcedure as the SQL Command Type.

clip_image004

You will need to type the execute command and also provide the parameters if you choose Text.

clip_image006

If you choose StoredProcedure, here is where your procedure parameters will go.

On teh Parameters tab, you will provide them. Under Name, you will type the exact parameter names from your procedure; Under Value you get a drop down list so you can pick the correct report parameter to feed your procedure.

clip_image008

Here is the good reason why we sometimes prefer StoredProcedure over Text. The report parameter SUP_CCRID is defined as a Multi-value parameter, and also it comes from a SQL query. When we pass a Multi-value report parameter to a stored procedure, Text will not do. If you ever tried that, you will get an error that just doesn’t tell you much.

clip_image010

Of cause in your stored procedure will need to write so that it can take a long string of text, and also be able to turn long string of text, separated by comma,  into rows. See my blog Using Multi-value Parameters in SSRS Report on how to do that.

Categories: SSRS Expert
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: