Oct 25, 2006

A Scenario to Ponder #6

Here is a question which I find to be pretty interesting. Can anyone come-up with a query or a batch which, when executed, will print itself? I would like to see the lengthiest batch anyone can come up with. The solution can be in SQL Server 2000 or 2005. For example, here is a no-brainer solution that works for SQL Server 2005

SELECT (select text from sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_requests
WHERE session_id = @@spid

The above query was just to give an idea. So, don't try the dbcc command to get the input buffer :)
You cannot select from the data persisted beforehand, meaning the query (or batch) should run in any SQL Server with no initial setup.

Happy coding!

Please Note: Answering this question will get you no better in SQL programming than you were before attempting it. You may want to try just for the fun of it.


Rob Farley said...

Not code I'm amazingly proud of, but it does the job:

declare @s varchar(8000);
set @s = 'declare @s varchar(8000);set @s = ''XXX'';
select replace(replace(@s,''= ''''YYY'',''= '''''' +
''''''''YYY'',''@s,''''= ''''''''XXX'');';
select replace(replace(@s,'= ''XXX','= ''' +
replace(@s,'''','''''')),'@s,''= ''''YYY','@s,''= ''''XXX');

--I could make it shorter/longer as required, of course... I think the trick is to make it as short as possible, not as long as possible.

Omnibuzz said...

Beautiful Rob! Didn't think it was possible with select query till now. And, like you said, getting the solution shorter is more tough. Just realised, when you mentioned it, that I can paste the same solution twice and make it longer :)

Juz wondering how the code (that you are proud of) will look like. This IS amazing.

Omnibuzz said...

Hi All,
Here is my solution. Though its not exactly an ethical solution. I guess it works :)

declare @a varchar(1000),
@b varchar(1)
set @b = ''''
set @a= 'declare @a varchar(1000),
@b varchar(1)
set @b = %s%s%s%s
set @a = %s%s%s

Let me know your comments.

Rob Farley said...

Well, it would start by having less apostrophes!

Omnibuzz said...

To tell you the truth I tried to bring the apostrophes down.. worked for an hour with your solution and I got more and more away from the answer. Honestly, I could have never come up with the solution you gave. Though I can understand it, I can never reproduce it.

I would still consider it a masterpiece. Way beyond my league.

Post a Comment