Table function - list of parameters

Question

Table function - list of parameters

_simon_

SSCrazy

Points: 2329
More actions
April 30, 2010 at 11:13 am

#221144

Is it possible to create a table function, to which any number of varchar parameters could be passes (maybe some kind of xml data type)? For example:
select getValues('tableName', 'column2')
select getValues('tableName', 'column1', 'column2')
select getValues('tableName', 'column1', 'column2', 'column3')
If it is possible, how can I loop through all parameters in getValues function?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

what would the function do with the values if it got them? you might be looking at a problem head on, when a sideways look might present a better solution.

what exactly are you trying to do in the function?

a function cannot do dynamic sql, so you can't construct a sql statement from the table/list of columns, so passing in a table and list of columns will not help you in a function...a procedure, yes, but not a function.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

_simon_ SSCrazy Points: 2329 More actions · Answer 2

OK, a procedure then.

It's about lottery. Each lottery game can have different parameters about winning numbers. One such parameter is 'number of numbers you have to select'. So if the game states, that you have to select 7 number, the table would look like this:

create table Game01 (

id int identity(1, 1),

userId int,

number1 int,

number2 int,

number3 int,

number4 int,

number5 int,

number6 int,

number7 int

)

And if you have to select 9 different number, the table would look like this:

create table Game02 (

id int identity(1, 1),

userId int,

number1 int,

number2 int,

number3 int,

number4 int,

number5 int,

number6 int,

number7 int,

number8 int,

number9 int

)

So: first parameter is the table name and the second one is the list of all the columns, which include a selected number.

In the procedure I will generate some groupings, which would help with the final control of each (different) lottery game. The grouping will have some pattern, so I want to generate a select statement from all the columns (probably with execute statement). In the first example the user will call:

exec CheckProcedure 'Game01', 'number1', 'number2', 'number3', 'number4', 'number5', 'number6', 'number7'

And in the second:

exec CheckProcedure 'Game01', 'number1', 'number2', 'number3', 'number4', 'number5', 'number6', 'number7', 'number8', 'number9')

The user should be able to insert the values from this procedure into a new table, so with a scalar function it would be easy (haven't tested this out yet though):

select getValues('Game01', 'number1', 'number2', 'number3', 'number4', 'number5', 'number6', 'number7')

into #temp

For stored procedures things change...

Edit: I can't just loop through columns, because the user could want to use this procedure with not all columns, like:

exec CheckProcedure('Game01', 'number1', 'number2')

exec CheckProcedure('Game01', 'number2', 'number3')

_simon_ SSCrazy Points: 2329 More actions · Answer 3

One option could be, that I always call the procedure with 2 parameters (table name, list of columns):

exec CheckProcedure('Game01', 'column1, column2, column3')

But each column as it's own parameter would be really cool 😀

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

Simon, i would suggest that, u just

1. create a stored procedure with only one parameter.

2. create a comma-delimited string with the Table_Name+Columns.

3. Pass this concatenated comma-delimited string to the SP.

4. Inside the SP, first thing u do is to split that comma-delimited string into single single strings.

5. There on , you can process anything you want with the string.

Here is one example of how to get a comma-delimited string.

declare @value varchar(255)

Declare @Table_Name varchar(255)

set @Table_Name = 'A'

set @value = ''

select @value = @value + '['+ column_name + '] , ' from information_schema.columns

where table_name = @Table_Name

select @value = substring(@value, 1, len(@value)-1)

Select @value = '['+@Table_Name+'], '+ @value

Select @value

For how to split the comma-delimited string, i would recommend you go thro this article Passing Parameters as (almost) 1, 2, and 3 Dimensional[/url] from one of the finest man here, Jeff Moden 🙂

Hope this helps you!!

Edit : Fixed tags!

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 5

Hi,

Can we pass all the columns to the procedure as a table parameter or as an XML?

which one will be better for performance?

string or XML or a table?

Paul White SSC Guru Points: 150467 More actions · Answer 6

sharath.chalamgari (5/3/2010)
Can we pass all the columns to the procedure as a table parameter

Only in SQL Server 2008.

or as XML?

Yes.

which one will be better for performance - string, XML or a table?

It depends. For the small number of items in your list it probably won't make much difference which way you go - unless it is called millions of times. Table probably fastest (2008 only), string second, XML third - but it really does depend on the circumstances.

The cost of splitting the data up will probably be insignificant compared to the other operations you are planning to perform.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

_simon_ SSCrazy Points: 2329 More actions · Answer 7

OK,

I created a stored procedure with only one parameter (comma delimited) and used Jeff Moden's parsing technique.

Thanks