Skip to main content

How do I get result of dynamic SQL into a variable?


DECLARE @SQLQuery VARCHAR(4000)
SET @SQLQuery = 'SELECT @Query_Result_Count = Count(Column_1) FROM table_A'
DECLARE @Outer_Variable AS INT
EXEC Sp_executesql
@query = @SQLQuery,
@params = N'@Query_Result_Count INT OUTPUT',
@Query_Result_Count = @Outer_Variable OUTPUT
If you can see, we have used “@Query_Result_Count” variable into @SQLQuery which is not declared anywhere before into the declared variable script. This is because we are passing this variable as a parameter into SP_ExecuteSQL statement the same way we are executing the stored procedure. You can check this with “@params = N’@Query_Result_Count INT OUTPUT’,” line of statement.
You can get the output of any dynamic SQL into your local SQL variable by declaring that variable first like we did “DECLARE @Outer_Variable AS INT” and then get this variable filled with the values return by executing the SP_ExecuteSQL statement this way “@Query_Result_Count = @Outer_Variable OUTPUT”. Here “@Query_Result_Count” is the variable that we are passing and using into dynamic SQL.
You can also pass multiple parameters to Sp_ExecuteSQL this way,
@params = N'@Para1 INT, @Para2 Varchar(200), @Query_Result_Count INT OUTPUT '
Also you Need to provide values for input parameters like @Para1, and @Para2 by declaring an outer variable or any static values this way:

DECLARE @Outer_Para1 INT
SET @Outer_Para1 = 10
DECLARE @Outer_Para2 VARCHAR(200)
SET @Outer_Para2 = 'Test'
EXEC Sp_executesql
@query = 'SELECT @Query_Result_Count = Count(Column_1) FROM table_A WHERE Column_2 = @Para1 AND Column_3 = @Para2 ',
@params = N'@Para1 INT, @Para2 Varchar(200), @Query_Result_Count INT OUTPUT',
@Para1 = @Outer_Para1 , @Para2 = @Outer_Para2, @Query_Result_Count = @Outer_Variable OUTPUT

Comments

Post a Comment

Popular posts from this blog

ASP.NET MVC - Set custom IIdentity or IPrincipal

Here's how I do it. I decided to use IPrincipal instead of IIdentity because it means I don't have to implement both IIdentity and IPrincipal. Create the interface interface ICustomPrincipal : IPrincipal { int UserId { get ; set ; } string FirstName { get ; set ; } string LastName { get ; set ; } } CustomPrincipal public class CustomPrincipal : ICustomPrincipal { public IIdentity Identity { get ; private set ; } public bool IsInRole ( string role ) { return false ; } public CustomPrincipal ( string email ) { this . Identity = new GenericIdentity ( email ); } public int UserId { get ; set ; } public string FirstName { get ; set ; } public string LastName { get ; set ; } } CustomPrincipalSerializeModel - for serializing custom information into userdata field in FormsAuthenticationTicket object. public class CustomPrincipalSerializeMode...
8 comments
Read more

Validate credit card number with Mod 10 algorithm in C#

Introduction All you know what information contains in your NIC number. But do you know what information contains in the Credit Card Number? Following article provides brief details about what information contain in your credit card and demonstrates to how to validate credit card number using mod 10 (Luhn) algorithms with C#. Background  Card Length   Typically, credit card numbers are all numeric and the length of the credit card number is between 12 digits to 19 digits.  14, 15, 16 digits – Diners Club 15 digits – American Express 13, 16 digits – Visa 16 digits - MasterCard   For more information please refer  http://en.wikipedia.org/wiki/Bank_card_number . Hidden information  Major Industry Identifier (MII)   The first digit of the credit card number is the Major Industry Identifier (MII). It designates the category of the entry which issued the card.     1 and 2 – Airlin...
1 comment
Read more

Web Services Description Language Tool (Wsdl.exe)

Ref:  https://msdn.microsoft.com/en-us/library/7h3ystb6(VS.80).aspx The Web Services Description Language tool generates code for XML Web services and XML Web service clients from WSDL contract files, XSD schemas, and .discomap discovery documents. wsdl [options] {URL | path} Argument Description URL The URL to a WSDL contract file (.wsdl), XSD schema file (.xsd), or discovery document (.disco). Note that you cannot specify a URL to a .discomap discovery document. Path The path to a local WSDL contract file (.wsdl), XSD schema file (.xsd), or discovery document (.disco or .discomap). Option Description /appsettingurlkey: key or /urlkey: key Specifies the configuration key to use in order to read the default value for the URL property when generating code. When using the   /parameters   option, this value is the   <appSettingUrlKey>   element and contains a string. /appsettingbaseurl: baseurl or /baseurl:...
Post a Comment
Read more