Hello Again, did you miss me?
ok now i have this problem. I need to make a query that return one single data. I need to store this data in a variable. Something like that:
DECLARE @A VARCHAR(50)
@A=SELECT mycolumn FROM mytable WHERE mycolumn = 'Something'
There is a form to do it?
Thanks Friends
Hello,
I am interested in what the simplest was to get a query result that will only ever have one result (ie One column, one row) into a variable. An ugly way is to use a cursor that simply fetches the first row but that seems to be a horrible way to do it and it has sometimes major drawbacks sometimes (mainly if I have to dynamically choose the table). Surely there is a better way?
What do you think? A simple example would be nice.
Cheers
J
Is this possible as given below
declare @Qry as varchar(8000)
declare @Cnt as int
begin
set @Qry = 'select @Cnt=count(*) from ' + @TableName
exec @Qry
select @Cnt
end
But its not working....
can any one help me out in this.....
Thnx
Parag
The following query is failing when trying to apply the
MAX(field_x_order)
to the variable @max
Note the x is represented by the string variable @stri
declare @i int
declare @stri varchar(10)
declare @max int
set @i = 18
set @max = 0
while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
select @max = MAX(field_ + @stri + _ORDER) FROM table_name WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near the keyword 'IS'.
exec ('UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL')
end
I have also tried:
select MAX(field_ + @stri + _ORDER) INTO @max = FROM display_1a WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near '@max'.
and:
select @max = ('SELECT MAX(field_' + @stri + '_ORDER) FROM display_1a WHERE field_' + @stri + ' IS NOT NULL') -- error: Conversion failed when converting the varchar value 'SELECT MAX(field_19_ORDER) FROM display_1a WHERE field_19 IS NOT NULL' to data type int.
Thanks,
Lou
Lou
Hi!
Is it possible to set a query result (scalar) to scalar variable. I would like to set a qery result (SELECT COUNT(*) FROM MyTable) to a scalar variable:
DECLARE @temp int
SET @temp = query result...
Is it possible? I couldn't find the way to do that...
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
Hi:
Is there a way to assign a dynamic query result to a local variable?
declre @sqlString nvarchar(4000),
@minEventDate datetime,
@databaseName varchar(25)
selct @databaseName = 'customer_12345'
(actually, a cursor loop will assign the database name dynamically, here just to simplify the situation)
select @sqlString =
'select ' + @minEventDate + '= (select min(eventDate) from ' + @databaseName + '.dbo.tblABC)'
exec sql_executesql @sqlString
print '@minEventDate: ' + cast(@minEventDate as varchar(19))
Though the select min(eventDate) from customer_12345.dbo.tblABC
returns a date, ex. '02/01/2004 12:35 pm', however, the printed @minEventDate is always with Null value. It mean, the value was never correctly assigned to the local variable.
As an alternate way, I am using temp table to insert it with the query result and then assign to the local variable. Since I have many local variables to try to get the min, max, count for around 10 fields, perfer a way to direct assign to the local variable instead of 10 temp tables.
thanks
-D
Hi.
I am trying to store the column value to a variable from a distributed query.
The query is formed on the fly.
i need to accomplish something like this
declare @id int
declare @columnval varchar(50)
declare @query varchar(1024)
@Query = "select @columnval = Name from server.database.dbo.table where id ="+convert(varchar,@ID)
exec (@query)
print @Columnname
-MAK
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
Hi there
I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt.
how do we achieve that?
thanks and regards
Rahul Kuamr
Hi!Can anybody give me a hint how to put sa resut from EXEC into avariable.EXEC is called:EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)....)Thanks!Mario.
View 8 Replies View Relatedi need to know how many rows are in a certain table and store this value in a variable in order to process this variable later in the package. of course i can build a data flow task within a row count component but as far as i understand it's necessary to read all data from a data source in order to use the row count component. now the question is if it's possible to use a sql task in the control flow and put a select count(*) statement within the task and then write the result of this select statement into a variable. shouldn't that be much more faster than using a whole dataflow for this problem?
View 1 Replies View Related
Hi,
below is the sql statements for my web service using C#.
Code Block
string sql = "SELECT TOP 1 Pos FROM" + "TABLE1" +"ORDER BY Pos ASC"
SqlCommand comm = new SqlCommand(sql, conn);
Now if i want to set the Pos to a variable where i can call at another part of my program, how do i do that?
Hello All,
A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.
Thank you in advance
Hi this is probably a very stupid question, but I still need to know.
How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?
I have looked thru several similar threads with errors like this, but have not found a resolution. I have a SQL Task that runs this query:
Code Snippet
select NetRevenue = cast(sum(Base_Price + AL + MI + PO) as dec(10,2))
from Lancelot.DataWhse.dbo.GrossMarginDetail
where tran_date_key <= ? + ' 23:59:59' and
label_group <> 'X' and
not (ar_ship_key in ('S999991','S998101''S998102')) and
Document <> 'Cust Bal Debit Memo'
There is a parameter mapping of "User::LastSaturday" of type date. I also have a result set with a result set name of "0" with a variable name of "User::GrossMargin," which is defined as a double in the package. The task has a resultset type of Single row, and at the moment the answer that is returned is 66228637.10.
If I change the package variable to a type of string it of course works, but then I cannot do comparisons against it. I have step by step manually copied this from an existing DTSX, so I am baffled why this isn't working, and I'm ready to throw myself under a truck!
I also tried to bring it in as a string, then convert it in a script task, but I cannot figure out how to reference the input and output variables. I can't seem to find any relevant docs on how to do that...
If anyone has any ideas, I and my sanity would greatly appreciate it.
Help - I am having a moment!
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
r/ Anthony
hello
Does anybody know how to store the result of a select top 1 into a variable??
I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
But none of them work
Any ideas??
Thanks
Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)
declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output
How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable?
As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible?
For Example (I know this doesn't work, but it is effectively what I am trying to achieve):
select @result = EXEC(@sqlcommand)
I could then use the @result variable in an insert statement to update a table with the results from the EXEC command.
Any assistance would be greatly appreciated...
Regards,
Wayne
Good morning;
My Problem is :im my transaction i use insert code like the following :
" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"
In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.
" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "
I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.
Thinks.
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.
SELECT Max(Number)
FROM Employee
I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?
DECLARE @HISTORY_ID INT,
@NSTATUS VARCHAR(20),
@IMPORT_DATE DATETIME,
@TSQL VARCHAR(8000);
SET @HISTORY_ID = 350721;
SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')'
SELECT @TSQL, @HISTORY_ID;
EXEC (@TSQL) ;
Can I retrieve a result set from a sp into a variable within a Execute SQL Task?
View 23 Replies View RelatedHi,
Let's say that the query in my SQL Task returns a single integer number.
How can I put that single number in a variable?
Thank you.
Hellois there a variable that is available to me that contains the numberof rows contained in a dataset return from a database call?
have a class that runs a stored proc and returns a dataset/resultsetlooking to simply assign an integer this value if it is possible
i'm using (learning) vb.net and sql server
thanks in advance
I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!
SELECT count(*) FROM hsi.itemdata a
JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")
Hi!
I have an sproc that gives an xml result set. I want to save this to a file but by using ssis and script task. Now, I do exec usp_myProc in execute sql task and get the result as xml. (I can get it as result set too but niether has worked). Now I pass this variable to my script task User::XRset.
In the script task I create a file and then I want to write xml result of User::XRset to my file. I am at loss.
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID