Assing Dynamic Query Result To A Local Variable....
Jul 9, 2004
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)
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.
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
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,
Not wishing to derail the other recent thread on loading a local variable, I've posted this query (hee,hee,hee...I kill me) on a separate thread...though I think I am trying to do something similar...that is to build a dynamic select statement, but return a count of the rows it finds/doesn't find to a local variable...using the (amazingly timely) responses above, I tried this:
Note that the local variables @TargetDate and @TLevel are necessary because they are being passed into the procedure as variables....
DECLARE @SQLCmd varchar(256) DECLARE @TargetDate smalldatetime DECLARE @TLevel int DECLARE @n int SET @TargetDate = '2004-05-24' SET @TLevel = 1
SET @SQLCmd = 'SELECT @n = count(*) FROM EventLog WHERE ((CONVERT(varchar(10), [Date], 101) = ''' + CONVERT(varchar(10), @TargetDate, 101) + ''') AND (MsgLevel = ' + CONVERT(varchar(3), @TLevel) + '))' exec (@SQLCmd) if @n > 0 print 'yep' else print 'nope'
and, it's TRYING to work...but apparently the local variable @n is not recognized in the execution of the dynamic statement, as this is the output: Server: Msg 137, Level 15, State 1, Line 1 Must declare the variable '@n'. nope
/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter
I am using a local variable to capture datetime and then select records from another table by making use of the above local variable result. But the query is running too slow when I use the local variable
declare @a smalldatetime select @a=last_run_time from job_status where job_des='sample'
SELECT * FROM History WHERE CHANGE_DATE> @a
Instead of the above select statement if I use the below statement it is returning results quickly. Can someone help me in tuning the above query. SELECT * FROM History WHERE CHANGE_DATE> '5/23/2008 6:22:00.000 AM '
History table has columns ( case number, change_date, change_desc). I have two indexes defined on the above table one is on case_number and the other on change_date.I tried using force index but still the query is running slow.
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.
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.
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 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
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 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
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.
Hi, I have two queries as under:QUERY 1:SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS DocsQUERY 2:SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, AttachmentFrom Dept_LegalLawINNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegIDINNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawIDWHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).Try many thing but no success yet.Need urgent help.Thanks
Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).
PoDet is child table having PoNo and PoQty
Suppose following records are in both tables
Po:-
OrderNo : 1 PoNo :- Po No 1 ShipDate :- Oct 5, 2007
OrderNo : 1 PoNo :- Po No 2 ShipDate :- Dec 5, 2007
OrderNo : 1 PoNo :- Po No 3 ShipDate :- Oct 5, 2007
PoDet:
PoNo :- Po No 1 PoQty :- 2000
PoNo :- Po No 2 PoQty :- 3000
PoNo :- Po No 3 PoQty :- 4000
I want to generate a Delivery No. which will be generated in this way :-
1. Earlier Shipdates should be assigned a Lower Number 2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number
So meeting the above two conditions, a single SQL should return
PoNo :- Po No 3 DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)
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
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?
I am trying to create a procedure or function that will deal with weekly information for staff records.
Rather than declaring 14 local variables for the information, is it possible to declare a local variable as an array? Sorry for using VB terminology - not sure how SQL would describe it.
* My problem is that I want to update the field A.CdeTour depending on the data present in B.
Let's take the first line from the table A: the number of the street 'well.' is '25', and '25' is odd and between '1' and '41' which meens that I should update my field A.CdeTour should be set to '100' (B.CdeTour). Let's take another example, so in the second line: the street is still 'well.' but here the number is '50'. '50' is even and between '42' and '84' so the field A.CdeTour should be set to '200' (B.CdeTour).
Here I wrote a query, which doesn't work properly:
DECLARE @Num VARCHAR(4) UPDATE A SET @Num = Number, CodeTournee= CASE WHEN @Num % 2 = 0 THEN( -- even number of street SELECT CdeTour FROM B WHERE @Num BETWEEN FirstEven AND LastEven AND A.PostCode = B.PostCode AND A.Street = B.Street )ELSE( -- odd number of street SELECT CdeTour FROM B WHERE @Num BETWEEN FirstOdd AND LastOdd AND A.PostCode = B.PostCode AND A.Street = B.Street )END FROM A, B
The query runs but the problem is that it doesn't update the field, because it doesn't interpret @num by its value.
In fact, if instead of @num, I hard-code a value it works ... WHERE '0025' BETWEEN FirstOdd AND LastOdd ...
Can someone help me???? I would be very pleased, if someones could give me another way of doing it or a trick in order to avoid this problem.
Set @Test='SELECT VIOXX_LastName + '' + VIOXX_FirstName + '' + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number FROM tblPlaintiff WHERE VIOXX_Number NOT IN(SELECT VIOXX_Number FROM tblCase_Plaintiff) OR VIOXX_Number IN (SELECT tblCase_Plaintiff.VIOXX_Number FROM tblCase INNER JOIN tblCase_Plaintiff ON tblCase.Case_Number = tblCase_Plaintiff.Case_Number WHERE (tblCase.Status = ''InActive'')) ORDER BY VIOXX_Number, VIOXX_LastName' Select @Test
and get the following result: SELECT VIOXX_LastName + ' + VIOXX_FirstName + ' + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number FROM tblPlaintiff WHERE VIOXX_Number NOT IN(SELECT VIOXX_Number FROM tblCase_Plaintiff) OR VIOXX_Number IN (SELECT
the latter part of my original text is not stored in the variable. Is there some limitation on the number of characters for a local variable in transact sql?
Hi guys I am sitting and testing som variants of this simple SP, and I have an question that I couldent answer with google or any thread in this forum.
Perhaps I am doing something really easy completly wrong here.
Why does the local variables in the first code segment slow down the overall execution of the procedure? Dont mind the logic why I have them there are only testing som things out.
If i declare two variables the same way: DECLARE @v INT SET @v = 100
When I use it in a WHERE CLAUSE: ...WHERE [V] BETWEEN @v AND @x) Is there any different then ...WHERE [V] BETWEEN 100 AND 200)
Cant figure this out, why does it hurt the performance so bad? As a C# guy its the same thing ?
Thanks in advance /Johan
Slow
ALTER PROCEDURE [dbo].[spStudio_Get_Cdr] @beginDate DATETIME = null, @endDate DATETIME = null, @beginTime INT, @endTime INT, @subscribers VARCHAR(MAX), @exchanges VARCHAR(MAX) = '1:', @beginDateValue int, @endDateValue int AS BEGIN SET NOCOUNT ON;
DECLARE @s INT SET @s = @beginDateValue DECLARE @e INT SET @e = @endDateValue print @s print @e
DECLARE @exch TABLE(Item Varchar(50)) INSERT INTO @exch SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
DECLARE @subs TABLE(Item Varchar(19)) INSERT INTO @subs SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
SELECT [id] ,[Abandon] ,[Bcap] ,[BlId] ,[CallChg] ,[CallIdentifier] ,[ChgInfo] ,[ClId] ,[CustNo] ,[Digits] ,[DigitType] ,[Dnis1] ,[Dnis2] ,[Duration] ,[FgDani] ,[HoundredHourDuration] ,[Name] ,[NameId] ,[Npi] ,[OrigAuxId] ,[OrigId] ,[OrigMin] ,[Origten0] ,[RecNo] ,[RecType] ,[Redir] ,[TerId] ,[TermAuxId] ,[TermMin] ,[Termten0] ,[Timestamp] ,[Ton] ,[Tta] ,[Twt] ,[Level] FROM [dbo].[Cdr] AS C WHERE (C.[DateValue] BETWEEN @s AND @e) AND (C.[TimeValue] BETWEEN @beginTime AND @endTime) AND EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level]) AND (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
END
Fast
ALTER PROCEDURE [dbo].[spStudio_Get_Cdr] @beginDate DATETIME = null, @endDate DATETIME = null, @beginTime INT, @endTime INT, @subscribers VARCHAR(MAX), @exchanges VARCHAR(MAX) = '1:', @beginDateValue int, @endDateValue int AS BEGIN SET NOCOUNT ON;
DECLARE @exch TABLE(Item Varchar(50)) INSERT INTO @exch SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
DECLARE @subs TABLE(Item Varchar(19)) INSERT INTO @subs SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
SELECT [id] ,[Abandon] ,[Bcap] ,[BlId] ,[CallChg] ,[CallIdentifier] ,[ChgInfo] ,[ClId] ,[CustNo] ,[Digits] ,[DigitType] ,[Dnis1] ,[Dnis2] ,[Duration] ,[FgDani] ,[HoundredHourDuration] ,[Name] ,[NameId] ,[Npi] ,[OrigAuxId] ,[OrigId] ,[OrigMin] ,[Origten0] ,[RecNo] ,[RecType] ,[Redir] ,[TerId] ,[TermAuxId] ,[TermMin] ,[Termten0] ,[Timestamp] ,[Ton] ,[Tta] ,[Twt] ,[Level] FROM [dbo].[Cdr] AS C WHERE (C.[DateValue] BETWEEN @beginDateValue AND @endDateValue) AND (C.[TimeValue] BETWEEN @beginTime AND @endTime) AND EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level]) AND (EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
