THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Things You Don't Learn in School

I was speaking to my friend Byer today, discussing the use of the OpenQuery function to get to some data from Oracle (in a linked server) - a requirement of the system on which he was working. More specifically, he needed to lookup a single value based in Oracle and return it to SQL Server to use in a query. Since OpenQuery does not allow a variable to be used for the SQL statement, and since this particular query needed a variable, dynamic execution was going to have to be used to create the correct SELECT statement sing the OpenQuery function. So, after sorting out these little details, we were finally at a point where we could use the value coming back from the dynamically executed SQL statement.

So, I will keep the OpenQuery stuff out of this to show what happened. Neither of us had never tried the following little morsel of code; and we discovered there is something wrong with it.

DECLARE @OneValue Table (Val Varchar(11))
DECLARE @sExec Varchar (1000)

SET @sExec = 'SELECT au_id FROM authors where au_lname = ''MacFeather'''

EXECUTE (@sExec)


Now that I think about it, I'm going to give the answer in my next post. Enjoy....

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta


No Comments
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement