Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing a list of values to a Stored Procedure (Part III) 3

Status
Not open for further replies.
[cry]

For my app, I have 100 customers spread throughout the U.S. and Canada. Several of them are still using SQL 2000, so I can't upgrade to 2005 until they are all ready to upgrade.

The only other option would be to support multiple versions of stored procedures, which I don't want to do.

-George

"the screen with the little boxes in the window." - Moron
 
If it's your app, why do you have denormalized comma-delimited strings?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
I'm glad you asked. [smile]

I don't have any denormalized comma-delimited strings. I feel like crying because there are other aspects of SQL2005 that I would love to use, but can't.

-George

"the screen with the little boxes in the window." - Moron
 
>>The only other option would be to support multiple versions of stored procedures, which I don't want to do.


hey George
Code: 
if  parsename(convert(varchar(20),serverproperty('productversion')),3) >8
begin

	select 'sql 2005 code here'
end
else
begin
	select 'sql 2000 code here'
end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
why support multiple versions of a stored proc, when you can support multiple versions all in the same proc ;-)

Ignorance of certain subjects is a great part of wisdom
 
Denis,

I wouldn't have multiple procedures. If I were to implement this, I would use this undocumented feature.

Code: 
if @@microsoftversion >= 150996343
    Select 'SQL 2005'
Else
    Select 'SQL 2000'

-George

"the screen with the little boxes in the window." - Moron
 
Isn't SQL 2000 going to balk at the SQL 2005 code, even if it's in a branch that never gets executed?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
I was thinking that, too, E^2, but figured instead of having both versions in the same SP, just replace the SELECTs with sp_Executes...

'Course, I won't have to worry about supporting 2005 until 2008's replacement comes out. :-(

< M!ke >
I am not a hamster and life is not a wheel.
 
By the way George, did you ever rerun option 5 to see how it performed without the "column that hurts performance" in it?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
I've been on vacation for the last week, and then I've got a business trip for the first 1/2 of next week. I'll try to find some time to play around with it when I get back.

-George

"the screen with the little boxes in the window." - Moron
 
I know you guys are probably sick of hearing this stuff, but the CLR does slightly better than E^2's #6 and Denis' functions. For a string of 10001 zip codes (I must have a <= in place of a < in that function I wrote to build the string or something) this is a typical set of times (no need to comment on the tremendous power that my PC has):

CLR --> 203 ms
Denis --> 280 ms
E^2 --> 271 ms

The lowest time I saw for CLR was 170 ms (a few times, out of about 15-20). E's I saw a 230 once. Denis' low was 250 (but in fairness, I didn't dig enough into his function to remove the ID column it returns. I did do this with E^2's, but it didn't make any difference.)

So, while it doesn't make that much of a difference, I suppose I will be a punk and post it anyways

Code: 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "clrSplit",
    FillRowMethodName = "FillRow", TableDefinition = "ID NVARCHAR(50)")]
    public static IEnumerable Split(String toSplit, SqlChars delimiter)
    {
        //whole string if no delimiter is specified
        if (delimiter.Length == 0)
            return new string[1] { toSplit };


        //split the string and return an 'array'
        return toSplit.Split(delimiter[0]);
    }

    public static void FillRow(object row, out SqlString str)
    {
        //um, fill the row
        str = new SqlString((string)row);
    }
}

One note, once it is deployed you need to alter the function it creates to take the parameters as varchar(max) and varchar(whatever, I used 100), respectively.

It may not be that much better a performer, but may as well be thorough with the FAQ's ;)


Time for bed now [sleeping2]

Alex



Ignorance of certain subjects is a great part of wisdom
 
Alex,

Would you mind giving a brief outline of how to use this code, where to put it, save it, compile it, and install it to SQL 2005?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]
 
Maybe I will write an FAQ's. Do you have visual studio 2005 or just BIDS?

I think you need the full version of VS (maybe pro even).

Anyway, here goes

1. Open Visual Studio, click 'Create Project'
2. Under Visual Basic and C#, there are project types available called 'Database Project'
3. Choose Database Project, then select 'SQL Server Project', name your project and create it (these are stored in the same folder as your regular VS Projects, so I always start the name with clr so I know what I'm looking for).
4. Click Project --> Add User-Defined Function
5. Paste in the code (if you are using C#, need to rewrite if you want to do it with VB of course) into your new .cs file.
6. Build --> Deploy
7. Run this code to enable CLR (or you won't be able to use it)
Code: 
EXEC sp_configure 'show advanced options' , '1';

go

reconfigure;

go

EXEC sp_configure 'clr enabled' , '1'

go

reconfigure;

-- Turn advanced options back off

EXEC sp_configure 'show advanced options' , '1';

go

8. Script your resulting function as an alter, to change parameter types to nvarchar(max) and nvarchar(something < max), respectively. I don't know why the default size for a string once you deploy is nvarchar(4000). It will look like this after you make the changes:

Code: 
ALTER FUNCTION [dbo].[clrSplit](@toSplit [nvarchar](max), @delimiter [nvarchar](15))
RETURNS  TABLE (
	[ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerProject1].[UserDefinedFunctions].[Split]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'clrSplit'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'clrSplit.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'clrSplit'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=10 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'clrSplit'

Then after this, you are ready to try it (you can try before altering the function, but it will not return any characters past 4000.

use it like a normal table valued function.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
I have been wanting to write an article on using CLR functions for my website. Maybe this can be the starting point.

Ignorance of certain subjects is a great part of wisdom
 
It's not really ready for public consumption at the moment. I have been struggling to find time to build comment-ability into the blog section (where said article may or may not end up). Maybe this weekend. The address is simply my tek tips handle .com though...

Ignorance of certain subjects is a great part of wisdom
 
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
751
grnzbra
grnzbra
Cpreston
  • Locked
  • Question Question
How to add a Letter to datepart
Replies
5
Views
1K
Andrzejek
Andrzejek
SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
891
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question Question
How Do I Add Column to Middle of Table
Replies
4
Views
1K
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top