Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

Help! hresult DTS_E_VARIABLEALREADYONREADLIST

We have a SSIS package that intermittently fails on the deployed server (works fine on our dev server) with the following error:

"The variable "%1!s!" is already on the read list. A variable may only be added once to either the read lock list or the write lock list."

This seems to happen right at the start when the variables are processed before any tasks happen. It also only fails half the time.

I just don't even know where to begin. Any clues?

I also want to add that not only does it fail only some of the time it also fails on different variables, always on an expression and always at the begining.

I think maybe the expression evaluator has a locking problem...

Here are some examples:

The following is the *first* 4 messages of one failed run:

Beginning of package execution

The variable "User::RefreshFrom" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.

An error occurred with the following error message: "The expression for variable "DeleteRevenueFactQuery" failed evaluation. There was an error in the expression.".

The following is the *first* 3 messages of another failed run:

Beginning of package execution

The variable "User::SqlServer" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

The expression "@.[User::SqlServer]" on property "ServerName" cannot be evaluated. Modify the expression to be valid.

|||

Kris,

Its hard to work out what's wrong from what you've said here. Can you share your package?

If you like, send your package to me and I'l take a look.

-Jamie

jamie.thomson[at]conchango.com

|||

Kris, just a guess, but are you running the RTM version of SSIS?

Thanks,

K

|||Yes, I'm running on the final version of SSIS|||

One thing to add, this package always works on a single processor machine but we can recreate the intermittent problem on 2 different multi-processor machines.

Does anyone know if expressions are evaluated to set variables in concurrent threads?

The variables that are giving the intermittent problems are used in more than one expression to initialize other variables or properties.

I'm worried it is executing the expressions on more than one thread and ending up on different processors, and the locking scheme doesn't use a memory boundary when checking if the variable is already on the readonly list before adding it.

Anyways, I think at this point I'm going to scrap using expressions and setup the variables and properties in a script task. I have to get this working soon.

Everything else is done on our project.

|||

I too am experiencing this error. If I re-build/deploy the package, the error seems to go away until I build/deploy again, and then I usually get the error.

BobP

|||

I had the same failure where I had a sequence container with 2 execute package tasks in it running in parallel. This worked fine on my workstation but failed on the dual processor server. I added a dependency so they run sequentially and the problem has gone away.

My failure condition on the dual proc server was when the SSIS packages were launched from a SQL Agent job. Running interactively with DTEXEC from a command proc worked.

|||

I too have had the same problem on a dual-proc machine. There seems to be a familiar story developing here.

[Kris, sorry I didn't get back to you after you mailed - just haven't had time to explore it enough I'm afraid]

-Jamie

|||

I had the same problem on quad-processor machine. I'll try to get rid of this problem by setting MaxConcurrentExecutables to 1. It seems critical bug!

|||

I am facing the same issue on the 4 proc machine, If I execute the package using dtexecui i works without any problems. Does anyone have resolution on this ?

Regards

Meghana

|||

Well, I don't know what the current status on this matter is but, if anybody is interested, I'm having the same problems on a 4 Intel Xeon processors server.

Similar to what is mentioned in other related posts, everything runs smoothly on my 1 processor development box but, when deployed on a multiprocessors server, I'm having multiple DTS_E_VARIABLEALREADYONREADLIST error intermittently, i.e. with multiple tasks but not necessarily the same throughout various executions.

For the time being, I’ve fixed the problem by either adding precedence constraints between tasks (which could easily work in parallel) or by setting the MaxConcurrentExecutables package property to 1 or both but, for me, it is more a workaround than any real solution: if I cannot use both common configurations and multiprocessors, SSIS seems suddenly limited.

A quick note: contrary to what is mentioned in a related post, I can reproduce the same errors with both the dtexec command prompt utility and the graphical interface.

I should add that I’m also having issues with package transactions and Microsoft DTC but, at this point, I’m not sure whether these issues are due to multiprocessors, Windows 2000 (kinda old for SQL Server 2005, isn’t it?) or simply a wrong use of them.

Anyway, any new insights on this matter would be greatly appreciated.

Help! How to insert multiple rows into Database??

I keep getting this error but it will only insert the 1st row into my database table

The variable name '@.CustId' has already been declared. Variable names must be unique within a query batch or stored procedure.

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim drow As GridViewRow

For Each drow In GridView1.Rows

Dim textBoxText As String = CType(drow.FindControl("Label2"), Label).Text

SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName)

SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString)

SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice)

SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText)

SqlDataSource2.Insert()

Next

Response.Redirect("checkout.aspx")

End Sub

since you are operating in a loop, you should clear your parameters collection on each pass through

Dim drowAs GridViewRowFor Each drowIn GridView1.RowsDim textBoxTextAs String =CType(drow.FindControl("Label2"), Label).Text SqlDataSource2.InsertParameters.Clear()'<-- Clear the params first SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName) SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString) SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice) SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText) SqlDataSource2.Insert()Next

|||

Try this after your insert:

SqlDataSources2.InsertParameters.Clear();

There is another way to do this. Declare your parameters first, then assign the InsertParameters' default values for each InsertParameter after the insert.

SqlDataSource2.InsertParameters("Parameter1").DefaultValue=valueofParameter1

...

|||Omg .. thanks a lot ! It works... Haha ...

Help! Creating Custom Data Flow Task

I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

So provide a custom IDTSCustomProperty90 in ProvideComponentProperties() and let users type in the variable name is enough(of course, I should also follow what you said)?

I wanted to make it behave the same way as the FileNameVariable property of Raw File Destination, that is: users don't type in the variable name, instead, they select from all available variable names.
|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

How can I get the matching variable and it's value at runtime? And generally in which method should I do that? Thanks.
|||

Here's a sample from a component I developed to raise custom events from a package's control flow; the same basic technique should work for you in data flow as well.

First, create a property that stores the variable name. This will be set by the package developer at design time.

Code Snippet

private string variableName;

///

/// This property gets or sets the name of the variable in which the

/// event text is stored.

///

[Browsable(true)]

[Category("Custom")]

[Description("Gets or sets the name of the variable in which the event text is stored.")]

public string VariableName

{

get

{

return variableName;

}

set

{

if (value.IndexOf("::") == -1)

{

variableName = "User::" + value;

}

else

{

variableName = value;

}

}

}

(The set accessor for the property is simply explicitly adding the User namespace to the specified variable name unless a namespace is already specified. I've yet to add a drop-down so the package developer can pick from a list of available variables.)

Then, you can use the component's VariableDispenser object (which is passed in a parameter to many of the methods you overload when developing your component) to lock and work with the package variable identified by the property. Here's a sample from the Validate method in my component:

Code Snippet

Variables vars = null;

DTSExecResult result;

variableDispenser.LockOneForRead(variableName, ref vars);

if (vars[variableName].DataType != TypeCode.String)

{

// The variable is accessable, but is not the correct data type

componentEvents.FireError(0, SubComponent(variableDispenser),

string.Format("Variable '{0}' is not a String variable", variableName), "", 0);

result = DTSExecResult.Failure;

}

else

{

result = DTSExecResult.Success;

}

vars.Unlock();

return result;

The key thing here is that you're using the variableDispenser to lock the specific variable identified by the component's property, working with it, and then unlocking it when you're done.

(The SubComponent method which is referenced in the code sample above is just a little helper routine that returns a string in a standard format to identify where an error or event is coming from. I need this type of string in many places, so I have a function that can be reused, but it's not really relevant to your question...)

Hope this helps!

Help With Variable Containing Datetime

HI,

I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.

DECLARE @.DATE NVARCHAR(100)
SET @.DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @.DATE)

WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.

BUT,

WHEN I RUN A VARIABLE CONTAINING DATETIME,

DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + @.DATE)

IT GIVES ME AN ERROR :-

"Line 1: Incorrect syntax near '12'. "

IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.Try this:
DECLARE @.DATE DATETIME

SET @.DATE = GETDATE()
EXEC ('SELECT ' + ''''+@.DATE+'''')

Harshal.|||Hi,

Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.|||And what about this approach:

declare @.date datetime
set @.date = getdate()
select @.date

Greetz,
DePrins
;)|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.

Try this:
DECLARE @.DATE DATETIME,
@.Dt_Dsc Varchar(50),
@.SQL varchar(200)

SET @.DATE = GETDATE()
Set @.Dt_Ddc = Replace(Cast(Left(@.Date , 11) AS varchar(50)),' ','_')
Set @.SQL = 'Select ' + @.Dt_Dsc

Exec (@.SQL)

Gil|||In order to prevent you from tearing out your hair later, I'd like to strongly suggest that you format your dates differently and use them as a prefix rather than a suffix on your table names. If you format the prefix as E20040720 instead of 20_july2004, you won't have problems with 12_dec2000 sorting between 04_jul2004 and 20_may2010! If you use a prefix instead of a suffix, all of your extract tables will sort together by date of extract when you display table names sorted alphabetically. You can use:DECLARE @.prefix VARCHAR(10)
SET @.prefix = 'E' + Replace(Convert(VARCHAR(10), GetDate(), 120), '-', '')Note that I added a letter before the digits, just to make it easier to work with the tables going forward. Sometimes it gets messy trying to cope with table names that start with a digit.

-PatP|||Hi,

Thanks Pat And Glubstein That Was Wonderful Solved Many Of My Problems And Saved Many Headaches.

Thanks Once Again

Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [zero]
in the table.
--
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
--
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
--
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
--
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
--
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
--
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:
>> EXEC @.pass = dbo.usp_GeneratePassword
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
>)
>> Hi all,
>[quoted text clipped - 180 lines]
>> Thanks.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||I'm glad I was able to help you out.
--
Dan Guzman
SQL Server MVP
"thegekkster via SQLMonster.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>

Help with using OUTPUT parameter

Hi all,
I have a procedure (sp1) that needs to execute another procedure (sp2); need
to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
called, executed from within ASP. As it is currently I get a value of 0 [
;zero]
in the table.
Code for SP1:
--
CREATE PROCEDURE dbo.usp_AddAffiliateApplication
(
@.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100), @.last
varchar(100),
@.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
varchar(200),
@.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
@.zip char(5),
@.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
)
AS
SET NOCOUNT ON
SET @.send = 'N'
IF @.url NOT IN ( SELECT a.applicantURL
FROM dbo.affiliateApplications a )
BEGIN
SET @.send = 'Y'
EXEC @.pass = dbo.usp_GeneratePassword
--
DECLARE @.TRAN1 varchar(50)
SELECT @.TRAN1 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN1
WITH MARK 'Insert-AAA'
INSERT INTO dbo.affiliateApplications (applicantWebsiteName, applicantURL,
affiliateCategoryID,
applicantFirstName,
applicantLastName, applicantEmailAddress,
applicantAreaCode,
applicantPhoneNumber, businessStructureID,
applicantPayName,
applicantAddress1, applicantAddress2,
applicantCity, stateID,
applicantZipCode, countryID,
applicantPassword, applicantIP,
applicantDateApplied)
VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
@.structure, @.pay, @.add1,
@.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
ELSE
BEGIN
DECLARE @.days int
SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
FROM dbo.affiliateApplications aa
WHERE (aa.applicantURL = @.url) AND ((aa.
applicantApprovalStatus = 'N')
OR (aa.
applicantApprovalStatus = 'P'))
IF @.days <= 90
BEGIN
SET @.send = 'X'
END
ELSE
BEGIN
SET @.send = 'Y'
DECLARE @.TRAN2 varchar(50)
SELECT @.TRAN2 = 'AddAffiliateApplication'
BEGIN TRAN @.TRAN2
WITH MARK 'Update-AAA'
UPDATE dbo.affiliateApplications
SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
WHERE (applicantURL = @.url)
IF @.@.ERROR = 0
BEGIN
COMMIT TRAN AddAffiliateApplication
END
ELSE
BEGIN
ROLLBACK TRAN AddAffiliateApplication
END
END
END
GO
Code for SP2:
--
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex'
)
AS
BEGIN
DECLARE @.password varchar(20)
DECLARE @.type tinyint
DECLARE @.bitmap char(12)
SET @.password = ''
SET @.bitmap = 'aeiouy!#$-_+'
WHILE @.passLength > 0
BEGIN
IF @.passType = 'simple'
BEGIN
IF (@.passLength%2) = 0
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
ELSE
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
END
ELSE
BEGIN
SET @.type = ROUND(1 + (RAND() * (3)),0)
IF @.type = 1
SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
ELSE IF @.type = 2
SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
ELSE IF @.type = 3
SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
ELSE IF @.type = 4
SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
(RAND() * (11)),0)),1)
END
SET @.passLength = @.passLength - 1
END
SELECT @.password OUTPUT
END
GO
Other than this issue, in which I guess I'm not going about setting or
getting the output parameter in the right way, each of the SPs work properly
by themselves.
Any help or suggestions appreciated.
Thanks.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1> EXEC @.pass = dbo.usp_GeneratePassword
This statement will assign the stored procedure return code to the @.pass
variable. However you are returning the password generated by
usp_GeneratePassword as a result set (SELECT statement).
A stored procedure return code (returned via a RETURN statement) is an
integer usually used to indicate success or failure. Data can be returned
via OUTPUT parameters or in a result set. It's easier to process OUTPUT
parameters in Transact-SQL rather than a result set.
To return the password value as an output parameter, change the
usp_AddAffiliateApplication code to:
EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
Remove the SELECT from usp_GeneratePassword and change the header as
follows:
CREATE PROCEDURE dbo.usp_GeneratePassword
(
@.passLength int = 8,
@.passType char(7) = 'complex',
@.password varchar(20) OUTPUT
)
Hope this helps.
Dan Guzman
SQL Server MVP
"thegekkster" <u6631@.uwe> wrote in message news:613dacf27c8fd@.uwe...
> Hi all,
> I have a procedure (sp1) that needs to execute another procedure (sp2);
> need
> to set a variable (@.pass) in sp1 with a value generated within sp2. SP1 is
> called, executed from within ASP. As it is currently I get a value of 0
> [zero]
> in the table.
> --
> Code for SP1:
> --
> CREATE PROCEDURE dbo.usp_AddAffiliateApplication
> (
> @.web varchar(200), @.url varchar(200), @.cat int, @.first varchar(100),
> @.last
> varchar(100),
> @.email varchar(100), @.area char(3), @.phone char(7), @.structure int, @.pay
> varchar(200),
> @.add1 varchar(200), @.add2 varchar(200), @.city varchar(100), @.state int,
> @.zip char(5),
> @.country int, @.pass varchar(20), @.ip varchar(15), @.send char(1) OUTPUT
> )
> AS
> SET NOCOUNT ON
> SET @.send = 'N'
> IF @.url NOT IN ( SELECT a.applicantURL
> FROM dbo.affiliateApplications a )
> BEGIN
> SET @.send = 'Y'
> --
> EXEC @.pass = dbo.usp_GeneratePassword
> --
> DECLARE @.TRAN1 varchar(50)
> SELECT @.TRAN1 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN1
> WITH MARK 'Insert-AAA'
> --
> INSERT INTO dbo.affiliateApplications (applicantWebsiteName,
> applicantURL,
> affiliateCategoryID,
> applicantFirstName,
> applicantLastName, applicantEmailAddress,
> applicantAreaCode,
> applicantPhoneNumber, businessStructureID,
> applicantPayName,
> applicantAddress1, applicantAddress2,
> applicantCity, stateID,
> applicantZipCode, countryID,
> applicantPassword, applicantIP,
> applicantDateApplied)
> VALUES (@.web, @.url, @.cat, @.first, @.last, @.email, @.area, @.phone,
> @.structure, @.pay, @.add1,
> @.add2, @.city, @.state, @.zip, @.country, @.pass, @.ip, GETDATE())
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> ELSE
> BEGIN
> DECLARE @.days int
> SELECT @.days = DATEDIFF(d, aa.applicantDateApplied, GETDATE())
> FROM dbo.affiliateApplications aa
> WHERE (aa.applicantURL = @.url) AND ((aa.
> applicantApprovalStatus = 'N')
> OR (aa.
> applicantApprovalStatus = 'P'))
> IF @.days <= 90
> BEGIN
> SET @.send = 'X'
> END
> ELSE
> BEGIN
> SET @.send = 'Y'
> DECLARE @.TRAN2 varchar(50)
> SELECT @.TRAN2 = 'AddAffiliateApplication'
> BEGIN TRAN @.TRAN2
> WITH MARK 'Update-AAA'
> --
> UPDATE dbo.affiliateApplications
> SET applicantDateApplied = GETDATE(), applicantApprovalStatus = 'P'
> WHERE (applicantURL = @.url)
> --
> IF @.@.ERROR = 0
> BEGIN
> COMMIT TRAN AddAffiliateApplication
> END
> ELSE
> BEGIN
> ROLLBACK TRAN AddAffiliateApplication
> END
> END
> END
> GO
> --
> Code for SP2:
> --
> CREATE PROCEDURE dbo.usp_GeneratePassword
> (
> @.passLength int = 8,
> @.passType char(7) = 'complex'
> )
> AS
> BEGIN
> DECLARE @.password varchar(20)
> DECLARE @.type tinyint
> DECLARE @.bitmap char(12)
> SET @.password = ''
> SET @.bitmap = 'aeiouy!#$-_+'
> WHILE @.passLength > 0
> BEGIN
> IF @.passType = 'simple'
> BEGIN
> IF (@.passLength%2) = 0
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> ELSE
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> END
> ELSE
> BEGIN
> SET @.type = ROUND(1 + (RAND() * (3)),0)
> IF @.type = 1
> SET @.password = @.password + CHAR(ROUND(97 + (RAND() * (25)),0))
> ELSE IF @.type = 2
> SET @.password = @.password + CHAR(ROUND(65 + (RAND() * (25)),0))
> ELSE IF @.type = 3
> SET @.password = @.password + CHAR(ROUND(48 + (RAND() * (9)),0))
> ELSE IF @.type = 4
> SET @.password = @.password + SUBSTRING(@.bitmap,CONVERT(int,ROUND(1 +
> (RAND() * (11)),0)),1)
> END
> SET @.passLength = @.passLength - 1
> END
> SELECT @.password OUTPUT
> END
> GO
> Other than this issue, in which I guess I'm not going about setting or
> getting the output parameter in the right way, each of the SPs work
> properly
> by themselves.
> Any help or suggestions appreciated.
> Thanks.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Thanks, Dan.
This helped clarify the issue for me, and your suggestions worked perfectly.
Appreciate your help...
Dan Guzman wrote:[vbcol=seagreen]
>This statement will assign the stored procedure return code to the @.pass
>variable. However you are returning the password generated by
>usp_GeneratePassword as a result set (SELECT statement).
>A stored procedure return code (returned via a RETURN statement) is an
>integer usually used to indicate success or failure. Data can be returned
>via OUTPUT parameters or in a result set. It's easier to process OUTPUT
>parameters in Transact-SQL rather than a result set.
>To return the password value as an output parameter, change the
>usp_AddAffiliateApplication code to:
>EXEC dbo.usp_GeneratePassword @.password = @.pass OUTPUT
>Remove the SELECT from usp_GeneratePassword and change the header as
>follows:
>CREATE PROCEDURE dbo.usp_GeneratePassword
>(
> @.passLength int = 8,
> @.passType char(7) = 'complex',
> @.password varchar(20) OUTPUT
> )
>
>[quoted text clipped - 180 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||I'm glad I was able to help you out.
Dan Guzman
SQL Server MVP
"thegekkster via droptable.com" <u6631@.uwe> wrote in message
news:61403d2080344@.uwe...
> Thanks, Dan.
> This helped clarify the issue for me, and your suggestions worked
> perfectly.
> Appreciate your help...
>

Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)

Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet

WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!