Get the SQL 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

SQL Server 2008 Forums

Transact-SQL (2008)

Get the SQL Statement

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-10-24 : 14:49:59

Hi,

Is there a way to get the SQL Query with parameters, passed to the stored procedure to be set to a varchar variable inside the stored procedure.

to elaborate my question,

if a stored procedure is called as follows (by a c# program):
dbo.MyProc 'a',123,Null,'b','',0

I need to get the string "dbo.MyProc 'a',123,Null,'b','',0" to a variable.
i know lenghty, custom query will do the job, but I need to know, whether there is any system variable or any mechanism to get the calling SQL statement.

I need to do it inside the stored procedure and not from the program where it is calling.

Srinika

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 14:58:50

hmm...you mean generate the SP call command within same procedure?

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

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-10-24 : 15:34:11

Yes Visakh,
I need that to do a debug / testing, where
I need to record the queries and
make a change and
redo the same using the recorded queries.
Instead of entering data for about 100 test cases in a UI, each time I test.

I'll give u a sample, which I got it by concatenating all variables, converting to varchar if not, putting Null whereever necessary, and so on.

[dbo].[ProcessItem] 22094, 13479, 0.00, '10-12', 'TEST', 'Good', Null, 10, 'Does not meet fullness', 2, 1.00, '01', 0, Null, 'No'
Go
[dbo].[ProcessItem] 22094, 13479, 1.00, '10-12', 'TEST', 'Good', Null, 1, '', 2, 1.00, '01', 0, Null, 'No'
Go
[dbo].[ProcessItem] 22094, 13521, 25.00, '12-12', 'TEST', 'Good', Null, 6, '', 2, 1.00, '01', 0, Null, 'No'
Go
[dbo].[ProcessItem] 22094, 13521, 30.00, '01-11', 'TEST', 'Good', Null, 6, '', 2, 1.00, '01', 0, Null, 'No'

For One Stored procedure with few parameters, it is not that much of a deal. But imagine a procedure with 20~30 parameters and i have about 20 of those to be debugged.

Srinika

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 16:26:12

where do you get these value combinations from?

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

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-10-24 : 16:54:47

by something like


declare @Qry varchar (max) --, @UserID
set @Qry = '[dbo].[ProcessItem] ' + 
	convert(VARCHAR(10),@b) + ', ' +
	convert(VARCHAR(10),@p) + ', ' +
	convert(VARCHAR(10),@q) + ', ' +
	isnull('''' + @exp + ''', ','NULL, ') +
	isnull('''' + @l + ''', ','Null, ') +
	isnull('''' + @c + ''', ','Null, ') +
	isnull('''' + @a + ''', ','Null, ') +
	convert(VARCHAR(10),@r) + ', ' +
	isnull('''' + @s + ''', ','Null, ') +
	convert(VARCHAR(10),@ui) + ', ' +
	convert(VARCHAR(10),@cn) + ', ' +
	'''' + @si + ''', ' +
	convert(VARCHAR(10),@se) + ', ' +
	isnull(convert(VARCHAR(10),@Cl) + ', ','Null, ') +
	isnull('''' + @O + '''','Null') 

exec [dbo].[InsertToQueryLog] @Qry, '', @ui

Srinika

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-25 : 10:04:00

it'd be easier to log the call in .net -- depending on the system architecture of course -- if there is a distinct data layer then that will make it easy. if your system ad hoc calls the database from everywhere whenever it feels like it then harder.....

or run sql server profiler for a while and capture the traffic.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2012-10-26 : 15:37:01

Hi Charlie,

It is called from so many different places. There is no Business or Data layer.
Profiler option is not that feasible ( I can see the query, but not easy to write it to a table, in each test case )

I'm looking for something like a system variable or system column value, which I can use, to record the query

Srinika

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-29 : 06:07:22

http://sqlserverpedia.com/wiki/The_Server-side_Trace:_What,_Why,_and_How

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-29 : 19:43:44

I can't say that this will be of help to you but...[CODE]DBCC INPUTBUFFER[/CODE]See BOL for additionasl details.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources