Saving EXEC Result to a Variable ?

How can I save the EXEC Result to a Variable.
For example:
declare @.myString as varchar(50)
declare @.myValue as decimal(12,2)
set @.myString='Select ' + '10-5'
EXEC (@.myString)
Print @.myalue <-- Should print 5you can use sp_ExecuteSql. Check BOL for more info on this. or google it.
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:uP38IDcDFHA.3340@.TK2MSFTNGP10.phx.gbl...
> How can I save the EXEC Result to a Variable.
> For example:
> declare @.myString as varchar(50)
> declare @.myValue as decimal(12,2)
> set @.myString='Select ' + '10-5'
> EXEC (@.myString)
> Print @.myalue <-- Should print 5
>
>
>|||You can also do this using either temp tables or a user-defined function:
CREATE FUNCTION udf_test( @.value1 DECIMAL( 12, 2 ), @.value2 DECIMAL( 12, 2 )
)
RETURNS DECIMAL( 12, 2 )
AS
BEGIN
DECLARE @.result DECIMAL( 12, 2 )
SET @.result = @.value1 - @.value2
RETURN @.result
END
GO
DECLARE @.myString VARCHAR(50)
DECLARE @.myValue DECIMAL( 12, 2 )
-- Temp table way
CREATE TABLE #result ( result DECIMAL( 12, 2 ) )
SET @.myString = 'INSERT INTO #result SELECT ' + '10-5'
EXEC( @.myString )
SELECT * FROM #result
DROP TABLE #result
-- User defined function way (Make sure you set the database owner dbo to
whatever you need)
SET @.myValue = dbo.udf_test( 10, 5 )
PRINT @.myValue
DROP FUNCTION udf_test
GO
"Luqman" wrote:

> How can I save the EXEC Result to a Variable.
> For example:
> declare @.myString as varchar(50)
> declare @.myValue as decimal(12,2)
> set @.myString='Select ' + '10-5'
> EXEC (@.myString)
> Print @.myalue <-- Should print 5
>
>
>
>sql

Saving Datetime to variable

Hi im working on a simple function. I have a table with two columns
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
end
On Sun, 22 Jan 2006 06:14:02 -0800, pietro wrote:

>Hi im working on a simple function. I have a table with two columns
>(datetime) and I need to extract date rom one of them and time from another
>one - and then to join it and save it in the variable (which is declered as a
>datetime). Please, can somebody help me or advice another waz how to get it?
>many thanks
Hi Pietro,
Are these date and time columns stored as datetime or as char/varchar?
If the former, you might consider storing them together in one column.
If the latter, you might consider storing them as datetime (much better
integrity checks, much better possibilities for doing calculations) AND
consider storing them together in one column.

>CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
Why are you not returning a datetime? That's what you want to store the
combined value in, after all!

>as
>begin
>declare @.in_date as varchar
>declare @.in_time as varchar
Since you don;t specify length here, these variables are now defined at
the default length: varchar(1).

>declare @.in as char(100)
>set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
>kod=@.vz and rok=@.r )
>set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
>kod=@.vz and rok=@.r )
The use of convert in this statement suggests that the columns are
already of the datetime datatype. All this converting from datetime to
string and back won't do your performance any good - and the fact that
you didn't choose one of the recommended formats means that you're not
even assured that this will never return unexpected results.
See below for some better suggestions.

>set @.in = @.in_date +' '+ @.in_time
>return @.ret
>end
Your choice to use a user-defined functions has some performance
implications as well. I am aware of the benefits of UDF for readability,
reuseability and documentation - but are you aware of the implications
on performance?
Anyway, I promised you some alternatives. The first alternative is what
you should use if pol1 and pol2 are defined as datetime or smalldatetime
columns:
DECLARE @.result datetime -- or smalldatetime
SET @.result = (SELECT DATEADD(ms, DATEDIFF(ms, '0:00', pol2), pol1)
FROM dbo.vzorky_osr
WHERE kod = @.vz
AND rok = @.r)
The second alternative is what you should use if ALL of the following
conditions are met:
* pol1 and pol2 are both char or varchar
* pol1 is formatted yyyy-mm-dd
* pol2 is formatted hh:mm:ss or hh:mm:ss.mmm (where .mmm denotes the
milliseconds)
DECLARE @.result datetime -- or smalldatetime
SET @.result = (SELECT CAST(pol1 + 'T' + pol2 AS datetime) -- or
smalldatetime
FROM dbo.vzorky_osr
WHERE kod = @.vz
AND rok = @.r)
If col1 and col2 are (var)char but not in the format noted above, you
can either
a) use string manipulation to build a yyyy-mm-ddThh:mm:ss[.mmm] format
from whatever the current format is, or
b) concatenate them anyway, then use CONVERT with a style parameter to
ensure that SQL Server can't misinterpret the format.
Example for b:
CAST ('3/4/5' AS datetime) is dangerous, because is can be interpreted
as 3rd of april 2005, of march 4, 2005, 2003, april 5 or m,aybe yet
something else
CONVERT(datetime, '3/4/5', 11) is unambitious, since the style parameter
11 says that the japanese format (yy/mm/dd) is used.
If you need further help, then you'll have to provide more information.
See www.aspfaq.com/5006 for that.
Hugo Kornelis, SQL Server MVP

SaveCheckpoints=True affects Recordset

I have a package that creates a recordset in a variable (Type=Object, Name=CountryTable). The recordset is then picked up in a Script Task and loaded into a table using this code:

Dim adp As New OleDb.OleDbDataAdapter
dt = New DataTable

adp.Fill(dt, Dts.Variables("CountryTable").Value)

It was working fine until I turned SaveCheckpoints ON. Now it does not load any rows into the dt table. The dataflow task with the recordset destination ('CountryTable' variable) reports 10 rows in the pipeline. If I turn SaveCheckpoints OFF, it fills the dt table OK. If it cannot fill the dt table because of SaveCheckpoints being ON, shouldn't it give an error message? Thanks.

Note: I have SP1 installed.

That's weird for sure. I'll need to investigate.

When we are writing checkpoints, we also write out the value of variables to the checkpoint file - so that we can start the package again in the correct state. I can imagine an error when serializing an object variable could cause some checkpoint funkiness, but as I say, it needs more investigation.

Donald

|||

Donald,

I thought that Object variables don't get written out (unless the behaviour has changed in SP1).

-Jamie

|||

They're not and its documented in BOL now.

I hade the same issue, that my package worked up until I added checkpoints at which point it wouldn't run properly. Thought I had raised a bug but haven't

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
MichelHello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||If you have SQL Server 2005, the below query is valid. And you'lll have more
features in FOR XML.
"Michel" <michel_mueller@.bluewin.ch> wrote in message
news:ujy1pp69GHA.4708@.TK2MSFTNGP05.phx.gbl...
> Hi
> How can I save the returned XML Expression into a variable? I tried to
> this a described on the next line, but with no luck...
> I use SQL Server 2000 SP 4
> DECLARE @.doc nvarchar(4000)
> SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
> auto)
> Thank's
> Michel
>

Save the return value into a variable

Hi
How can I save the returned XML Expression into a variable? I tried to this
a described on the next line, but with no luck...
I use SQL Server 2000 SP 4
DECLARE @.doc nvarchar(4000)
SET @.doc = (SELECT * FROM sysdba.Account WHERE AccountID = 'ALFKI' FOR XML
auto)
Thank's
Michel
Hello Michel,
Can't be done in SQL Server 2000 since the XML Serialization is always done
post query there.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

save sql SUM() into a variable

I'm just wondering if we can save sql aggregate functions into a variable.

I have this query:

select company, dept, sum(pers) as pers1, sum(amount) as amount1,
sum(amount)/sum(pers) as wage
from xxtestsum
group by company, dept

instead of calling sum(amount) and sum(pers) again in "sum(amount)/sum(pers) as wage",
I would like to save them in some kinda variable in the select clause so it will save process time.

Sorry but im new to sql programming. So thx for your understanding.

If you are worried about a performance loss in that sql sentence because of the repeating sum, you shoudn't be. Sql Server's query optimized will automatically cache the results of each aggregate function, so the second call to sum(amount) will actually not force the sum aggregate to run again. That is, provided both sum aggregates are being affected by the very same group by clause.

Anyway, if you still want to get those values into a variable and you are using an sqldatasource (this forum is for that...), then you should know that you can call the "Select" method on the datasource manually. This will return you an IEnumerable object which, depending on how the datasource is configured, will either be a DataView or a DataReader. Using any of those, you can extract the full result of your query and do with it as you wish.

|||

Thanks for the clarification

save dr property to variable

how do you store a datareader propety to a variable? Below is my current code. I have already declared my connectionString and sqlComm objects, as well as the userName and such. I need to store the value from the dr to the variables, UserName, UserPass, and serverName. Thanks

Try sqlCon.Open() dr = sqlComm.ExecuteReaderWhile dr.Read userName = dr("uName").ToString LogInfo("userName = " & userName) userPass = dr("uPass").ToString LogInfo("userPass = " & userPass) serverName = dr("sName").ToString LogInfo("serverName = " & serverName)End While dr.Close()Catch obugAs Exception LogEvent("Credentials Error: " & obug.Message)Finally sqlCon.Close()End Try

You would set them exactly as you have done above (i.e. whilst inside the "dr.Read" while loop). Are you saying this doesn't work? If so, have you stepped through your code to see what is happening?

|||

Hi,

How many rows do you have in your table. If there is only one row and you want to store them into variables, its fine. However what if you have more than one rows? With each loop of your dr, your previous value in the variable will get lost.

Instead use a Generic List to store your values.

Checkthis sample.

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Got it to work. Since I had declared the variables as string, and did not fill them with anything, I was getting an error while trying to use the variables in some code after this dr object. Setting the variables to empty string resolved the errors:

Dim var AS String = ""

thanks for the responses.

save an image to a byte[] variable from a database

i'm trying to read an image file from a database(ms-sql, .mdf) with type image. Anyone have any ideas on how to do this? I have a table adapter created but could not assign it to my byte[] variable. Thanks in advance.

Hi,dreox2006 ;

try these links :

http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp

http://support.microsoft.com/kb/326502

hope helps