SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Print a select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

l463649
Starting Member

Australia
5 Posts

Posted - 01/07/2013 :  03:51:38  Show Profile  Reply with Quote
I want to print (to file) a the select statements in my report as shown below, I have 100's of lines and don't want to have duplicated select statements in the SQL.

print'TABLE : MYTABLE:'
print''
print'Checking column otid: referencing id in table OTHERTABLE:'
SELECT COUNT(*) FROM MYTABLE WHERE NOT EXISTS ( SELECT * FROM OTHERTABLE WHERE OTHERTABLE.id = MYTABLE.otid)
print''

I know I could do the following but as I said I don't want to duplicate the select statements.

print'TABLE : MYTABLE:'
print''
print'Checking column something: referencing id in table OTHERTABLE:'
print'SELECT COUNT(*) FROM MYTABLE WHERE NOT EXISTS ( SELECT * FROM OTHERTABLE WHERE OTHERTABLE.id = MYTABLE.otid)'
SELECT COUNT(*) FROM MYTABLE WHERE NOT EXISTS ( SELECT * FROM OTHERTABLE WHERE OTHERTABLE.id = MYTABLE.otid)
print''

Thanks

Graeme

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 01/07/2013 :  04:24:28  Show Profile  Reply with Quote
what is the reason that you need to do this ? I don't see any practical reason for doing this


KH
Time is always against us

Go to Top of Page

l463649
Starting Member

Australia
5 Posts

Posted - 01/07/2013 :  04:32:34  Show Profile  Reply with Quote
quote:
Originally posted by khtan

what is the reason that you need to do this ? I don't see any practical reason for doing this


KH
Time is always against us





Because I want the sql statements to appear in my report!

Thanks

Graeme

Edited by - l463649 on 01/07/2013 05:24:54
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2013 :  10:36:54  Show Profile  Reply with Quote
why should you display sql statements in report? i dont think users will be intesrested in sql statements that run on background. they should be concerned only on end resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 01/07/2013 :  13:19:00  Show Profile  Visit jackv's Homepage  Reply with Quote
You could could try something like:
SELECT @@SPID -- to get the current spid

place that value in a variable and run the sql

SELECT spid, db_name(s.dbid), text
FROM master.dbo.sysprocesses as s
cross apply ::fn_get_sql(s.sql_handle)
where spid = <spid_variable>



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

l463649
Starting Member

Australia
5 Posts

Posted - 01/07/2013 :  17:04:29  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

why should you display sql statements in report? i dont think users will be intesrested in sql statements that run on background. they should be concerned only on end resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





I am not really interested if you think its a good idea or not I just want to know how I could do it!

Thanks

Graeme

Edited by - l463649 on 01/07/2013 17:54:39
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 01/07/2013 :  20:15:56  Show Profile  Reply with Quote
The simplest solution for your request is, just declare a variable and assign your query, you can play with that variable however you want, as in below:

declare @sql varchar(max)
set @sql='select * from users'
exec(@sql)
print(@sql)
go

If you don't like this approach, I'll try to figure out other possibilities..
Go to Top of Page

l463649
Starting Member

Australia
5 Posts

Posted - 01/07/2013 :  20:30:49  Show Profile  Reply with Quote
quote:
Originally posted by shan007

The simplest solution for your request is, just declare a variable and assign your query, you can play with that variable however you want, as in below:

declare @sql varchar(max)
set @sql='select * from users'
exec(@sql)
print(@sql)
go

If you don't like this approach, I'll try to figure out other possibilities..



Awesome that looks like it will work (this so much easier in Oracle).

Thanks

Graeme
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2013 :  23:15:46  Show Profile  Reply with Quote
quote:
Originally posted by l463649

quote:
Originally posted by visakh16

why should you display sql statements in report? i dont think users will be intesrested in sql statements that run on background. they should be concerned only on end resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





I am not really interested if you think its a good idea or not I just want to know how I could do it!

Thanks

Graeme


Wow...Without understanding the purpose and value add it gives top users whats the use of implementing something?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

l463649
Starting Member

Australia
5 Posts

Posted - 01/08/2013 :  00:31:25  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by l463649

quote:
Originally posted by visakh16

why should you display sql statements in report? i dont think users will be intesrested in sql statements that run on background. they should be concerned only on end resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





I am not really interested if you think its a good idea or not I just want to know how I could do it!

Thanks

Graeme


Wow...Without understanding the purpose and value add it gives top users whats the use of implementing something?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





It's for me to use, stop second guessing what I am up to!

Thanks

Graeme
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 01/08/2013 :  08:01:19  Show Profile  Visit jackv's Homepage  Reply with Quote
Use sp_executesql rather than exec - there are various security \ performance benefits

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000