Showing posts with label variable. Show all posts
Showing posts with label variable. Show all posts

Tuesday, March 20, 2012

Associative array

I have a variable declared as this:
type ltype_Queue is table of NUMBER index by binary_integer;
l_Queue ltype_Queue;
How can I accomplish the equivalent to this:
select *
from Items
where ID in l_Queue
Thanks,
Layneselect *
from Items
where ID in (select * from TABLE(cast(l_Queue as myTableType)));

where myTableType is a data type defined as below :
create type myTableType as table of varchar2(255)
/

Originally posted by lrobin3
I have a variable declared as this:
type ltype_Queue is table of NUMBER index by binary_integer;
l_Queue ltype_Queue;

How can I accomplish the equivalent to this:

select *
from Items
where ID in l_Queue

Thanks,
Layne

Monday, March 19, 2012

assigning xml output

I have a small question.
I am recently started using 2005 and I want to assign results of following
query in to a variable of any type.
select top 1 * from dbo.authors for xml auto
Please let me know how do I achieve this.
Kishortry using for xml path()
--
"kishor" wrote:

> I have a small question.
> I am recently started using 2005 and I want to assign results of following
> query in to a variable of any type.
> select top 1 * from dbo.authors for xml auto
>
> Please let me know how do I achieve this.
> Kishor|||and use it this way. Hope this helps.
declare @.a varchar(8000)
set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
select @.a|||Hi
I got error.
Line 2: Incorrect syntax near 'xml'.
Kishor
"Omnibuzz" wrote:

> and use it this way. Hope this helps.
> declare @.a varchar(8000)
> set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
> select @.a
>|||This works only is SQL Server 2005. You are using SQL 2005 right?

Assigning XML data to variables

Hi Guys...

Need some assistance here again.
What data type should I use for the variable that I receive from a resultset?

I tried setting it to object, but I encountered some problem later in the stage during the data flow because the XML source adapter cannot get the variable.

I tried setting it to String but get some error during runtime because of invalid datatype.

The main purpose here is actually to get the data from an XML column in a table and populate it into the XML Source Adapter so that I can do some transformations.

Any ideas?

Thanks.Hi,
Yes, this is definitely an issue. In the next release (post- CTP15) you will be able to put the XML result set into a string variable that can then be consumed by the XML Source Adapter. For now, you might be able to work around it with a script component that creates a new string variable and populates it with the data from the object variable. I'm sorry I don't have a better answer for you.

Thanks,
Mark

Sunday, March 11, 2012

Assigning the results of a table function to a table variable

SQL Server 2K SP1.
I'm trying to put the results of a Table function into a Table variable for
use in a Select. My code is:
DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
SELECT LoanNo
FROM UV_Tran016 AS t16
INNER JOIN @.LoanTable lt
ON t16.LoanNo = lt.LoanNo
The error I get is:
Server: Msg 195, Level 15, State 10, Line 4
'UDF_GetLoansInDeal' is not a recognized function name.
Line 4 is the SET statement.
SELECT TOP 10 *
FROM UDF_GetLoansInDeal (1, 8)
works just fine.
The function is defined as:
CREATE FUNCTION UDF_GetLoansInDeal
(
@.DealType int
, @.Id int
)
RETURNS @.LoansTable table
(
LoanNo DECIMAL(10,0)
)
AS
BEGIN
IF @.DealType = 1
BEGIN
INSERT @.LoansTable
SELECT Loan_No
FROM U_PledgeData
WHERE PledgeId = @.Id
ORDER BY PledgeId
END
ELSE
IF @.DealType = 2
BEGIN
INSERT @.LoansTable
SELECT Loan_No
FROM U_IntercompanyData
WHERE IntercompanyId = @.Id
ORDER BY IntercompanyId
END
RETURN
END
What am I doing wrong?
Thanks!I'm not positive, and obviously this is untested, but I think you just meant
to do this:
SELECT t16.LoanNo
FROM UV_Tran016 AS t16
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t16.LoanNo = lt.LoanNo
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:DD09EA8C-6F54-4086-BAF7-4DD2CC42879F@.microsoft.com...
> SQL Server 2K SP1.
> I'm trying to put the results of a Table function into a Table variable
> for
> use in a Select. My code is:
> DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> SELECT LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN @.LoanTable lt
> ON t16.LoanNo = lt.LoanNo
> The error I get is:
> Server: Msg 195, Level 15, State 10, Line 4
> 'UDF_GetLoansInDeal' is not a recognized function name.
> Line 4 is the SET statement.
> SELECT TOP 10 *
> FROM UDF_GetLoansInDeal (1, 8)
> works just fine.
> The function is defined as:
> CREATE FUNCTION UDF_GetLoansInDeal
> (
> @.DealType int
> , @.Id int
> )
> RETURNS @.LoansTable table
> (
> LoanNo DECIMAL(10,0)
> )
> AS
> BEGIN
> IF @.DealType = 1
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_PledgeData
> WHERE PledgeId = @.Id
> ORDER BY PledgeId
> END
> ELSE
> IF @.DealType = 2
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_IntercompanyData
> WHERE IntercompanyId = @.Id
> ORDER BY IntercompanyId
> END
> RETURN
> END
> What am I doing wrong?
> Thanks!
>|||SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
should be
INSERT INTO @.LoanTable
SELECT * FROM dbo.UDF_GetLoansInDeal (1, 8)
Keith Kratochvil
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:DD09EA8C-6F54-4086-BAF7-4DD2CC42879F@.microsoft.com...
> SQL Server 2K SP1.
> I'm trying to put the results of a Table function into a Table variable
> for
> use in a Select. My code is:
> DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> SELECT LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN @.LoanTable lt
> ON t16.LoanNo = lt.LoanNo
> The error I get is:
> Server: Msg 195, Level 15, State 10, Line 4
> 'UDF_GetLoansInDeal' is not a recognized function name.
> Line 4 is the SET statement.
> SELECT TOP 10 *
> FROM UDF_GetLoansInDeal (1, 8)
> works just fine.
> The function is defined as:
> CREATE FUNCTION UDF_GetLoansInDeal
> (
> @.DealType int
> , @.Id int
> )
> RETURNS @.LoansTable table
> (
> LoanNo DECIMAL(10,0)
> )
> AS
> BEGIN
> IF @.DealType = 1
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_PledgeData
> WHERE PledgeId = @.Id
> ORDER BY PledgeId
> END
> ELSE
> IF @.DealType = 2
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_IntercompanyData
> WHERE IntercompanyId = @.Id
> ORDER BY IntercompanyId
> END
> RETURN
> END
> What am I doing wrong?
> Thanks!
>|||Aaron,
Ah, I see. Since the function returns a table, I can use the function in the
place of a table name in the join.
That makes sense, but I wanted to assign the table to a table variable
because I'm really going to do a delete on multiple tables and didn't want t
o
execute the function on each delete. For example,
DELETE UV_Tran016 AS t16
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t16.LoanNo = lt.LoanNo
DELETE UV_Tran022 AS t22
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t22.LoanNo = lt.LoanNo
DELETE UV_Tran025 AS t25
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t25.LoanNo = lt.LoanNo
etc.
I believe the next reply shows me how to do that.
Thanks for the help,
Bob
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm not positive, and obviously this is untested, but I think you just mea
nt
> to do this:
> SELECT t16.LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
> ON t16.LoanNo = lt.LoanNo|||Keith,
Ah, this is another place where SET is not used to assign a value to a local
variable. I get it.
Thanks,
Bob
"Keith Kratochvil" wrote:

> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> should be
> INSERT INTO @.LoanTable
> SELECT * FROM dbo.UDF_GetLoansInDeal (1, 8)
> --
> Keith Kratochvil|||"Bob" <notrainsley@.worldsavings.com> wrote in message
news:DD09EA8C-6F54-4086-BAF7-4DD2CC42879F@.microsoft.com...
> SQL Server 2K SP1.
> I'm trying to put the results of a Table function into a Table variable
> for
> use in a Select. My code is:
> DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> SELECT LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN @.LoanTable lt
> ON t16.LoanNo = lt.LoanNo
> The error I get is:
> Server: Msg 195, Level 15, State 10, Line 4
> 'UDF_GetLoansInDeal' is not a recognized function name.
> Line 4 is the SET statement.
> SELECT TOP 10 *
> FROM UDF_GetLoansInDeal (1, 8)
> works just fine.
> The function is defined as:
> CREATE FUNCTION UDF_GetLoansInDeal
> (
> @.DealType int
> , @.Id int
> )
> RETURNS @.LoansTable table
> (
> LoanNo DECIMAL(10,0)
> )
> AS
> BEGIN
> IF @.DealType = 1
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_PledgeData
> WHERE PledgeId = @.Id
> ORDER BY PledgeId
> END
> ELSE
> IF @.DealType = 2
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_IntercompanyData
> WHERE IntercompanyId = @.Id
> ORDER BY IntercompanyId
> END
> RETURN
> END
> What am I doing wrong?
> Thanks!
>
The result of a table-valued function is a result set. It can't be assigned
directly to a variable. Replace the SET with an INSERT:
INSERT INTO @.LoanTable (LoanNo)
SELECT LoanNo FROM UDF_GetLoansInDeal (1, 8);
Another change you should make is to remove the ORDER BYs in your function.
They do nothing except maybe slow down the query.
Usually it's better to use in-line table-valued functions in preference to
multi-statement ones where you can. For example your function could be
rewritten as follows, which may yield a better query plan and faster
results.
CREATE FUNCTION UDF_GetLoansInDeal
(
@.DealType int
, @.Id int
)
RETURNS TABLE
AS
RETURN
(
SELECT Loan_No AS LoanNo
FROM U_PledgeData
WHERE PledgeId = @.Id
AND @.DealType = 1
UNION ALL
SELECT Loan_No AS LoanNo
FROM U_IntercompanyData
WHERE IntercompanyId = @.Id
AND @.DealType = 2
)
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Assigning the for xml query output to a variable

Hi,
I would like to generate an XML query using the 'for xml' clause and assign
that to a text variable for further manipulation.
for e.g., I would like to do some thing like this.
declare @.test text
Set @.test = Select * from test_table for xml auto
But this gives my the syntax error. Can anyone help capturing the xml output
of a query into a variable?
Regards,
ArunHi
Have you looked at the text datatype limitations. You can not use a lot of
the T-SQL string manipulation functions against it, so you can't really do
much with it once you have it.
You can do a SELECT ... INTO ... to write it to a temporary table, then
select it into your @.test variable. I have not tried it, but is a
suggestion.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arun" <kathir_arun@.hotmail.com> wrote in message
news:ePOh0u8QFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to generate an XML query using the 'for xml' clause and
> assign
> that to a text variable for further manipulation.
> for e.g., I would like to do some thing like this.
> declare @.test text
> Set @.test = Select * from test_table for xml auto
> But this gives my the syntax error. Can anyone help capturing the xml
> output
> of a query into a variable?
> Regards,
> Arun
>

Assigning query output to a variable

I am running a query that will return 1 field from 1 row. What is the
syntax to assign that to a variable.
Samplecode:
Declare @.DataFilePath varchar(200)
Declare @.DBName varchar(200)
Set @.DBName = 'TestDB'
@.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
@.DBName
Thanks!
Ron@.DataFilePath = select MAX(FileName) From master.dbo.sysaltfiles WHERE name
=
@.DBName
Or MIN. Since you are getting one row, it does not do much, but it does
tell SQL Server that only one value is coming out of the select.
RLF
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Op0ca4p%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I am running a query that will return 1 field from 1 row. What is the
>syntax to assign that to a variable.
> Samplecode:
> Declare @.DataFilePath varchar(200)
> Declare @.DBName varchar(200)
> Set @.DBName = 'TestDB'
> @.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
> @.DBName
>
>
> Thanks!
> Ron
>
>

Assigning Filepath to Global Variable in DTS

How can I assign file path to a DTS Global Variable in an activex script.

i hope u know how to create global variable in DTS. Here we have two global variable DestinationPath and SourcePath and see how its been used in VBScript

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success

sSourceFile1 =dtsglobalvariables("DestinationPath").value
sSourceFile2 = dtsglobalvariables("SourcePath").value

set fso = CreateObject("Scripting.FileSystemObject")
fso.deletefile sSourceFile1
fso.copyfile sSourceFile2, sSourceFile1

End Function

http://www.sqldts.com is one of the best resource in DTS

Madhu

|||

Now that I have assigned a filepath to a global variable and is a dynamic one, since the file that is created is done on a daily basis and each file has a datetime stamp, i have figured out that already, but now i am facing trouble assigning the dynamic path that i have assigned in global variable to the Send Mail Task in DTS, can you help me with this issue.

|||

Consult http://msdn2.microsoft.com/en-us/library/ms141698.aspx for how to create a property expression on your send mail task.

You are just about there!

jkh

Thursday, March 8, 2012

Assigning DATEADD results to variable

I am working on a stored procedure that requires saving the results of
several DATEADD calculations to variables. The problem I am having is
illustrated by the following code:
----
declare @.LastOfRecMo datetime
declare @.FirstOfNextMo datetime
declare @.LastOfNextMo datetime
declare @.AprilFirst datetime
select @.AprilFirst = '4/1/2005'
select @.LastOfRecMo = '3/31/2005'
select @.FirstOfNextMo = DATEADD(d, 1, @.LastOfRecMo)
select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.AprilFirst))
select LastOfRecMo = @.LastOfRecMo,
FirstOfNextMo = @.FirstOfNextMo,
FirstOfNextMo2 = DATEADD(d, 1, @.LastOfRecMo),
LastOfNextMo = @.LastOfNextMo
----
I would expect the @.FirstOfNextMo variable to be set to '4/1/2005', but as
you can see by running the above code, the result, returned as FirstOfNextMo
,
is always NULL, although FirstOfNextMo2 returns the correct date using the
identical calculation. Interestingly, @.LastOfNextMo works correctly with a
much more complex DATEADD calculation.
What's going on here?Sheldon Penner wrote:
> I am working on a stored procedure that requires saving the results of
> several DATEADD calculations to variables. The problem I am having is
> illustrated by the following code:
> ----
> declare @.LastOfRecMo datetime
> declare @.FirstOfNextMo datetime
> declare @.LastOfNextMo datetime
> declare @.AprilFirst datetime
> select @.AprilFirst = '4/1/2005'
> select @.LastOfRecMo = '3/31/2005'
> select @.FirstOfNextMo = DATEADD(d, 1, @.LastOfRecMo)
> select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.AprilFirst))
> select LastOfRecMo = @.LastOfRecMo,
> FirstOfNextMo = @.FirstOfNextMo,
> FirstOfNextMo2 = DATEADD(d, 1, @.LastOfRecMo),
> LastOfNextMo = @.LastOfNextMo
> ----
> I would expect the @.FirstOfNextMo variable to be set to '4/1/2005',
> but as you can see by running the above code, the result, returned as
> FirstOfNextMo, is always NULL, although FirstOfNextMo2 returns the
> correct date using the identical calculation. Interestingly,
> @.LastOfNextMo works correctly with a much more complex DATEADD
> calculation.
> What's going on here?
First thing is the date format you are using is not portable. The only
portable formats are:
yyyymmdd
yyyy-mm-ddThh:mm:ss.mmm(no spaces)
But your code works fine for me with the bad date format. Try changing
the date format and see what you get.
David Gugick
Imceda Software
www.imceda.com|||I'm baffled! I worked on this problem for hours yesterday, restarted the SQ
L
server, rebooted the computer, and could not get the code to run properly.
This morning, after reading your post, I tried again and the problem has gon
e
away. Thank you for your response.
"David Gugick" wrote:

> Sheldon Penner wrote:
> First thing is the date format you are using is not portable. The only
> portable formats are:
> yyyymmdd
> yyyy-mm-ddThh:mm:ss.mmm(no spaces)
> But your code works fine for me with the bad date format. Try changing
> the date format and see what you get.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||I thought the problem was solved, but it's not. DATEADD behaves erratically
when given a variable as an argument. The code I sent you yesterday
inexplicably runs correctly now, but the following example, closer to what I
am actually using, does not:
----
declare @.ReceiverDate datetime
declare @.FirstOfRecMo datetime
declare @.LastOfRecMo datetime
declare @.FirstOfNextMo datetime
declare @.LastOfNextMo datetime
-- Receiver Date is 3/27/2005
set @.ReceiverDate = '20050327'
-- First Day of Receiver Month
select @.FirstOfRecMo = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0'
+ cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
select myVar = @.FirstOfRecMo,
calc = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0' +
cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
-- First Day of Month after Receiver Month
select @.FirstOfNextMo = DATEADD(m, 1, @.FirstOfRecMo)
select myVar = @.FirstOfNextMo,
calc = DATEADD(m, 1, @.FirstOfRecMo)
-- First Day of Receiver Month
select @.LastOfRecMo = DATEADD(d, -1, @.FirstOfNextMo)
select myVar = @.LastOfRecMo,
calc = DATEADD(d, -1, @.FirstOfNextMo)
-- Last Day of Month after Receiver Month
select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
select myVar = @.LastOfNextMo,
calc = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
----
I entered the Receiver Date in the format you recommended, although in the
actual application, the date is pulled from the database.
The first calculation correctly returns '3/1/2005' in both the myVar and
calc columns, indicating that '3/1/2005' was successfully assigned to the
variable @.FirstOfRecMo.
The second calculation, however, fails to assign the results of the DATEADD
function to @.FirstOfNextMo. It returns NULL in the myVar column, although
the calc column correctly returns '4/1/2005'.
The other two calculations return NULL in both columns.
Is the problem with my SQL Server, perhaps? Does the code run correctly on
your computer?
Your help is very much appreciated.
"David Gugick" wrote:

> Sheldon Penner wrote:
> First thing is the date format you are using is not portable. The only
> portable formats are:
> yyyymmdd
> yyyy-mm-ddThh:mm:ss.mmm(no spaces)
> But your code works fine for me with the bad date format. Try changing
> the date format and see what you get.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||This is very creepy!
When I type the code into Query Analyzer, it produces the errors I described
in my previous messages. However, if I copy the code to this newsgroup, pos
t
it, then copy the code from the post and paste it into Query Analyzer, it
runs properly.
Evidently, SQL Server is messing with my code. Any thoughts?
"Sheldon Penner" wrote:
> I thought the problem was solved, but it's not. DATEADD behaves erratical
ly
> when given a variable as an argument. The code I sent you yesterday
> inexplicably runs correctly now, but the following example, closer to what
I
> am actually using, does not:
> ----
> declare @.ReceiverDate datetime
> declare @.FirstOfRecMo datetime
> declare @.LastOfRecMo datetime
> declare @.FirstOfNextMo datetime
> declare @.LastOfNextMo datetime
> -- Receiver Date is 3/27/2005
> set @.ReceiverDate = '20050327'
> -- First Day of Receiver Month
> select @.FirstOfRecMo = cast(cast(Year(@.ReceiverDate) as char(4)) + right('
0'
> + cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
> select myVar = @.FirstOfRecMo,
> calc = cast(cast(Year(@.ReceiverDate) as char(4)) + right('0' +
> cast(Month(@.ReceiverDate) as varchar(2)), 2) + '01' as datetime)
> -- First Day of Month after Receiver Month
> select @.FirstOfNextMo = DATEADD(m, 1, @.FirstOfRecMo)
> select myVar = @.FirstOfNextMo,
> calc = DATEADD(m, 1, @.FirstOfRecMo)
> -- First Day of Receiver Month
> select @.LastOfRecMo = DATEADD(d, -1, @.FirstOfNextMo)
> select myVar = @.LastOfRecMo,
> calc = DATEADD(d, -1, @.FirstOfNextMo)
> -- Last Day of Month after Receiver Month
> select @.LastOfNextMo = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
> select myVar = @.LastOfNextMo,
> calc = DATEADD(d, -1, DATEADD(m, 1, @.FirstOfNextMo))
> ----
> I entered the Receiver Date in the format you recommended, although in the
> actual application, the date is pulled from the database.
> The first calculation correctly returns '3/1/2005' in both the myVar and
> calc columns, indicating that '3/1/2005' was successfully assigned to the
> variable @.FirstOfRecMo.
> The second calculation, however, fails to assign the results of the DATEAD
D
> function to @.FirstOfNextMo. It returns NULL in the myVar column, although
> the calc column correctly returns '4/1/2005'.
> The other two calculations return NULL in both columns.
> Is the problem with my SQL Server, perhaps? Does the code run correctly o
n
> your computer?
> Your help is very much appreciated.
> "David Gugick" wrote:
>

Assigning data from an SQL query to a variable

Hello all,

for a project I am trying to implement PayPal processing for orders

public void CheckOut(Object s, EventArgs e)
{

String cartBusiness ="0413086@.chester.ac.uk";
String cartProduct;
int cartQuantity = 1;
Decimal cartCost;
int itemNumber = 1;

SqlConnection objConn3;
SqlCommand objCmd3;
SqlDataReader objRdr3;

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);
objConn3.Open();
objRdr3 = objCmd3.ExecuteReader();
cartProduct ="Cheese";
cartCost = 1;
objRdr3.Close();
objConn3.Close();

cartBusiness = Server.UrlEncode(cartBusiness);

String strPayPal ="https://www.paypal.com/cgi-bin/webscr?cmd=_cart&upload=1&business=" + cartBusiness;

strPayPal += "&item_name_" + itemNumber + "=" + cartProduct;
strPayPal += "&item_number_" + itemNumber + "=" + cartQuantity;
strPayPal += "&amount_" + itemNumber + "=" + Decimal.Round(cartCost);

Response.Redirect(strPayPal);

Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query.

So I want cartProduct = Title and cartCost = Price from the SQL query.

How do I do this?

Thanks

DO NOT use string concatenation like you have now. Use Parameterized Queries.

you close the SQL connection not the reader first. Then loop through the reader, get the values and assign the variables. I would definetely recommend using Try/Catch block to catch any errors.

The following code is for illustration only.

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);

Try
If objConn3.State = 0 Then objConn3.Open()

objRdr3 = objCmd3.ExecuteReader();

If DataReader.HasRows Then
Do While objRdr3 .Read()
cartProduct = objRdr3 .Item("cartProduct"))
cartCost = objRdr3 .Item("cartCost"))
Loop

End If
Catch exc As Exception
Response.Write(exc)
Finally

objRdr3.close()
If objConn3.State = ConnectionState.Open Then
objConn3.Close()
End If
End Try

Assigning a variable a table value

Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.

So, I declare a variable:
DECLARE @.variable int
Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.

I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.

Thanks in advance for any help

You need to use Select. You can assign multiple variables at the same time, but you cannot mix assignments with result sets. You can use either Set or Select to assign literal or constant values to a variable. Set only allows one variable at a time.

You can also use variables for values in a select statement.

Here is a full example:

Declare @.variable int,

@.pkey int

Set @.pkey = 42

Select @.variable = col1

From myTable

Where pkCol = @.pkey|||Thanks for your time and help it worked perfect.

Assigning a Text Variable

Here's what I've got:

select sum(item_price) as item_price, sum(item_qty) as item_qty, avg (item_price) / avg(item_qty) as average_price from x_invoic

where item_code = (item from a different table)
and status = 9

The "item from a different table" are items that I'm interesting in compiling this data for. I'm not sure what the best way to "cycle" through these items is but I would like to get data on each one of them. I was thinking of using a variable, if possible, but I'll do whatever works.

Any input is appreciated.

Regards,
TechRickUse IN and GROUP BY|||Originally posted by jzhu
Use IN and GROUP BY

Thanks!

Assigning a particular flat file record to a string variable

Hey all!

Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?

Any thoughts?

In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?

Thanks for any suggestions!

Jim Work
You could do this in the data flow using a script component to keep track of which row you are on....|||Phil, that sounds like a great plan. I am very, very, very new at this, though, and I could use a little more detail?

Going through a flat file line-by-line seems like it would be a very straightforward thing to me...
|||The data flow on its own operates on a row by row basis. That's what it's designed to do, and very fast, by the way.|||Oh, you're kidding me!

Thanks!!

Assigning 1-row & multi-column query result to local variable

Hello,
I am just new to T-SQL programming on SQL server 2000. I would like to
ask you if it is possible (like in VBA for example) to save the result
of query (1 row, but multi columns) to some "array" or "object"
variable in order to reference the concrete components (fields) of
this variable in the future code.
For example in VBA:
Dim query1 as Recordset
Set query1 = CurrentDb.OpenRecordset("ABC")
MsgBox query1!ID
MsgBox query1!Comment
Or is the only way to save the results to the temp table and use other
queries to select the required fields? This means more rows of code
and slowing of the whole calculation process.
Thank you very much for your answer.
MilanMilan,
What are you trying to accomplish?
AMB
"Milan" wrote:

> Hello,
> I am just new to T-SQL programming on SQL server 2000. I would like to
> ask you if it is possible (like in VBA for example) to save the result
> of query (1 row, but multi columns) to some "array" or "object"
> variable in order to reference the concrete components (fields) of
> this variable in the future code.
> For example in VBA:
> Dim query1 as Recordset
> Set query1 = CurrentDb.OpenRecordset("ABC")
> MsgBox query1!ID
> MsgBox query1!Comment
>
> Or is the only way to save the results to the temp table and use other
> queries to select the required fields? This means more rows of code
> and slowing of the whole calculation process.
> Thank you very much for your answer.
> Milan
>|||No arrays in SQL but you could do something like this:
select @.var1 = col1, @.var2 = col2 ... from tableName where IDcol = ...
The query must return just 1 row though for this to work. If I remember
correctly, if it returns more then 1 row, then the variables will receive
the values of the last row.
Maybe if you described what you are trying to do, then someone could find a
better solution.
"Milan" <milan_vaclavik@.centrum.cz> wrote in message
news:b4cdce36.0503090705.1f5d824e@.posting.google.com...
> Hello,
> I am just new to T-SQL programming on SQL server 2000. I would like to
> ask you if it is possible (like in VBA for example) to save the result
> of query (1 row, but multi columns) to some "array" or "object"
> variable in order to reference the concrete components (fields) of
> this variable in the future code.
> For example in VBA:
> Dim query1 as Recordset
> Set query1 = CurrentDb.OpenRecordset("ABC")
> MsgBox query1!ID
> MsgBox query1!Comment
>
> Or is the only way to save the results to the temp table and use other
> queries to select the required fields? This means more rows of code
> and slowing of the whole calculation process.
> Thank you very much for your answer.
> Milan|||SQL is a declarative language not a procedural one like VB. Storing
values from rows to variables is something you should generally try to
avoid. Instead of retrieving values and then referencing them in future
code, aim to write declarative, set-based code that operates on the
whole set of data at once. Your SQL code will be much cleaner, more
efficient and more maintainable that way. Don't try to use TSQL like it
was VB.
It is in fact possible to assign column values to variables, using a
SET or SELECT statement but variable assignment should be the exception
rather than the rule. Frequent use of variable assignment from tables
implies that you'll be using cursor based processing - a common error
made by programmers new to SQL. If you have an actual problem (the code
you posted already doesn't do anything useful that can't be achieved
with a SELECT statement) then please come back with more information so
that we can suggest an alternative.
David Portas
SQL Server MVP
--|||I would like to thank you for your replies.
I have the table with columns named like "A_01", "A_02", ..., "A_30",
"B_01", "B_02", ..., "B_30", "C_01" etc. I know it is badly designed but
I inherited it from my colleague. I have created a complex (and slow)
query which returns 1 row from this table. What I have to do now
(separately for A, B, C...) is to insert some calculated values (based
on concrete values of 01, 02,..., 30) to some other tables. For example
if A_01 = 6, I have to input A_02/6 to the A_03th, (A_03+1)th, ...,
(A_03+5)th column of some concrete table. The calculation is really very
complex.
In VBA this is a trivial task but i can not manage it easily in T-SQL.
Thank you for your ideas!
Milan
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hello Milan,
If it's easy for you to do in VBA, then why not do it in VBA? What requirem
ent
is there that you must do this in T-SQL?
Craig

> I would like to thank you for your replies.
> I have the table with columns named like "A_01", "A_02", ..., "A_30",
> "B_01", "B_02", ..., "B_30", "C_01" etc. I know it is badly designed
> but I inherited it from my colleague. I have created a complex (and
> slow) query which returns 1 row from this table. What I have to do now
> (separately for A, B, C...) is to insert some calculated values (based
> on concrete values of 01, 02,..., 30) to some other tables. For
> example if A_01 = 6, I have to input A_02/6 to the A_03th, (A_03+1)th,
> ..., (A_03+5)th column of some concrete table. The calculation is
> really very complex.
> In VBA this is a trivial task but i can not manage it easily in T-SQL.
> Thank you for your ideas!
> Milan
> *** Sent via Developersdex http://www.examnotes.net *** Don't just
> participate in USENET...get rewarded for it!
>|||I think you've realised that the root of your problem is the poor
design. I'm not sure why you would perpetuate this by creating another
table rather than do it in a view or query but anyway you may be able
to use something like this:
INSERT INTO Garbage_Out (a_01, a_o2, a_03)
SELECT I.a_01, NULL, I.a_02/I.a_01,
CASE I.a_01
WHEN 1 THEN I.a_0?
WHEN 2 THEN I.a_0?
..
END,
CASE I.a_01
WHEN 1 THEN I.a_0?
WHEN 2 THEN I.a_0?
..
END
FROM Garbage_In AS I
Fill in the question marks yourself - I wasn't clear from your
narrative which columns you would want to refer to. This "design" is
probably beyond redemption. Tables are not arrays.
David Portas
SQL Server MVP
--|||Hello Milan,

> In VBA this is a trivial task but i can not manage it easily in T-SQL.
Why not do it in VBA, then? What requirement is there that you do it in
T-SQL?
Craig

Assign XML to variable

How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?

I know that the following code works....

select * from tblUsers where userId = 1225 for XML raw

It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."

declare @.strXml nvarchar(1000)

set @.strXml = (select * from tblUsers where userId = 1225 for XML raw)

Ideas?

It is not possible to do this in SQL Server 2000 using TSQL. You cannot consume the XML output on the server-side in any form. This is possible in SQL Server 2005 since there is a native XML data type and there are lot of improvements to FOR XML clause.

assign variable within SP

I use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?
The name of the column I want to retreive the value from is "UserID"
Here's my SP so far:

ALTERPROCEDURE [dbo].[spUnregisterUser]

@.UserCodeint

AS

BEGIN

SETNOCOUNTON;declare @.uiduniqueidentifier--get useridSELECT @.uid=UserIDFROM tblUserDataWHERE UserCode=@.UserCode-- Delete userUPDATE tblUserDataSET IsDeleted='True'WHERE UserCode=@.UserCode

END

That's pretty much how you'd do it. What's not working?

assign variable value in Exists subquery

Hi
How to assign a variable value from if exist ?
like
declare @.i int
if exits( select @.i = ID from table1 where ID = 100)
-- do sth
but I always get an error
Thanks a lot for helpingAnn
You cannot do in that way.
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||Ann,
Posting the actual error would help.
Does it have to be in a subquery?
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
is easy to read/follow.
Regards
AJ
"Ann" <Ann@.discussions.microsoft.com> wrote in message news:14A79DE3-EC8D-45D9-8554-F96AF32
956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||declare @.i int
SET @.i = ( select ID from table1 where ID = 100)
if @.i IS NOT NULL ......
Although I assume you can do whatever you want to do probably simpler with a
join instead of an IF, but for that you would have to post the rest of your
code.
Jacco Schalkwijk
SQL Server MVP
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||My problem is
I have two tables
Product
Product_ID Product_Name
100 Apple
101 Peach
102 Banana
Order
Product_ID Customer_ID Quantity
100 1 5
101 1 6
Now I need to generate a report with every product and every customer. The
problem is that if nobody purchases Banana(which is 102),I need to insert
null
so it will look like
Customer_ID Product_ID Quantity
1 100 5
1 101 6
1 102 NULL
IF EXISTS(
SELECT * FROM Order WHERE Customer_ID =1) INSERT INTO
#temp(Customer_ID ,Product_ID , Quantity) SELECT Customer_ID ,Product_ID,
Quantity FROM Order WHERE Customer_ID = 1
ELSE
INSERT INTO #temp(Customer_ID ,Product_ID ,
Quantity) VALUES(1,Product_ID,NULL) -- suppose only one product here
If I use
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
I won't get 102(banana) in here
If I user
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
I'd have to select twice,that's why I am asking if possible,I can assign a
value in if exists
Thanks everyone
"Jacco Schalkwijk" wrote:

> declare @.i int
> SET @.i = ( select ID from table1 where ID = 100)
> if @.i IS NOT NULL ......
> Although I assume you can do whatever you want to do probably simpler with
a
> join instead of an IF, but for that you would have to post the rest of you
r
> code.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
>
>|||I assume you have a Customers table as well? Try:
SELECT C.customer_id, P.product_id,
COALESCE(SUM(quantity),0) AS quantity
FROM Customers AS C
CROSS JOIN Products AS P
LEFT JOIN Orders AS O
ON C.customer_id = O.customer_id
AND P.product_id = O.product_id
AND O.orderid = 10249
GROUP BY C.customer_id, P.product_id
I would think that the Orders table is denormalized if it has both the
order number and the customer id. Doesn't Order determine Customer?
David Portas
SQL Server MVP
--|||Thanks a lot,that 's what I need
"David Portas" wrote:

> I assume you have a Customers table as well? Try:
> SELECT C.customer_id, P.product_id,
> COALESCE(SUM(quantity),0) AS quantity
> FROM Customers AS C
> CROSS JOIN Products AS P
> LEFT JOIN Orders AS O
> ON C.customer_id = O.customer_id
> AND P.product_id = O.product_id
> AND O.orderid = 10249
> GROUP BY C.customer_id, P.product_id
> I would think that the Orders table is denormalized if it has both the
> order number and the customer id. Doesn't Order determine Customer?
> --
> David Portas
> SQL Server MVP
> --
>

assign variable to an XML query's result

Can I set output of an XML query to a variable (SQL Server 2000)? The
following query does not parse:
DECLARE @.Xml NTEXT
SET @.Xml =
(SELECT D.*
FROM WSDMS..Documents D
WHERE D.ID = @.DocumentID
FOR XML AUTO)
It's not possible for sql2k.
-oj
<bbla32@.op.pl> wrote in message
news:1172081331.938350.4510@.l53g2000cwa.googlegrou ps.com...
> Can I set output of an XML query to a variable (SQL Server 2000)? The
> following query does not parse:
> DECLARE @.Xml NTEXT
> SET @.Xml =
> (SELECT D.*
> FROM WSDMS..Documents D
> WHERE D.ID = @.DocumentID
> FOR XML AUTO)
>

assign variable to an XML query's result

Can I set output of an XML query to a variable (SQL Server 2000)? The
following query does not parse:
DECLARE @.Xml NTEXT
SET @.Xml = (SELECT D.*
FROM WSDMS..Documents D
WHERE D.ID = @.DocumentID
FOR XML AUTO)It's not possible for sql2k.
--
-oj
<bbla32@.op.pl> wrote in message
news:1172081331.938350.4510@.l53g2000cwa.googlegroups.com...
> Can I set output of an XML query to a variable (SQL Server 2000)? The
> following query does not parse:
> DECLARE @.Xml NTEXT
> SET @.Xml => (SELECT D.*
> FROM WSDMS..Documents D
> WHERE D.ID = @.DocumentID
> FOR XML AUTO)
>

assign variable to an XML query's result

Can I set output of an XML query to a variable (SQL Server 2000)? The
following query does not parse:
DECLARE @.Xml NTEXT
SET @.Xml =
(SELECT D.*
FROM WSDMS..Documents D
WHERE D.ID = @.DocumentID
FOR XML AUTO)It's not possible for sql2k.
-oj
<bbla32@.op.pl> wrote in message
news:1172081331.938350.4510@.l53g2000cwa.googlegroups.com...
> Can I set output of an XML query to a variable (SQL Server 2000)? The
> following query does not parse:
> DECLARE @.Xml NTEXT
> SET @.Xml =
> (SELECT D.*
> FROM WSDMS..Documents D
> WHERE D.ID = @.DocumentID
> FOR XML AUTO)
>

Assign Variable for FileName of Source File

I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.

I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.

Any ideas or suggestions?

The ForEach loop container can store the name of eac enumerated file into a variable. This article explains how to do that: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

Once its in a variable you can do pretty much anything you want with it and it seems you already know how to do that. If not, just reply here.

-Jamie

|||

Thanks for the link, but I have was able to setup a variable @.[User::FN] to the ConnectionString property associated with the flat file connection, and the looping and loading effort is working fine.

This may seem like a stupid question, and I apologize, but I cannot figure out how to reference that variable and use it in my execute SQL task that follows later and does my insert of the data into my table. What I want to do is something like this:

Insert into Table1 Select a, b, c, @.FN From Table2

The parser requires that I declare the variable and does not seem to recognize the @.[User::FN] variable.

Any suggestions?

|||

Yes, use an expression. This (sort of) shows you how:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie