Sunday, March 25, 2012
Cursor for dynamic sql
thanksCould you show give us some more detail on what is it that you are trying to
do?
I tend to avoid cursors and dynamic SQL where possible, but you are asking
for a combination of both :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"prefect" <uykusuz@.uykusuz.com> wrote in message
news:%237MeBX52FHA.3588@.TK2MSFTNGP15.phx.gbl...
is it possible to declare cursor for dynamic sql ?
thanks|||Refer to this.
http://www.sommarskog.se/dynamic_sql.html#cursor
But remember, you are digging your own grave.
--
Regards
R.D
--Knowledge gets doubled when shared
"prefect" wrote:
> is it possible to declare cursor for dynamic sql ?
> thanks
>
>|||he he :) , i was expecting that kind of response.
i am producing loginnames from name + surname according to some logic.
when a loginname repeats , i have to change the logic for each repeating
loginname therefore i have to use cursor inside a cursor but only once and
bla bla......anyway i solved my problem, thanks for your interest...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23lkb7o52FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Could you show give us some more detail on what is it that you are trying
> to
> do?
> I tend to avoid cursors and dynamic SQL where possible, but you are asking
> for a combination of both :-)
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "prefect" <uykusuz@.uykusuz.com> wrote in message
> news:%237MeBX52FHA.3588@.TK2MSFTNGP15.phx.gbl...
> is it possible to declare cursor for dynamic sql ?
> thanks
>
>
CURSOR AS PROCEDURE OUTPUT
CREATE PROCEDURE dbo.GenericCursor
@.genericCursor CURSOR VARYING OUTPUT
, @.CMD Nvarchar(1024)
AS
BEGIN
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMD,
N'@.utentiCursor CURSOR out',
@.genericCursor out
END
Everityng works fine: I want to use GenericCursor procedure and it works, but when I try to CLOSE and then DEALLOCATE the CURSOR it raises an error (as in the example):
CREATE PROCEDURE testGenericCursor
AS
BEGIN
DECLARE @.MyCursor CURSOR;
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SELECT idUtente FROM Utenti'
EXEC dbo.UtentiCursor @.MyCursor OUTPUT, @.CMDx;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @.MyCursor;
END;
-- CLOSE @.MyCursor;
-- DEALLOCATE @.MyCursor;
END
Why CLOSE and DEALLOCATEare not usable? They generate the following errors:
Msg 16950, Level 16, State 2, Line 13
The variable '@.MyCursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Line 14
The variable '@.MyCursor' does not currently have a cursor allocated to it.
I don't think you ever put the cursor into your output parameter in the first place (unless you just made a copy\paste error when putting your code into the post).
The problem is here:
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;' <<<< This won't work (see below)
exec sp_executesql @.CMD, <<<< Shouldn't this be @.CMDx?
Your going to have to pass @.genericCursor into sp_executesql as an output parameter as well, something like this:
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMDx, N'@.genericCursor cursor varying output', @.genericCursor output
The CLOSE and DEALLOCATE are in a different scope than the cursor...they don't know each other exist.
Your code revised:
CREATE PROCEDURE dbo.GenericCursor
@.genericCursor CURSOR VARYING OUTPUT
, @.CMD Nvarchar(1024)
AS
BEGIN
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMD,
N'@.utentiCursor CURSOR out',
@.genericCursor out
CLOSE @.genericCursor
DEALLOCATE @.genericCursor
END
CREATE PROCEDURE testGenericCursor
AS
BEGIN
DECLARE @.MyCursor CURSOR;
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SELECT name FROM dbo.sysobjects;'
EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @.MyCursor;
END
END
EXECdbo.testGenericCursor
This is an example from the EXECUTE (Transact-SQL) topic in Books Online that uses EXEC:
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
USE AdventureWorks;
GO
DECLARE tables_cursor CURSOR
FOR
SELECT s.name, t.name
FROM sys.objects AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @.schemaname sysname;
DECLARE @.tablename sysname;
FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN;
EXEC ('ALTER INDEX ALL ON ' + @.schemaname + '.' + @.tablename + ' REBUILD;');
FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;
END;
PRINT 'The indexes on all tables have been rebuilt.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO
A good link for dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html#cursor0
|||He is using cursor variables, which can be passed between scopes just like he is doing in dbo.GenericCursor. He just has to modify his sp_executesql call to pass the cursor variable as an output parameter.|||Thanks to evryone out there: I noticed I've posted the wrong code :-P
> SO THIS MEANS YOU ARE SO GOOD THAT YOU'VE UNDERSTOOD IT EVEN IF IT WAS WRONG!!!
Just to summarize what the matter is, this is the final working version:
CREATE PROCEDURE dbo.GenericCursor
@.genericCursor CURSOR VARYING OUTPUT
, @.CMD Nvarchar(1024)
AS
BEGIN
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMDx,
N'@.genericCursor cursor output',
@.genericCursor out
END
--
CREATE PROCEDURE testGenericCursor
AS
BEGIN
DECLARE @.MyCursor CURSOR;
DECLARE @.name as varchar(100);
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SELECT TOP 50 name FROM dbo.sysobjects;'
EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;
FETCH NEXT FROM @.MyCursor INTO @.name;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @.MyCursor INTO @.name;
SELECT @.name
END
CLOSE @.MyCursor
DEALLOCATE @.MyCursor
END
--
EXEC dbo.testGenericCursor
Thursday, March 22, 2012
Cursor and dynamic slq
like
set @.cur=cursor for exec(@.sql) ... some thing like thanksThis way:
SET @.sql = 'DECLARE c CURSOR FOR SELECT ... '
OPEN c
...
However, best practices is to avoid cursors as well as dynamic SQL...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:D9D93F32-DFD3-4207-B609-1B36654CBD02@.microsoft.com...
> hi can we use sql dynamic query with curosr
> like
> set @.cur=cursor for exec(@.sql) ... some thing like thanks
Monday, March 19, 2012
Currency Formats
different currency formats on reports? For example, we want to be able to
display in US dollars and /or Euros.Change the language property for the desired text box to English - Irish (or
whatever) and change the format to currency. btw the character code for Euro
is ALT-0128.
"nscotia17" wrote:
> Is there any dynamic way through Reporting Services where you can use
> different currency formats on reports? For example, we want to be able to
> display in US dollars and /or Euros.|||You can also hide and display fields, rows, groups based upon a visiblitiy
expression to match whatever idetifies your currency as â?¬ or $ in your data.
"nscotia17" wrote:
> Is there any dynamic way through Reporting Services where you can use
> different currency formats on reports? For example, we want to be able to
> display in US dollars and /or Euros.