StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Disclaimer: As I've indicated in my comments, giving a bunch of users access directly to SSMS to run reports is a <strong>very bad idea</strong>. Get some sort of front-end, even a simple MS Access database - you would only need a single license to develop the database, and you could give the rest of the users Access Runtime, for instance. There are so many ways a user could really mess you up if they don't know what they're doing. I will offer some ideas below, but I <strong>don't recommend doing this</strong>.</p> <hr> <p>One solution: use temp tables so you don't have to worry about each user's tables overlapping:</p> <pre><code>-- drop the table if it already exists if object_id('tempdb..#z') is not null DROP TABLE #z SELECT * INTO #z FROM y </code></pre> <p>When you prefix a table name with <code>#</code>, it becomes a connection-scoped temporary table, which means separate sessions will not see the temporary tables in other sessions even if they have the same name.</p> <hr> <p>Often it is not necessary to create a temp table unless you have some really complicated scenario. You should be able to make use of subqueries, views, CTE's, and stored procedures to generate the output real-time without any new tables being involved. You can even build views and procedures that reference other views so you can organize your complicated logic. For example, you might encapsulate the logic into a stored procedure like this:</p> <pre><code>CREATE PROCEDURE TheReport ( @ReportID int, @Name varchar(50), @SomeField varchar(10) ) AS BEGIN -- do some complicated query here SELECT field1, field2 FROM Result Q END </code></pre> <p>Then you don't even have to send updates to your users (unless the fields change). Just have their query call the stored procedure, and you can update the procedure directly at your convenience:</p> <pre><code>DECLARE @ReportID int DECLARE @Name varchar(50) DECLARE @SomeField varchar(10) -- YOU CAN MODIFY THIS -- SET @ReportID = 5 SET @Name = 'MyName' SET @SomeField = 'abc' -- DON'T MODIFY BELOW THIS LINE -- EXEC [TheReport] @ReportID, @Name, @SomeField; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload