Print a select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Print a select statement

Author

Topic

l463649
Starting Member

5 Posts

Posted - 2013-01-07 : 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)

17689 Posts

Posted - 2013-01-07 : 04:24:28

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

KH
[spoiler]Time is always against us[/spoiler]

l463649
Starting Member

5 Posts

Posted - 2013-01-07 : 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
[spoiler]Time is always against us[/spoiler]

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

Thanks

Graeme

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-07 : 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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-07 : 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

5 Posts

Posted - 2013-01-07 : 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

shan007
Starting Member

17 Posts

Posted - 2013-01-07 : 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

5 Posts

Posted - 2013-01-07 : 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

52326 Posts

Posted - 2013-01-07 : 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

5 Posts

Posted - 2013-01-08 : 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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-08 : 08:01:19

Use sp_executesql rather than exec - there are various security \ performance benefits

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources