Does Altace Have Sugar In It

output parameters in a dynamic T-SQL

In a stored procedure in a first step, two variables are set that are required for further processing. This could look like this:

SELECT @ var1 = var1, @ var2 = var2
FROM MyTable WHERE ID = @ ID


Now the statement but must be compiled on the fly and the Select-String to run it. This could look like this:

SET @ query = 'SELECT @ var1 =
Var1, @ Var2 = var2
FROM MyTable WHERE ID =
' + CAST (@ ID as CHAR (15))
EXEC (@ query)

In my case the statement is executed on a host system and the call must be made through Open Query. This could look like this:

SET @ query = 'SELECT @ var1 =
Var1, @ Var2 = var2
FROM OPENQUERY (mysystem,''SELECT var1, var2 FROM MyTable WHERE ID = '+
dbo.MyTypeConverter (@ id) +''')'
EXEC (@ query)

very clear that the two variables thus no longer be bottled, but give an error. How can they be enforced?
According to a first idea was using a variable of type TABLE and added to the open query results with INSERT INTO. Then use the first select statement in order to set the variables. Works! Is not very elegant.
's prettier like this: SET @ params =

'@ var1 int OUTPUT, @ var2 nvarchar (max) OUTPUT' SET @
query = 'SELECT @
var1Out = Var1, @ Var2 = var2Out
FROM OPENQUERY (mysystem, SELECT var1, var2 FROM MyTable '
dbo.MyTypeConverter + (@ id) EXEC sp_executesql +''')'
@ query, @ params, @ = @ var1 var1Out OUTPUT, @ var2 = @ OUTPUT var2Out

This approach to the Procedure sp_executesql system offers additional advantages in the SQL Server Help can be found.

Nipples Pierced Las Vegas

Dynamic Method performance

In an interesting article in MSDN Magazine addressed to different performance problems, in particular in connection with Reflection. I am particularly interested in the acclaimed dynamic performance of the methods available for the. NET Framework version 2.0. A simple test application showed that the article is right.
In the test application is an object, a string assigned to a million times. This first wired directly, then with dynamic method and modified via Reflection. The result is clear. The direct-wired method is of course the fastest, the dynamic method takes twice as long for it (which I still find very good) and means of reflection, it takes 100 times longer. What a gain!
the way, the Assembly I have studied with Lutz's Reflector to ensure that the loop will be in practice for all three variants.

And here's the code: public class


Program {private delegate void
DemoDelegate (Foo arg);

static void Main(string[] args)
{
Stopwatch stopwatch = new Stopwatch();

Foo f = new Foo();
stopwatch.Start();
for (int i = 0; i < 1000000; i++)
{
f.Text = "Hello World!";
}
stopwatch.Stop();
Console.WriteLine("Direct: {0}", stopwatch.Elapsed);

stopwatch.Reset();

DemoDelegate del = createMethod();
stopwatch.Start();
for (int i = 0; i < 1000000; i++)
{
del(f);
}
stopwatch.Stop();

Console.WriteLine("Dynamic method: {0}", stopwatch.Elapsed);

stopwatch.Reset();

PropertyInfo property = f.GetType().GetProperty("Text");
stopwatch.Start();
for (int i = 0; i < 1000000; i++)
{
property.SetValue(f, "Hello World!", null);
}
stopwatch.Stop();
Console.WriteLine("Reflection: {0}", stopwatch.Elapsed);


Console.ReadKey();
}

private static DemoDelegate createMethod()
{
Type[] parameterTypes = new Type[] { typeof(Foo) };
DynamicMethod method = new DynamicMethod("Demo", null, parameterTypes, typeof(Program));

ILGenerator iLGenerator = method.GetILGenerator();
iLGenerator.Emit(OpCodes.Ldarg_0);
iLGenerator.Emit (OpCodes.Ldstr, "Hello World!");
MethodInfo setMethod = typeof (Foo) GetProperty ("Text") GetSetMethod ();..
iLGenerator.EmitCall (OpCodes.Call, setMethod, null);
iLGenerator.Emit (OpCodes.Ret);

return (DemoDelegate) method.CreateDelegate (typeof (DemoDelegate));}



} public class Foo {

private string _text;

public string Text {

get {return _text;} {set
_text = value;}}

}

What Does The Pinky Ring Mean

flash project! Not quite so flash.

Now, after one months work in the proportions I've misjudged thoroughly, I will go and take a break and concentrate on the start of the winter semester. Besides, I'll go one way or another small thing, but great strides will not be in the near future.

But I stay here, I promise:)