MS SQL Server :: Dynamic Query, Local Cursor Variable And Global Cursors

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

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 View Related

Local Or Global Variable In An Interaction Session Of The SQL Analyzer

Jul 20, 2005

Hi,I am new to SQL. Please bear with me and allow me to ask a dumbquestion.I am debugging a stored procedure (written in Trans-SQL), and I foundthat the SQL analyzer that I use doesn't have a debugger. All I cando it is execute a block of code and see what is going on in aninteraction seesion of the SQL analyzer. I would need to declare somevariable to hold values of the previous query in the interactivesession.I understand that this can be easily done in a stored procedure viathe Declare command (e.g., Delcare @order_no int). Is similarfunctionality exists in an interaction session of the SQL analyzer?If so, what is the command. Please advise.Thank you very much for the help.Alex

View 1 Replies View Related

SSIS Equivalent For Dynamic Properties Global Variable Example

Apr 17, 2007

Hi,

I have a dts package that currently uses a dynamic properties task to set the values of global variables. Each variable is based on the value of a query to the database.

I am in the process of migrating this dts package to SSIS but cannot find an equivalent function. I have looked at property expressions but cannot get this working the same way.

Any help would be appreciated.

Thanks

Lyn

View 11 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;

I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

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

Declare Cursor Based On Dynamic Query

Sep 18, 2006

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

This code is not working.

please suggest

Nitin

View 12 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

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

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

Feb 27, 2008

I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.

Here are the task steps.

[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.

If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.

CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile

The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False

Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.

General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True

I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output

Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1

Result Set is empty.
Expressions is empty.

When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.

When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?

The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.

Thanks for your help.

View 5 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Accessing Global Cursor

Mar 3, 2004

hi friends,

Here is the stored procedures that I used.

------------------------------------------------------------------------------------
create procedure globalCursor
AS
DECLARE abc CURSOR GLOBAL FOR
select * from sales
OPEN abc

create procedure globalCursorTest
AS
DECLARE @sdate datetime
DECLARE @sperson varchar(15)
DECLARE @sregion varchar(15)
DECLARE @sales int
EXECUTE globalCursor
FETCH NEXT FROM abc INTO @sdate, @sperson, @sregion, @sales
print @sdate
print @sperson
print @sregion
print @sales
------------------------------------------------------------------------------------

When I execute globalCursorTest using SQL Query Analyser, it says

------------------------------------------------------------------------------------
Server: Msg 16915, Level 16, State 1, Procedure globalCursor, Line 4
A cursor with the name 'abc' already exists.
Server: Msg 16905, Level 16, State 1, Procedure globalCursor, Line 5
The cursor is already open.
------------------------------------------------------------------------------------

how to solve this? or in other words, how to simply create the procedure in the database without executing it, as i can see the execution of the first procedure globalCursor causes this problem.

Jake

View 8 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

NT Security: Global Vs. Local Groups

Mar 5, 2001

Hello,
In NT MS suggests putting global groups into local groups and then assigning object permissions to those local groups in NTFS. I was wondering if this pattern should be followed in SQL server when assigning permissions to integrated login accounts. Is it better to use global groups or local groups?

Thanks
JJ

View 1 Replies View Related

Temp Tables, Global And Local

Jul 20, 2005

Can anyone tell me or post a link that says how many global temptables can exist SQL Server 2000? Also, is there a limit to thenumber of local temp tables that can exist?Thanks,Billy

View 1 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

Local Vs. Global Uniqueness And General Tagability

Jun 17, 2008

So, I have this idea of a database design I was hoping to get some input on.
I usually go through the process of creating a separate column for identity, like an auto-increment integer which serves as a context specific ID that easily can be passed around. However one of the short-comings I've come to realize is that the integer is just that, unique to the context and requires context information to make sense.
I'm pounding the idea of creating a separate table whose sole purpose is to provide global (database wide) unique IDs. As such each row in a table can still have an ID but instead of being tied to a specific context it will always make sense within the application. A foreign key constraint can ensure that and a relation.
Now, with a global ID which I can create FKs to, I don't have to create a separate table for relations between entities and can have relations through these global IDs and I think that's a good thing because if there are many relations between many entities they don't have to be defined more than once. There is also a single "hub" where all relations fit which will allow me to access most information through some relatively simple joins.
My concerns are performance and Linq to SQL implication. I'm worried that Linq to SQL might start pulling an excessive amount of data and I'm worried that the practicality of such a design might hurt the long running of the application development process.
But I'm surprisingly interested in what it could mean for a more ubiquitous view of data and tagging. Basically a way of any data to have relations to any other data through this yet simple design decision.
If you think this is a good idea or see no particular problem with, please let me know! If you think there's problems with my idea, please motivate your critic but do tell me what I should look out for, or if you know of any better approach to my idea of being able to look at the data through some more common mechanism (which applies to all tables and the entire database mostly from a programming perspective).

View 2 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

Dynamic Cursors...

Jun 24, 2002

Hi!

I am trying to build a Cursor using an string I am passing into a Stored Procedure. This string I am passing contain my Filters parameter of the query.

Example: Set sSQL = ' And LName like ' + '''S% '''

I am passing sSQL into a stored procedure.

I want to be able to use that when building the cursor.

Something like;

DECLARE SampleCrsr CURSOR FOR
SELECT empl, lname
FROM stm_employee
Where Empl <> NULL + sSQL <-- This does not work.

Or something like this.

The idea is to build something like a Dynamic Cursor. I have done this in Sybase and I think ORACLE. U need help for SQL SEVER.

Do you have any idea how to go around or make it happen???

View 1 Replies View Related

Cursors With Dynamic SQL

Sep 22, 2005

Hi!

I'm moving from firebird to MS SQL and have many problems. In firebird, I did many things with FOR...DO..SUSPEND loops. That loop enabled me to scan through the result set, do many calculations and create new result set (defined as variabled in RETURNS part of procedure and actually returned with SUSPEND).

In MS SQL, I can accomplish same thing with cursors. I did read cursors are slow, but I really can't imagine how to perform all this calculations without them (writing software for production planning).

One thing I couldn't do. Declaring cursors with dynamic SQL (in firebird that was for...execute statement(statement) do.

Later I found out about sp_cursoropen and related procedures, but that get's me with "Only functions and extended stored procedures can be executed from within a function."

Here is the code of function with latest modifications to sp_ procedures. Note: This is REALY out-of-context procedures since it is part of 18 procedures which together creates rough production plan.

How to make this function work?

CREATE function [dbo].[plan_narudzbe] (
@koja_narudzba int,
@tip int)

returns @ReturnTable table (
ID_NARUDZBA_STAV int,
ID_ARTIKL VARCHAR (20),
KOLICINA float,
ID_RADNI_PLAN int,
TERMIN_NARUDZBE DATETIME)
as

begin
declare
@U_PROIZVODNJI float, @RASPISANI_PLAN float, @D float, @STR VARCHAR(1000),
@NARUDZBA_PLAN_FINI_PLAN int, @STATUS_NARUDZBE SMALLINT,

@ID_NARUDZBA_STAV int, @ID_ARTIKL VARCHAR (20), @KOLICINA float,
@ID_RADNI_PLAN int, @TERMIN_NARUDZBE DATETIME

SELECT
@NARUDZBA_PLAN_FINI_PLAN = VRIJEDNOST
FROM PARAMETRI
WHERE NAZIV='NARUDZBA_PLAN_FINI_PLAN'

set @NARUDZBA_PLAN_FINI_PLAN=COALESCE(@NARUDZBA_PLAN_FINI_PLAN,0);
if ((@TIP=1) AND (@KOJA_NARUDZBA IS NULL)) return;
set @STR= '
SELECT Q.ID,Q.ID_ARTIKL,
COALESCE(Q.POTVRDA_KOLICINA,Q.KOLICINA,0) AS KOLICINA,
COALESCE(Q.POTVRDA_TERMIN_ISPORUKE,Q.TERMIN_ISPORUKE) AS TERMIN,
N.STATUS ,
SUM(RN.KOLICINA_NALOGA) AS U_PROIZVODNJI
FROM NARUDZBA_STAV Q
LEFT JOIN RADNI_NALOG RN ON (RN.ID_NARUDZBA_STAV=Q.ID)
LEFT JOIN NARUDZBA N ON (Q.ID_NARUDZBA=N.ID)
WHERE Q.STATUS NOT IN (2,5,8,9) ';
if (@KOJA_NARUDZBA IS NOT NULL) set @STR=@STR+' AND Q.ID='+cast(@KOJA_NARUDZBA as varchar)+' ';
set @STR=@STR+' GROUP BY Q.ID,Q.ID_ARTIKL, COALESCE(Q.POTVRDA_KOLICINA,Q.KOLICINA,0),
COALESCE(Q.POTVRDA_TERMIN_ISPORUKE,Q.TERMIN_ISPORUKE), N.STATUS ';

-- Create a dynamc read-only cursor

DECLARE @cursor INT
EXEC sp_cursoropen @cursor OUTPUT, @STR, 2, 8193

-- Name the cursor
EXEC sp_cursoroption @cursor, 2, 'narudzba_stav'

DECLARE @narudzba_stav CURSOR
EXEC sp_describe_cursor @narudzba_stav out, N'global', 'narudzba_stav'

while (1=1) begin
fetch next
from narudzba_stav
INTO
@ID_NARUDZBA_STAV, @ID_ARTIKL, @KOLICINA, @TERMIN_NARUDZBE, @STATUS_NARUDZBE, @U_PROIZVODNJI
if (@@fetch_status<>0) break;

set @KOLICINA=@KOLICINA-COALESCE(@U_PROIZVODNJI,0);
if (@STATUS_NARUDZBE=12) BEGIN
if (@NARUDZBA_PLAN_FINI_PLAN=1) BEGIN
set @RASPISANI_PLAN=NULL;
SELECT
@RASPISANI_PLAN = SUM(KOLICINA)
FROM NARUDZBA_STAV_PLAN
WHERE ID_NARUDZBA_STAV_PLAN=@ID_NARUDZBA_STAV
set @KOLICINA=@KOLICINA-COALESCE(@RASPISANI_PLAN,0);
END
ELSE set @KOLICINA=0;
END
if (@KOLICINA>0) BEGIN
set @ID_RADNI_PLAN=NULL;
SELECT TOP 1 @ID_RADNI_PLAN = ID
FROM RADNI_PLAN
WHERE ID_ARTIKL=@ID_ARTIKL
ORDER BY AKTIVAN_PLAN DESC,ID DESC

insert @ReturnTable values (
@ID_NARUDZBA_STAV, @ID_ARTIKL, @KOLICINA, @ID_RADNI_PLAN, @TERMIN_NARUDZBE)
END
END
-- Close the cursor
EXEC sp_cursorclose @cursor

return
end

View 20 Replies View Related

Dynamic SQL And Cursors

Mar 7, 2008

Hi All

I wrote the following script. Its gets list of tables left joint to compare data between 2 table and insert the difference.
My problem is its taking for ever to Run.

What could be the problem.

CREATE PROCEDURE [dbo].[Update_D_Tables_InBlake_DBN12]

AS
BEGIN
DECLARE @TableName VARCHAR(255)
DECLARE @Sql VARCHAR(MAX)
DECLARE @Column VARCHAR(1000)
DECLARE @ID VARCHAR(255)

DECLARE TableCursor CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name in ('D_Address',
'D_AddressBankaccount',
'D_AddressCustomer',
'D_AddressEmployer',
'D_Application',
'D_ApplicationCustomerPri',
'D_ApplicationCustomerSec',
'D_ApplicationDetail',
'D_ApplicationDetailStatusHistory',
'D_ApplicationDetailUpdateHistory',
'D_ApplicationHistory',
'D_ApplicationQuestions',
'D_ApplicationSendReports',
'D_ApplicationStatusHistory',
'D_BankAccount',
'D_BlackHorseBranch',
'D_BlackHorseResponses',
'D_BrokerReferralInfo',
'D_BrokerWorkflow',
'D_Calls',
'D_CallTable',
'D_CCJs',
'D_CitiResponses',
'D_Contact',
'D_ContactCustomer',
'D_ContactEmployer',
'D_Customer',
'D_DataExtracts',
'D_DNCList',
'D_DuplicateApps',
'D_DuplicateMatches',
'D_EDLResponses',
'D_Employer',
'D_ExistingCredit',
'D_FinancialCustomer',
'D_FinancialStatements',
'D_HomeownerDetails',
'D_IncompleteResults',
'D_IncompleteRules',
'D_LenderOtherDetail',
'D_LenderOwnerRoles',
'D_LenderPlans',
'D_LenderResponse',
'D_LenderSender_Report',
'D_LenderStatusRole',
'D_LenderStatusRoleAssign',
'D_LoanDetails',
'D_Mortgage',
'D_Occupations',
'D_OccupationsCustomer',
'D_PhoneLogin',
'D_PhoneLogin_Skills',
'D_ProvidentResponses',
'D_ReportAllianceLeicHistory',
'D_SkillSet',
'D_Tenancy',
'D_Users',
'D_WelcomeResponses'
)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @Line VARCHAR(1000)

SET @Line = ''
SET @ID = ''

SELECT top 1 @ID = @ID + QUOTENAME(c.Column_Name)
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName

SELECT @Line = @Line + 'b.'+ QUOTENAME(c.Column_Name) + ' , '
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName

SELECT @Column = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @Sql = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @Sql = 'INSERT'+ ' '
SELECT @Sql = @Sql + 'INTO Staging.dbo.'+ @TableName +' ' + 'SELECT' + ' ' + @Column
SELECT @Sql = @Sql + ' '+ 'FROM MyAPOLLO_101.dbo.'
SELECT @Sql = @Sql + @TableName + ' ' +'B' + ' '
SELECT @Sql = @Sql + 'LEFT JOIN' + ' '
SELECT @Sql = @Sql + 'Staging.dbo.'
SELECT @Sql = @Sql + @TableName + ' ' +'C'+ ' '
SELECT @Sql = @Sql + 'ON' + ' '
SELECT @Sql = @Sql + 'B.'+ @ID + ' '+ '=' + ' ' + 'C.'+ @ID + ' '
SELECT @Sql = @Sql + 'WHERE ' + ' ' + 'C.'+ @ID + ' ' + 'IS NULL' --Edited 04/03

EXEC (@Sql)
PRINT @TableName
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor
END

View 3 Replies View Related

Cursors And Dynamic SQL

Jul 23, 2005

On this site:http://www.sommarskog.se/dynamic_sql.htmlI have found an example how to use cursor with dynamic SQL:DECLARE @my_cur CURSOREXEC sp_executesqlN'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN@my_cur',N'@my_cur cursor OUTPUT', @my_cur OUTPUTFETCH NEXT FROM @my_curBut when I tried to do this:IF (@Naziv <> '')SET @sql_where = @sql_where + N' AND Naziv LIKE ' + @NazivIF (@Funk <> '')SET @sql_where = @sql_where + N' AND Funkcija LIKE ' + @FunkIF (@Mj <> '')SET @sql_where = @sql_where + N' AND NazivMjesta LIKE ' + @MjIF (@Drz <> '')SET @sql_where = @sql_where + N' AND (drzava1 LIKE ' + @Drz +' OR drzava2 like ' + @Drz + ' OR drzava3 LIKE ' + @Drz + ')'DECLARE @CursSearch CURSORSET @sql = N'SET @CursSearch = CURSOR FORSELECT CvorID, NadCvorID,IzvorisniCvorID, Naziv, TipCvora,NasljednaLinija, Funkcija, NazivMjesta,drzava1, drzava2, drzava3FROM dbo.PretrazivanjeWHERE NasljednaLinija LIKE @NasljednaLinija'+ @sql_where + N'; OPEN @CursSearch'EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',@CursSearch OUTPUT....by fetching cursor i got this message:The variable '@CursSearch' does not currently have a cursor allocated to it.Can anybody tell me what i did wrong?

View 5 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

Out Variable In Cursors

Apr 17, 2008

i want to store the output in out variable .Which gives multiple values.

create procedure usp_test (@AccountID INT)
as
begin
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
end

i get nearly ten rows in the print statement
how can i assign the output to a out variable where i get all ten rows.

Any ideas or suggestions.

View 1 Replies View Related

Sync/Merge Local Sql Compact Databases To Single Global Database

Jun 23, 2007

Hi, I have compact sql databases which will be local on multiple users
devices. Due to space constraints, for one of the tables i have had to use
auto incrementing integer which works fine for the local database but i
would like to merge all of the users databases into a global database. The
table format can be seen below:

Code SnippetCREATE TABLE Players
(
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
FirstName NVARCHAR(32),
LastName NVARCHAR(32)
);

CREATE TABLE Sessions
(
ID INTEGER NOT NULL IDENTITY,
PlayerID UNIQUEIDENTIFIER ,
SessionDateTime DATETIME,
CONSTRAINT pkSessions PRIMARY KEY (ID),
CONSTRAINT fkPlayerID FOREIGN KEY (PlayerID) REFERENCES Players(ID)
);

CREATE TABLE SessionDetail
(
SessionID INTEGER,
Time real,
Power real,
CONSTRAINT pkSessionDetail PRIMARY KEY (SessionID,StrokeTime),
CONSTRAINT fkSessionID FOREIGN KEY (SessionID) REFERENCES Sessions(ID)
);

The Players table will merge fine as GUIDs are used. However, how will the
sync capabilities of compact SQL handle the sessions table? When it pushes
the local data to the remote database will it change the Sessions.ID column
to a unique field (as no doubt lots of people will have 1, 2, 3 in their
local databases and the global database must have a unique ID), and then
transfer this changed ID back to the local database? Furthermore, if it
changes the Sessions.ID column during the merge it will also need to update
the SessionDetail.SessionID foreign key to maintain referential integrity,
is this also handled?

So my question is, how much of this sync and data merge is automated and are
there any good examples or pointers for my scenario? I cannot reasonably use
a GUID for Session.ID as there will be lots of SessionDetail entries and the
size would be far too much.

Many thanks,

Chris

View 7 Replies View Related

Global Variable

Apr 23, 2007

Hi All,I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an errorThe element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. I've seen some examples online to get global varaibles in task script and all of them display the same code Any idea Franck

View 1 Replies View Related

SQL Global Variable

Apr 19, 2001

How to create my own global variable and set its value. In another word, i want to set the value of a variable in one sp and want another sp to see its value.

thanks

Michael

View 2 Replies View Related

Global Variable

Feb 20, 2003

Is there a way to declare a global variable that can be used in seperate stored procedures?

I have a statistics table that tracks the inserts and updates for several stored procedures.

I need to have the procedure_A run and insert a count of inserted and updated rows.

Procedure_B needs to identify the row inserted by procedure_A and update the columns with a count of how many it inserted and updated.

Procedure_C needs to do the same as procedure_b.

I want to pass a global variable with the id of the row inserted by procedure_A to the rest of the procedures so that they will know which row to update. How do I do this? Can I?

Thanks.

View 3 Replies View Related

Global Variable

May 27, 1999

Hi all,
I have a user using this tool, which creates a temporrary storeproc in tempdb.
when I don't give him permission on temp_db, it errors out and says you should create a system variable to point to a different database to create this sp.

Can anyone kindly explain to me how could this be done?
I appreciate any comment on this thread.

Thanks in advance.
Jay

View 1 Replies View Related

Global Variable

Aug 22, 2006

Dear All!

I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.

Please provide a smal example.

Regards,
Shabber.

View 6 Replies View Related

Global Variable??

Nov 1, 2007

I have several stored procedures and UDFs that have code that convert between GMT timezone and other U.S. timezones.

for example,
to convert from GMT to CT I use this:
@dtmCentral = dateadd(hour, -5,@GMTdtm)

However, come Nov 4th after 2AM, when DST ends, I need to change this to
@dtmCentral = dateadd(hour, -6,@GMTdtm)

Assuming that I can store this -5 or -6 value in a global variable, I want to have a stored procedure that updates it when DST starts and ends.

Is there a better way of doing this other than the option of going through the entire calculations of figuring out whether it's DST or not each time ?

Thanks.

View 6 Replies View Related