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)

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

View 4 Replies


ADVERTISEMENT

Is It Possible To Store The Result Of A Dynamic Query In A Local Variable

Dec 19, 2000

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

View 4 Replies View Related

Can We Set Result Of Dynamic Query To Variable?

Dec 13, 2007

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

View 1 Replies View Related

Dynamic Query, Local Cursor Variable And Global Cursors

Oct 3, 2006

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

View 3 Replies View Related

Storing The Result In A Variable Resulting From A Dynamic TSQL Query

Aug 30, 2007

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 ?

View 3 Replies View Related

Store Count Result In A Local Variable

Feb 10, 2000

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

View 4 Replies View Related

More Selecting Into Local Variable With Dynamic Select...

May 25, 2004

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

Thoughts?

View 8 Replies View Related

SQL Query Results Into Local Variable

Oct 11, 2006

Hi,

I'm trying to put the results from a SQL query that returns only one filed but one or more rows into a local variable in a comma separated format.

Any help is appreciated.

Thanks.

View 2 Replies View Related

Query Result In A Variable

Sep 5, 2006

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

View 1 Replies View Related

Obtain The Result Of Dynamic Query With Openrowset

Oct 5, 2006

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









View 7 Replies View Related

Change Local Variable Inside Query

Jul 20, 2005

/*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

View 1 Replies View Related

Using Local Variable Making The Query Slow

May 28, 2008


Hi,

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.

will there be any difference if use dynamic sql ?

View 7 Replies View Related

Simplest Way To Get A Query Result Into A Variable

May 25, 2004

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

View 11 Replies View Related

Apply The Result Of A Query That Includes Variable

Jun 19, 2008

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

View 9 Replies View Related

Setting A Value Of The Query Result To Scalar Variable

Mar 26, 2006

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...

View 1 Replies View Related

Storing Values Into Variable From A Distributed Query Result

Mar 25, 2002

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

View 2 Replies View Related

Analysis :: Dynamic Declaration Of Variable In MDX Query?

May 27, 2015

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. 

View 3 Replies View Related

SQL Server 2012 :: Table Variable In Dynamic Query?

Jul 2, 2015

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

View 8 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

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

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



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!

View 5 Replies View Related

Assing Report Parameters

Aug 1, 2007

Can you evaluate and assign a new value to a parameter in an expression for a textbox?

View 3 Replies View Related

Store Result[index] From Table In Local Var

Apr 30, 2008

Hi,

Can I take the [index] result from a table and store it in a local var?
For example a table

declare @keyWords(
word varchar(40),
wordLen smallint
)

word len
---- ---
test 4
t 1
e 1

declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords

This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)

View 2 Replies View Related

I Want To Show OpenQuery() Result With Local Table Values

May 12, 2008

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

View 2 Replies View Related

How To Assing Sequential Number To A Date Range

Oct 7, 2007

I have two tables

1. Po (Purchase Order)
2. PoDet (PoDetails)

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)


PoNo :- Po No 1
DelNo : 2

PoNo :- Po No 2
DelNo : 3

Can anybody help on this.

View 4 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

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

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

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

View 3 Replies View Related

Local Variable

May 27, 2004

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.

TIA

Fatherjack

View 3 Replies View Related

Displaying Result From Dynamic SQL

Jan 3, 2008

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!

View 1 Replies View Related

Problem With A Local Variable

Dec 19, 2001

Hello everybody,

Here I have two tables. Let's call them A and B.

A contains the folowing data: (address of persons)

PostCode Street Number CdeTour
-------- ------ ------ -------
31000 Well. 0025 NULL
31000 Well. 0050 NULL
31000 Wash. 0010 NULL

B contains the folowing data:

PostCode Street FirstEven LastEven FirstOdd LastOdd CdeTour
-------- ------ --------- -------- -------- ------- -------
31000 Well. 0002 0040 0043 0073 100
31000 Well. 0042 0084 0001 0041 200
31000 Wash. 0002 0050 0001 0049 100

* 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.

Thanks in advance.

View 1 Replies View Related

Transact SQL - Local Variable

Jun 28, 2004

I execute the following in my Query Analyzer:

Declare @Test varchar(8000)

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?

Any ideas? Thanks in advance.

View 2 Replies View Related

Text Local Variable

Jan 17, 2007

Hi All,

How to declare TEXT datatype as local variable . My data is getting truncated as it cross varchar(8000) characters.

Saju

View 1 Replies View Related

Local Variable Slows Down SP (?)

Dec 17, 2007

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]))

END

View 4 Replies View Related

T-SQL (SS2K8) :: Set A Local Variable

Feb 24, 2015

declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@sql3 varchar(8000),

[Code].....

i need to set a Apostrophe in front of dir and at the end

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved