What are the challenges you face when working across database platforms? Take the survey
Options

Problem with declared variables

wdwd Posts: 25
edited July 23, 2007 11:10AM in SQL Prompt Previous Versions
Hi,

I recently installed SQLPrompt 3.5 RC and have run into a strange issue.

I declared a few variables for a stored procedure and went on with my code and wanted to start setting these variables. After typing the set command I waited for the variables to all be displayed by SQLPrompt, but only a couple of them were shown. I tried flushing the cache to see if that would help but to no avail.

Here is the code that I had used:
DECLARE
			@JobId			INT,
        @Jobname		NVARCHAR(50),
        @Jobdir			NVARCHAR(100),
        @LocalDir		NVARCHAR(100),
        @LocalFile		NVARCHAR(100),
        @FTPServer		NVARCHAR(50),
        @FTPFile		NVARCHAR(50),
        @Namechange		NVARCHAR(50),
        @SaveDir		NVARCHAR(100),
        @Command		NVARCHAR(100),
        @ScriptFolder	NVARCHAR(100),
        @LogName		NVARCHAR(30)

SET @

@Command, @FTPFile, @LogName, @Namechange, @Savedir and @Scriptfolder were all listed by SQLPrompt; the rest weren't.

I had added @Jobid to the top of the list after all other items were there, could this be the reason for the confusion? What could I do to make SQLPrompt see all of the other variables? Do I have to add my variables all at the end of the list instead of inserting them in an existing list?

Many thanks

wd

Comments

  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    Try this:

    From the SQL Prompt menu click Options > Listed Candidates > Variables and Parameters. Now increase the value of "Maximum number of recently declared variables to display in the candidate list".

    If you're working with small scripts, or your scripts have regular GO markers (say every 50 lines or so) you could also just switch to "Search entire batch/GO block" on the same options screen.


    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    Hi Bart,

    Super..... problem solved.... damn you're quick :wink:
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    No problem. Glad to be able to help.
    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    I should say my second suggestion will not perform well if you start editing a large script with few batch markers in it so if you start experiencing performance problems you'll want to switch back and just use a larger value for the maximum number of recently used variables to display.
    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.