I'm having difficulty passing a date parameter in a SSIS OLEDB connection to a stored procedure using SQL command with the error message "Attempted to read or write protected memory ..." and if I create a variable, I get the error "must declare the scalar variable..". Here is the OLEDB command: "EXEC dbo.mic_TGDrop ?" where ? requires a date parameter @ReportDate. Why does the call to the stored procedure fail? What would be the correct syntax of a variable to use SQL Command from variable? The SQL BOL does not cover this; is there another resource that has a simple example? Thanks.
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi all, I have created a global variable and it will read the user input from a web application. How can i pass this variable into OLE DB destination SQL command so that i can retrieve the user specified table? Or is there a better way to do this?
select * from TABLE where user='jacky' ,it can working,but if like this: dim name as string="jacky" select * from TABLE where user=name it won't doing,Can a variable used in SQL select command,if can,how to make it working.
Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query.
Here's what it looks like:
[after submitting a form on the previous page with a drop down list box titled "fiscal"]
dim strYear
strYear = Request.Form("fiscal")
sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';"
[where strYear is the following: PYMNT_DT > ''12/31/98'']
Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed?
This is a really simple question. When I set my variable (the one will hold my SQL Command) property "EvaluateAsExpression=True", I don't get the option to edit the expression, I was expecting to get a little button that would open the "Property Expressions Editor" if pressed.
Am I doing anything wrong? Should I edit my SQL Command somewhere else and then Copy+Paste on my variable expression?
I need to pass a ServerName as a field to my query and I'm doing that by looping through a list of servers...
Description: The package path referenced an object that cannot be found: "Package.Variables[User::UserVarchar1].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
DTExec: Could not set Package.Variables[User::UserVarchar1].value value to 10.
I was not able to find the solution myself so here I am to ask you.
I'm using an Ole DB Source with the SQL command from variable.
My variable expression needs to look like this:
select * from dbo.fx_function (SomeDateVariable)
My sql server fx_function recieves as a parameter datetime, my variable SomeDateVariable is datetime type.
I tried all different types of cast on @User :: SomeDateVariable but I either can't evaluate the expression or I have errors trying to execute the package.
I read several posts on this topic and I would like to confirm my understanding.
This question has to do with parameterizing the select statement in the OLE DB Source editor. Initially, I thought I could use Data access mode: SQL command, and somehow use a user variable in there to build my Select statement.
But in researching further, it looks like I need to store the entire SQL command in a one long string variable (let's call it A), where A is built off of another variable that has my parameter. I then use A in Data access mode: SQL command from variable.
Am I correct? And is there not a way to accomplish the samething with only one variable that is your parameter value?
It seems a bit clumsy to store the entire SQL expression in a string variable.
Using Jamie's, John's and Rafae post i have got this far. My scenario demands me to do an incremental load. So i have followed your article and have created a one variable 'vDate' (Dataflow Task, Datetime, 3/3/2008) and another variable SourceSQL (Dataflow Task, String). I have set this SourceSQL evaluation to True and have added this; "select * from stagingperfdata where startdatetime = "+ (DT_WSTR,10) @[vDate].
I want to call this within a OLE DB source, so i select the data access method to SQL command from variable. My variable name is visible and i am able to select it and then save and debug. I also have a derived column transformation to increment the variable vDate by one day. The flow is successful but there is no records written to the target. I tried the same query (available in the variable's value column) in SSMS and it is retrieving 12 records. I went back to the Ole Db source and wanted to preview the data, but i get "Query timeout expired (Microsoft SQL Native Client)" error. Am i missing something fundamental?
Hello All I m trying to update a table whose col name will be read from another table. For e.g. Table1 gives the result: 'emp1', 1, 'John' 'emp2', 2, 'Mike' Now in the second table, i need to update the table with Col name = 'Emp1' and then from the second row (above), I need to update Col name= 'Emp2' I need to write one Update Statement which will handle all the cases. I tried Update Table2 set @VariableName = ....... but didnt work... How can i do that ?
I created a package scope variable to use as a sql command for a data flow task. But when I click the drop down to select it, nothing appears. Anybody else have this issue and find a solution?
First let me say that I'm new to SSIS, so be gentle with me.
I need to extract a limited set of data from a very large view in Oracle (I know, yuk!). The view contains millions of rows, but I only need the child matches of 343 unique keys in a one to many relationship. In pure SQL the query would look something like this.
Select proj, tn, rn, total FROM Oracle.view WHERE proj in (select distinct proj from MSSQL.dbo.projlist)
As you can see, this is an impossible query on many levels.
My first thought was to get the runtime list into a variable and use that variable as a parameter in the Oracle OleDb Source. Alas, the Oracle source will not allow me to add a parameter.
My second thought was to use a script component and build a SQL_Command string into a variable with all of my keys included. Then use the read_write variable as the SQL Command from variable in the Oracle Source. My attempts to construct such a variable expression have failed.
In the data flow task I have multiple OLE DB source task to retreive data from a cube using MDX. Below is an example of the query from sql command text field: I have a few questions on how to modify this statment to make it more robust.
1) The where clause [Calendar].[Quarter 2 (2007)] should actually be some kind of variable that will change each time the user runs this project. So I am wondering how to make this a variable and get it into this field.
2) I had thought that I could use the SSIS variable. I have created one, but I can not figure how to get it into the field that contains the given select statment.
3) I believe that once I get this variable part to work then I want a way to have user set this data value. Either by selecting data from a table in database or through a user interface where user enters data. I did do some resarch on creating a user interface, but I did not understand what I had to do, so if any one knows where to find a tutorial on how to do this let me know, or what they believe the best/easiest way is to get data from user to fill this where clause. select * from OPENROWSET('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;', 'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Adjusted Relative Weight],[Measures].[Adjusted Case Weight]} ON COLUMNS, ({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} * [RIC].[CMGGRPCD].ALLMEMBERS) ON ROWS FROM [ESR] WHERE [Calendar].[Quarter 2 (2007)] ')
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
Hi, i am using command files(.cmd) and osql utility to conneft to a database. following is the requirement. I have a query "SELECT top 1 au_fname FROM pubs..authors".
Can i store the value of au_fname in the command file?
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value. ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid This is all done in Page_Load event backend code. string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
I'm looking at http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx and http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx trying to understand where we are at using datetime overrides in ssis from the command line. One of these articles suggests something has been fixed in ssis sp1, what part of this issue was fixed?
Can you override (from the command line) a user var in ssis whose ValueType property is datetime? Or do some of the older articles still apply, ie the user var must be string so that ssis wont create a separate user var with the same name etc etc? And if it is string, does some kind of conversion need to be done to compare it against datetime cols in t-sql inside the pkg?
I have to read data from an MDB file into SQL Server. Simple, but I need to place a literal into every row. In my OLE DB Source I would use:
SELECT '001' AS col1, foo, bar FROM Table1
But the problem is that I pass my value for col1 into the package from C# using Managed DTS.
So I set up my OLE DB Source using "SQL command from variable." And I have two variables one to receive the value of for col1 (col1) and one that is an evaluated expression that has a dynamic sql statement (dynSql).
col1 is a String as is dynSql. dynSql looks like this:
"SELECT " + @[col1] + " as col1, foo, bar FROM Table1"
Now as long as I set the value of col1 to '001' that should work. Right? Well not exactly. The destination for col1 in SQL Server is a char( 3 ). But the package makes the col1 column from the source a DT_WSTR. Which causes the famed: "Text was truncated or one or more characters had no match in the target code page.".
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use: select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" then I'm getting a error message: @OldID not declare.
Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from sys.filegroups where is_default = 1) ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
I'm seeing a problem with printing very long strings using the PRINT command on a VARCHAR(MAX) variable. After a certain amount of characters the string is truncated....it looks like the limit is at around 8,000 characters.
Does anyone know of a solution or a workaround for this?
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty. Expressions is empty.
When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/* ** fn_FindReports.sql ** ** This multi-statement table-valued user-defined ** function takes an EmplyeeID number as its parameter ** and provides information about all employees who ** report to that person. */ USE ClassNorthwind GO /* ** As a multi-statement table-valued user-defined ** function it starts with the function name, ** input parameter definition and defines the output ** table. */ CREATE FUNCTION fn_FindReports (@InEmployeeID char(5)) RETURNS @reports TABLE (EmployeeID char(5) PRIMARY KEY, Name nvarchar(40) NOT NULL, Title nvarchar(30), MgrEmployeeID int, processed tinyint default 0) -- Returns a result set that lists all the employees who -- report to a given employee directly or indirectly AS BEGIN DECLARE @RowsAdded int -- Initialize @reports with direct reports of the given employee INSERT @reports SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN -- Mark all employee records whose direct reports are going to be -- found in this iteration UPDATE @reports SET processed = 1 WHERE processed = 0
-- Insert employees who report to employees marked 1 INSERT @reports SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0 FROM employees e, @reports r WHERE e.ReportsTo = r.EmployeeID AND r.processed = 1 SET @RowsAdded = @@rowcount -- Mark all employee records whose direct reports have been -- found in this iteration UPDATE @reports SET processed = 2 WHERE processed = 1 END RETURN -- Provides the value of @reports as the result END GO
