| Author |
 Topic  |
|
|
l463649
Starting Member
Australia
5 Posts |
 Posted - 01/07/2013 : 03:51:38
|
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
16746 Posts |
Posted - 01/07/2013 : 04:24:28
|
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
|
 |
|
|
l463649
Starting Member
Australia
5 Posts |
Posted - 01/07/2013 : 04:32:34
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47154 Posts |
Posted - 01/07/2013 : 10:36:54
|
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/
|
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 01/07/2013 : 13:19:00
|
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 |
|
|
|
l463649
Starting Member
Australia
5 Posts |
Posted - 01/07/2013 : 17:04:29
|
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 |
|
|
|
shan007
Starting Member
USA
8 Posts |
Posted - 01/07/2013 : 20:15:56
|
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.. |
|
|
|
l463649
Starting Member
Australia
5 Posts |
Posted - 01/07/2013 : 20:30:49
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47154 Posts |
Posted - 01/07/2013 : 23:15:46
|
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/
|
|
|
|
l463649
Starting Member
Australia
5 Posts |
Posted - 01/08/2013 : 00:31:25
|
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 |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1770 Posts |
Posted - 01/08/2013 : 08:01:19
|
Use sp_executesql rather than exec - there are various security \ performance benefits
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
| |
Topic |
|
Print a select statement
