CODEWITHSUNDEEP
sqlsql-serversql-server-2005
serhio
serhio

Reputation: 28586

Write a SQL Log procedure

I need to write a simple SQL log procedure, that could be called like this:

Log("This is my param1 = {0}, param2 = {1} and param3('{2}')", 
     @param1, @param2, @param3)

the output should be redirected to the file on SQL Server "C:\output.txt"

is it possible using such a procedure with variable number of params and how?

perhaps I could use

exec master..xp_cmdshell 'echo created > c:\output.txt'
exec master..xp_cmdshell 'echo appended data >> c:\output.txt'
exec master..xp_cmdshell 'echo more data >> c:\output.txt'

Upvotes: 0

Views: 468

Answers (2)

Vikdor
Vikdor

Reputation: 24134

Firstly, SQL Server's T-SQL doesn't allow variable number of arguments to stored procedures like how Java supports. But since SQL Server 2005 has XML data type, you can build a simple XML with your arguments and send that as the second parameter, first being the log statement with placeholders. In your stored procedure, you can read those values back from XML.

Secondly, xp_cmdshell requires you to have 'sa' permissions, afaik. Instead, you could write those log statements to a temporary table and get a stored procedure created by your admin that takes the table name and the filepath and dumps the contents of the table to the filepath specified.

Edited the comment about variable number of arguments as per one of the comments below

Upvotes: 1

podiluska
podiluska

Reputation: 51514

As long as you have a finite number of parameters, you can use nullable parameters in SQL server to implement optional parameters

So

create proc prcLog
    @Format varchar(1000),
    @p1 varchar(100) = null,
    @p2 varchar(100) = null,
    @p3 varchar(100) = null,
    ....

exec prcLog '{0}', 'a'
exec prcLog '{0} : {1}', 'a', 'b'

Given the amount of File IO and string manipulation, a CLR stored procedure may be a better option

Upvotes: 1

Related Questions