Solved

Use 'GO' without losing variables?  Is there an equivalent to GO or elegant workaround?

Posted on 2012-03-15
5
344 Views
Last Modified: 2012-09-14
I set some variables that are calculated dates and want to see a check of those as soon as a long-running query is started.  

If I have an initial SELECT that displays the variables, it doesn't display until the query completes.  And of course, if I insert a GO command I lose the variable values.
I want the initial SELECT to execute (display) right away.

The only work-around I can think of (as a sql beginner) is to write my variables into a temp table, use GO, and then read them back out.  I hope there's a more elegant method.  Is there?

(main query displays all at once, not piecemeal, due to a sort on a calculated column)

Thanks,

Ed

DECLARE @variable varchar(20)
SET @variable = '"SEARCH CRITERIA"'

SELECT 'Starting a query based on the criteria of: ' + @variable + '.  This may take a while.' AS 'Intro that allows double check of criteria. ______________________________________'

--GO

WAITFOR DELAY '00:00:05.000'  --just to simulate the time required to run the query.  Often 10 minutes or more.
SELECT 'This is a long running query based on ' + @variable + '.' AS 'Main Query ________________________________________'

Open in new window

0
Comment
Question by:nosliwde99
  • 3
5 Comments
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 37728250
Instead of using the select to display a message, cant you use the print command

print 'Starting a query based on the criteria of: ' + @variable + '.  This may take a while.' AS 'Intro that allows double check of criteria. ______________________________________'
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37728981
why do you need GO? I have been writting SQL for years and didn't had to use GO very often!
0
 

Author Comment

by:nosliwde99
ID: 37731581
No dice so far.  Thanks for the idea Sandy.  The PRINT only prints the message to the messages tab at the same time that the results are populated.   The sample code illustrates the problem nicely.  Other thoughts?
0
 

Accepted Solution

by:
nosliwde99 earned 0 total points
ID: 37759606
So, I just wrote my variables into a temp table, used the GO to be able to execute the first query straight away, then read the variables back out of the temp table.  I'm guessing from the responses that there is no other good method.  Will leave open a bit longer to see if anyone comes up with a better solution.
0
 

Author Closing Comment

by:nosliwde99
ID: 38398101
No good solution found - this was the best work-around (i.e. writing variables to a temp table, employing the 'GO', then reading the variables back out of the temp table).
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now