Add custom T-SQL command via SQLCLR

Question

Add custom T-SQL command via SQLCLR

chip8

SSC Rookie

Points: 31
More actions
February 26, 2010 at 8:21 am

#386402

I have an SqlProcedure 'showbis' with one string parameter. The procedure 'showbis' uses the parameter string to get a dataset from an external ODBC datasource. The syntax in the query window is as follows:
showbis 'select * from table1'.
I would like to create a custom build-in function named 'bisselect' which would allow me to enter the command:
bisselect * from table1
I tried creating an SqlProcedure with no parameters and then reading the sql command from the system tables to get the the select statement but SQL did not like the text following the word bisselect.
Any thought are appreciated.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

To the best of my knowledge, you cannot do anything like that in SQL Server as it does not allow you to alter, modify or in any way change its internal command parsing tables.

The closest that you can come, with either T-SQL or SQL CLR, is the stored procedure that you already have.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150468 More actions · Answer 2

chip8 (2/26/2010)
I have an SqlProcedure 'showbis' with one string parameter. The procedure 'showbis' uses the parameter string to get a dataset from an external ODBC datasource. The syntax in the query window is as follows:
showbis 'select * from table1'.
I would like to create a custom build-in function named 'bisselect' which would allow me to enter the command:
bisselect * from table1

One option might be to re-code this as a CLR Table-Value Function, so you could write:

SELECT *

FROM BIS.[SELECT] (N'SELECT * FROM Table1');

The TVF is named [SELECT], and the schema is BIS.

Seems like an odd requirement though!

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

chip8 SSC Rookie Points: 31 More actions · Answer 3

It would be nice if SQL Server allowed us to ADD custom commands like the 'select' command.

The problem I have with the stored procedure passing in a string is with character string parameters. This starts to get ugly:

showbis 'select * from table where field1 = ''abc'' and field2 = ''def'' and field3=''ggg'''

I would be satisifed if I could have the SQLProcedure or an SQLFunction IGNORE the parameters on the command line. In a query window you could type the command:

Select * from CLRFunction() where field1='abc' and field2='def' and field3='ggg'

I could then get the full command from the sys.dm_exec_requests table and build the proper select statement.

Paul White SSC Guru Points: 150468 More actions · Answer 4

I have one question: why don't you use a linked server for this requirement?

You could then use the four-part name syntax, or EXECUTE...AT for pass-through queries.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

chip8 SSC Rookie Points: 31 More actions · Answer 5

That was my first approach. I'm dealing with an old Unisys database named Mapper. I could not setup a linked server to it. I developed a WCF application to handle the data collection from the Unisys server. I call out to the WCF from SQL CLR application. I have a number of SQLFunctions that I use to access the Unisys tables. Example: SQLFunction Customers() will return the Customers table in Unisys. I also modify the SQL select statements to statement Unisys will accept (can't use Left(), right() substring()). All works fine. However, when I want to update or insert it gets ugly with all the ''''' single quotes around string data.