Dynamic SQL in Cursor

Hi,
I am trying to execute a dynamic sql statement using a cursor. Can it
be done?
Thanks for your help
Moshe
SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
PAY_OTH, VAT_OTH, COUNTRY, BAND
FROM ' + @.TABLE +
' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
ORDER BY FDATE, FTIME'
DECLARE CALL_CURSOR CURSOR FOR
EXECUTE @.SQL
OPEN CALL_CURSOR
FETCH NEXT FROM CALL_CURSOR INTO
etc....
I get the error...
Incorrect syntax near the keyword 'EXECUTE'
Perhaps EXECUTE (@.sql)
<mosheallen@.gmail.com> wrote in message
news:1143983296.804707.42310@.i39g2000cwa.googlegro ups.com...
> Hi,
> I am trying to execute a dynamic sql statement using a cursor. Can it
> be done?
> Thanks for your help
> Moshe
> SET @.SQL = 'SELECT FMONTH,FDATE, FTIME, DIALED, CONVERT(INT, FLONG /
> 60) AS MINUTES, CONVERT(INT, FLONG) % 60 AS SECONDS, FLONG, PAY_TIME,
> VAT_TIME, SNCODE, SERVICE, CATEGORY, PLMNAME, DIRECT_NUM, DEST_NUM,
> PAY_OTH, VAT_OTH, COUNTRY, BAND
> FROM ' + @.TABLE +
> ' WHERE(DIRECT_NUM = ' + @.PHONE_NUMBER + ')
> ORDER BY FDATE, FTIME'
> DECLARE CALL_CURSOR CURSOR FOR
> EXECUTE @.SQL
> OPEN CALL_CURSOR
> FETCH NEXT FROM CALL_CURSOR INTO
> etc....
> I get the error...
> Incorrect syntax near the keyword 'EXECUTE'
>
|||Thanks, I tried that.
|||So, does it work?
<mosheallen@.gmail.com> wrote in message
news:1143986530.107966.99980@.j33g2000cwa.googlegro ups.com...
> Thanks, I tried that.
>
|||No, it doesn't work. Can you make a cursor with dynamic sql?
Thanks for your help
|||Yes, see the below example
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
<mosheallen@.gmail.com> wrote in message
news:1143987937.829636.183710@.t31g2000cwb.googlegr oups.com...
> No, it doesn't work. Can you make a cursor with dynamic sql?
> Thanks for your help
>
|||I don't really understand how to use what you sent me for my code.
|||I did not send you it for your code. I just gave an example how to build the
dynamic SQL. I hope you got the idea.
<mosheallen@.gmail.com> wrote in message
news:1143989823.538912.246360@.v46g2000cwv.googlegr oups.com...
>I don't really understand how to use what you sent me for my code.
>
|||I didn't get the exact idea, no. I deceided anyway to use a select into
using dynamic and run the cursor off that table. Thanks for your help
and erev tov
|||Cursors and dynamic SQL are considered the worst possible programming
practices. You put all your code in uppercase to make it hard to read,
you used proprietary syntax when Standard syntax is available, and your
design is so screwed up that you do not know the name of the table
until runtime. You have columns with vague nams like "service" (code?
name?date?) or "country" (iso code? name? population?)
You need to start over, if you really want to get it right. If you do
not care about being a good programmer, then use any of the kludges you
will get on newsgroups. I will bet that you are creating tables with
identical structures and slightly diffrerent names.
That is a tape file system. Cursor statements mimic all of the 1950's
IBM tape file commands, so you can write the same code you understand
from 50 years ago and never have to learn RDBMS. You can OPEN a cursor
just like you did an open on a channel.
Too bad. Your porograms will run 2-3 orders of magnitude slower than
they should, have no data integrity, etc.