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
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
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 ?
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
im running a dynamic query with open rowset in it
pseudocode:
@CMD=declare @ RETURN SELECT @RETURN =SUM(X) FROM OPENROWSET(....) SELECT @RETURN
EXEC @CMD
This pseudocode dipplay the result of @return
the problem:
capture @return into @myvalue outside the dynamic sql scope
something like
Select @myvalue=exec(@cmd)
I don't wanna run on ditributed transaction like this
insert mytable
exec(@cmd)
thanks,
joey
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
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...
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
Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below :
declare @pYear_Internal as NVarchar(100)
set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE]
WITH
MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE]
SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal)
[code]....
While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect. It looks like it doesn't recognize DECLARE keyword as it does with SQL queries. I just want a query that runs directly against the server.
I have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.
For Eg- @TableVariable
SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '
BUt it gives an error that @TableVariable must be declared
Hi all.
I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.
So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.
The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:
SET @sqlQuery = ... (build the dinamic sql query)
SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery
EXEC @cursorQuery
OPEN myCursor
FETCH NEXT FROM myCursor INTO ...
CLOSE myCursor
DEALLOCATE myCursor
This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.
My first thought was to make the cursor name unique, which led me to:
...
SET @cursorName = 'myCursor' + @uniqueUserID
SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery
EXEC @cursorQuery
...
The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.
So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.
I guess my concrete questions are:
Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?
Anybody sees another way arround this?Thanks in advance,
Carlos
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
I have a SP that returns a different dataset based on different id(parameter).
So that I can NOT get fields in the Report Builder.
Is there a way to have a "generic" Report that displays whatever it gets from the SP.
In a simple "table" format.
If not, could you, please, get me some suggestions how to address this problem.
One idea that I have in mind is to create Report Definitions dynamically...
I'd rather not to go there.. but..
Thank you very much!
How can I make this stored procedure concept work:
-----------------------------------------------
Create Procedure MyProc @MyValue varchar(5)
As
Declare @ColumnName as varchar(11)
Set @ColumnName = 'Price_' + @MyValue
Select Sum(Price) As @ColumnName --????
From MyTable where Item = @MyValue
Return
-----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
Bjorn
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?
Created a stored procedure which returns Selected table from database.
I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure
(
@condition varchar(20),
@ID bigint,
@date1 as datetime,
@date2 as datetime
)
AS
/* SET NOCOUNT ON */
IF @condition LIKE 'all'
SELECT CllientEventDetails.*
FROM CllientEventDetails
WHERE (ClientID = @ID)
IF @condition LIKE 'current_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom <= ISNULL(@date1, EventFrom)) AND
(EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE ''
SELECT CllientEventDetails.*
FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
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